Results 1 to 6 of 6

Thread: Is Query of a Query possible in PHP / MySQL?

  1. #1

    Afrostyle Is Query of a Query possible in PHP / MySQL?

    i'm currently in the process of trying to convert my site from Coldfusion to PHP. I'm stuck on trying to do a query of query. Here is the code in Coldfusion:

    Code:
    <cfquery name="GetArt" datasource="NSLA2">
    	SELECT Max(ID) AS MaxID FROM news
    </cfquery>
    <cfquery name="TopArt" datasource="NSLA2">
    	SELECT * FROM news WHERE ID = #GetArt.MaxID#
    </cfquery>
    <cfquery name="Articles" datasource="NSLA2">
    	SELECT ID, title FROM news WHERE ID < #GetArt.MaxID# ORDER BY ID DESC
    </cfquery>
    What this allow me to do is get the latest article by choosing the greatest ID. The first query grabs the highest ID. The second query uses the variable from the first "MaxID" and selects the article where ID equals the "MaxID" found in the first query. The last query selects all of the articles with a ID number less than the "MaxID from the first query.

    I've tried to recreate this in PHP. I think I'm on the right track but don't really know where to go. Here is what I have.

    PHP Code:
    $get "SELECT Max(ID) AS MaxID FROM news";
    $getr mysql_query($get);

    while(
    $info mysql_fetch_array($getr)) {
        
    $top 'SELECT * FROM news WHERE ID = " . $info ['MaxID'] . "';
        
    $art 'SELECT ID, title FROM news WHERE ID < " . $info ['MaxID'] . " ORDER BY ID DESC';
        
    $topr mysql_query($top);
        
    $artr mysql_query($art);

    Am I on the right track? or is this not possible? Any help would be great...

  2. #2

    its true!!!

    hello darkstarclone

    yes you are on right track I have tested from my side as I have worked with coldfusion & php both it is correct

    Regards,
    Kunjan.

  3. #3
    1,627
    posts
    hugeExplosions = true;
    Quote Originally Posted by darkstarclone View Post
    i'm currently in the process of trying to convert my site from Coldfusion to PHP. I'm stuck on trying to do a query of query. Here is the code in Coldfusion:

    Code:
    <cfquery name="GetArt" datasource="NSLA2">
        SELECT Max(ID) AS MaxID FROM news
    </cfquery>
    <cfquery name="TopArt" datasource="NSLA2">
        SELECT * FROM news WHERE ID = #GetArt.MaxID#
    </cfquery>
    <cfquery name="Articles" datasource="NSLA2">
        SELECT ID, title FROM news WHERE ID < #GetArt.MaxID# ORDER BY ID DESC
    </cfquery>
    What this allow me to do is get the latest article by choosing the greatest ID. The first query grabs the highest ID. The second query uses the variable from the first "MaxID" and selects the article where ID equals the "MaxID" found in the first query. The last query selects all of the articles with a ID number less than the "MaxID from the first query.

    I've tried to recreate this in PHP. I think I'm on the right track but don't really know where to go. Here is what I have.

    PHP Code:
    $get "SELECT Max(ID) AS MaxID FROM news";
    $getr mysql_query($get);

    while(
    $info mysql_fetch_array($getr)) {
        
    $top 'SELECT * FROM news WHERE ID = " . $info ['MaxID'] . "';
        
    $art 'SELECT ID, title FROM news WHERE ID < " . $info ['MaxID'] . " ORDER BY ID DESC';
        
    $topr mysql_query($top);
        
    $artr mysql_query($art);

    Am I on the right track? or is this not possible? Any help would be great...
    Well...a few things:

    1. Your code uses a while() on the mysql_fetch_array - you don't need to do this, the first query only returns 1 row (the MAX of the ID column in articles) so you can take that out and just call $info = mysql_fetch_array($getr) once.

    2. You don't need to do "SELECT Max(ID) AS MaxID FROM news". You can instead just get the top 1 - the syntax differs depending on SQL 2000/2005 and MySQL but I'm assuming this is MySQL

    I'm pretty sure "SELECT * FROM news ORDER BY ID DESC LIMIT 1" is the correct syntax. This will always get the latest news article (i.e. get all rows ordered by the ID descending and limit the results to 1 row)

    3. You don't even need to do two queries - I know it's probably not that intensive, but you are making three calls to the database when one would probably do.

    If you did a "SELECT * FROM news ORDER BY ID DESC" and used mysql_fetch_array() once, this would give you the latest news article, then and further calls to mysql_fetch_array() will give you the rest.

    Consider changing your code to

    PHP Code:
    $getr mysql_query("SELECT * <or columns??> FROM news ORDER BY ID DESC");
     
    $first_article mysql_fetch_array($getr);

    // do stuff with $first_article

    while($other_article mysql_fetch_array($getr)) {
      
    // do stuff with $other_article

    Enjoy

    Edit: Accidentally did msql_fetch_array on $get instead of $getr - fixed now

    Edit: Why are we declaring $get anyway? Changed the code to get rid of it...
    Last edited by Charleh; September 4th, 2009 at 09:06 AM.
    MS Paint FTW!


  4. #4
    Thank you for the insight on this Charleh... it's good to have a fresh perspective on this. I do have one question though...

    Since you only have one query and when you call this function:
    PHP Code:
    $first_article mysql_fetch_array($query); 
    I understand that it brings up the first row in the array, but when you call it again with this:
    PHP Code:
    while($other_article mysql_fetch_array($query)) {} 
    wouldn't that bring up the first row again? or does Php keep track of that for you so it brings up all of the other rows in the array you didn't call?

  5. #5
    Alright... so I tested this out and it seems I answered my own question. The first row isn't doubled when you fetch the array again. I guess PHP keeps track of what you accessed... thank you again Charleh for the insight. Also thank you kunjan for taking the time to read this and encourage me on my pursuit.

    I'm going to be posting another question. Maybe you guys can help me understand how PHP handles arrays...

  6. #6
    icio's Avatar
    3,811
    posts
    looks better in lowercase
    You could use a subquery for this:
    Code:
    SELECT * FROM table WHERE id < (SELECT MAX(id) FROM table);
    will return all rows except the newest one (where the newest one has the largest ID.)

    Hope that helps
    "60% of the time it works... every time." -- Paul Rudd as Brian Fantana.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012