Need help ordering data from mysql database.

[Punk Rock Geek]

First, I will explain what my code is designed to do.

This part selects all of the contacts (contact_id) on user 1's (my) friend list. It turns them into a single variable, $contacts.

$query = $DB->query( "SELECT contact_id FROM ibf_contacts WHERE member_id='1'");


while ($row = mysql_fetch_assoc($query)) { 
$contacts = $row['contact_id'];

This next part displays all "posts" where the blog IDs (id_blog) are identical to my contacts IDs. In other words, it displays all blog entries of the people on my friends list. It orders them by "id", which is the id of the blog entry. (Chronological order)

$query2 = $DB->query( "SELECT post FROM mkp_blog_post WHERE id_blog='$contacts' ORDER BY 'id'");

while ($row = mysql_fetch_assoc($query2)) { 
    echo $row["post"]; 
}

The problem is that it is not ordering them correctly. For example:

Entry 1, Written By Person A
"Hello, How are you!"

Entry 2, Written By Person B
"I am doing fine."

Entry 3, Written By Person A
"What are you doing today?"

Entry 4, Written By Person B
"Not much."

The above is the order it should display in. However, this is how it is actually being displayed:

Entry 1, Written By Person A
"Hello, How are you!"

Entry 3, Written By Person A
"What are you doing today?"

Entry 2, Written By Person B
"I am doing fine."

Entry 4, Written By Person B
"Not much."

So what it's doing, is ordering them first based on user ID, and THEN based on the ID of the entry. (Which is just another way of saying the chronological order). Also, I did not show this above, because I didn't want to make things confusing, but it is also ordering them from oldest to newest, and not newest to oldest. I'm sure there is a fix for this? Thanks!

[GJay]

ORDER BY 'id'
means that your rows will be ordered by the string 'id', which is pretty meaningless.
try it without the quotes.

[Punk Rock Geek]

I changed it, but there was no effect.

[littlejones]

You only need quotes in the query for ORDER BY if you are referring to a variable for instances '$id' in which case you can remove the quotes and refer to it simply as id. However, you've said this hasn't worked, so I would suggest you check your database to make absolutely certain that you have a field called 'id' in ibf_contacts and mkp_blog_post tables and that you have the letters in the right case. If not then there's your problem.

[Punk Rock Geek]

You guys were right. It was just my code that was faulty. This is my new code, which combines everything into a single query:

$query = $DB->query( "SELECT mkp_blog_post.post FROM mkp_blog_post, ibf_contacts WHERE mkp_blog_post.id_blog=ibf_contacts.contact_id AND ibf_contacts.member_id='$idu' ORDER BY mkp_blog_post.id DESC");

while ($row = mysql_fetch_assoc($query)) { 

    echo $row["post"]; 

}

Thanks.

I just have one more small problem. How do I seperate each blog "post" by a vertical space? (<p>)

[littlejones]

Ok, well if you assume that everything that goes on inside the While statement is looped, all you have to do is add a line break after the echo, and then every echoed blog post will be followed by a line break. So you could use...

PHP Code:


<?php

while ($row = mysql_fetch_assoc($query)) { 

    echo $row["post"]."<br/>"; 

}

?>

Alternatively you could jazz things up a little bit and use a table...

PHP Code:


<table border="1">

<?php


while ($row = mysql_fetch_assoc($query)) { 

?>

   <tr><td><?php echo $row["post"]."<br/>"; ?> </td></tr>


<?php

}

?>

</table>

There is a way to use /n inside php code as a line break instead of using html but the above should be fine.