SQL Query - Where and Order By?

[glassfish]

Script:

<?php

    $tqs_admin_flag = "SELECT * FROM `flags`";
    $tqr_admin_flag = mysqli_query($dbc, $tqs_admin_flag) or die(mysqli_error($dbc));

while($row_admin_flag = mysqli_fetch_array($tqr_admin_flag)){

        //print_r($row_admin_flag);

        $tqs_admin_flag_thread = "SELECT * FROM `thread` WHERE `id` = '" . $row_admin_flag['thread_id'] . "' ORDER BY `date_created` DESC";
        $tqr_admin_flag_thread = mysqli_query($dbc, $tqs_admin_flag_thread) or die(mysqli_error($dbc));

    while($row = mysqli_fetch_assoc($tqr_admin_flag_thread)){

        include($_SERVER['DOCUMENT_ROOT'] . "/gallerysite/admin_flag_thread.php");


    }
}

?>

This in comparison works:

$tqs_admin_flag_thread = "SELECT * FROM `thread` ORDER BY `date_created` DESC";

I would appreciate suggestions for which reasons the above script (the way it is) may not work.

 

With the above script the querying and printing on screen does happen, though the "ordering by DESC" does not happen.

Edited October 24, 2014 by glassfish

[Ch0cu3r]

What format are the dates stored in the date_created column and what data type have you used?

 

Using queries within loops is not recommended. If the data in the two tables relate then you should use JOIN's. Example query

SELECT f.*,                 # DO NOT DO THIS - explicitly state the columns you want to return from both tables. 
       t.*,
FROM flags f
JOIN thread t on t.id = f.thread_id
ORDER BY t.date_created DESC

[glassfish]

I have given the column the name "date_created" then I chose "datetime", and nothing additionaly.

 

This is an example on how the datetime gets stored:

2014-10-22 13:18:50

Edited October 24, 2014 by glassfish

[ginerjm]

Not sure at all what you are doing here.  You run the query with the desc option in it but then you process the results by doing an Include statement over and over.  How do you know its not working?  You're not displaying any of the results?

 

Please stop posting your code in chunks.  It is impossible to get a complete picture of your problem when you break it up like that and we can't see the real picture.  Isolate the code you have a problem with and then post it intact.

[glassfish]

"Displaying the results" does happen, just the "ordering by DESC" does not happen. I will try the "join" suggestion a bit later.

[Ch0cu3r]

Whats the relationship between the two tables? One to many (a flag can have multiple threads assigned) or one to one (a flag can only have one thread assigned)? If its the latter then the results for the second query will never be ordered by date_created as only one result will be returned for each flag.

Edited October 24, 2014 by Ch0cu3r

[Psycho]

 . . . just the "ordering by DESC" does not happen.

 

How do you know "it is not happening"? There isn't anything in that code that would display anything to do with the records to even know what records were returned. You are just including the same file over and over. Perhaps you are using the value of $row in that include file. Try echoing the results of the query instead of doing the include and post the results. Try this ONE query instead of the looping queries. Also, you shouldn't use "*" in your select statement. List out the actual fields you need.

 

 

$query = "SELECT *
          FROM `flags`
          JOIN `thread` ON thread.id = flags.thread_id
          ORDER BY thread.id ASC, thread.date_created DESC";

[glassfish]

Thanks a lot for the answers!

 

Psycho, thanks a lot for the SQL query! It works! The "order by" works for myself too!