...

View Full Version : Need help ordering data from mysql database.



Punk Rock Geek
11-05-2006, 08:20 PM
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
11-05-2006, 08:27 PM
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
11-05-2006, 08:33 PM
I changed it, but there was no effect.

littlejones
11-06-2006, 12:25 AM
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
11-06-2006, 01:10 AM
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. :thumbsup:

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

littlejones
11-06-2006, 08:36 AM
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

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

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

}

?>



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




<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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum