[SOLVED] Query Returns Different Results via PHP

[JackR]

I'm relatively new to both PHP and MySQL, so perhaps this is obvious to someone else. I have a phpbb forum and a website using PHP. I've created a sort of staff blog for the website, using the forum database. I just added a couple tables and everything meshes nicely. However, my visitors complain that it's difficult to tell who's updated their blog without reading all of them, so I wanted to create a dynamically changing list of all the bloggers, with the ones who have most recently updated their blogs on top. When I run the following query on my database, I get exactly the result I want

 

SELECT DISTINCT u.username FROM xphpbb_users u, blog_posts o WHERE u.user_id=o.author GROUP BY o.postnum DESC

 

This gets me the usernames of all my bloggers, without repeats, and orders them by the most recent blog post first, and the oldest last. However, when I try to use the PHP code of the blog.php page to make this list, the names don't return in the same order; they're completely wrong. The order is the same as if I had run the query

 

SELECT DISTINCT u.username FROM xphpbb_users u, blog_posts o WHERE u.user_id=o.author ORDER BY o.postnum DESC

 

Where it starts at postnum 1, notes the username, goes all the way through, and then orders the ones it's returned (let's say they correspond to postnums 1, 5, 8, 16, and 24) by the postnum. However, this result shows me the bloggers in the order of who most recently STARTED a blog (made a first post).

 

How is it that a query works right when I run it myself, but the PHP returns the wrong result? How do I go about getting the result I want? I've heard about creating temporary rows to artificially read from the bottom up, but that seems complicated. Of course, I could always use two queries, but it looks like I should be able to get it all done with just the one.

 

Any help is much appreciated.

[BYGino]

Hi,

 

I had the exact same problem a couple of years ago trying to group items and list them sequentially in groups and never did quite get to the bottom of it. In the end I chose to hactk the PHP code using loops to pull the data in the order I wanted.

 

Don't suppose that's the answer you are looking for but it's an idea if you don't have any luck with it. I wouldn't really advise it though, it messy and bloated but it works for the task I needed it for.

[fenway]

I would have to argue that the situation you describe is impossible -- if you specify an ORDER BY clause, MySQL will return the recordset in that order... period.

[littledragon]

Are you using php_mysql or php_mysqli ?

 

(Whoops, removes second question cos had missed important part of original question)

[JackR]

# MySQL client version: 4.1.15

# Used PHP extensions: mysql

 

That's the info from the database itself.

 

I would have to argue that the situation you describe is impossible -- if you specify an ORDER BY clause, MySQL will return the recordset in that order... period.

I would love to agree with you, because that's how things work in my mind. Upon further investigation, I'm leaning more and more towards the error being in PHP's interpretation of the data. If I run the query on the database "SELECT DISTINCT author from blog_posts ORDER BY postnum DESC" I get the results:

author

1573

18

1456

5329

475

937

214

2703

498

5

2208

27

 

And if I look at the actual data, this is correct. User 1573 made the most recent post, and number 18 came before that. But when I run the following PHP:

$query = "SELECT DISTINCT author from blog_posts ORDER BY postnum DESC";

$result = mysql_query($query) or die(mysql_error());

while($line = mysql_fetch_array($result, MYSQL_ASSOC)){

print $line['author']."<br>";

}

I get the result of:

214

2208

5329

1573

498

2703

475

1456

27

5

937

18

 

Which is true in the sense of user 18 made the first post, the next user to BEGIN posting was 937. Those two made some posts and then user 5 made their first post, so on and so forth until user 214 was the most recent to begin posting.

 

I agree with you; this shouldn't be possible. Yet, somehow the data I'm getting right from my personal queries on the database is not the same as the data the PHP is getting when it runs the exact same queries on the exact same database.

 

Could there be an error on the part of the PHP backend or something that's causing this to happen? I figured that I could use two queries in succession here; however since the first query suffers from the same flaw as the original one-query approach, calling my second query results in the same wrong answer. I'm really at a loss.

[fenway]

As I see it, the real problem -- which I didn't notice before -- is that you're using DISTINCT.  That's effectively a GROUP BY -- which means that any non-aggregate column (in this case, postnum) will contain meaningless information not related to the aggregate column (in this case, author).  You can't really do this and expect to get meaningful results.

[JackR]

Then, ignoring the odd issue of getting two different sets of results, how would you suggest going about my query?

 

I essentially need to query the entire table starting from the bottom and going up. Obviously, I want to be able to do this as efficiently as possible, since this will end up being rather large as the table continues to grow. Just as a refresher, the purpose of this is so that I can create a list that shows who has posted most recently.

 

Is there a way to create a query that will still find distinct authors and order by postnum without making the obvious observation that all postnums are unique, and thus including ALL rows in the result? I've tried several queries involving all sorts of DISTINCT, ORDER BY, and GROUP BY, and even started trying to use JOINs, but all to no avail.

[fenway]

There are a few ways to deal with this... do you really need to look though all of the posts? Or just the ones in the last hour/day/week?  That would simplify matters a great deal.

[JackR]

That is one approach I've looked at, but that would require two queries. I list all of the bloggers in a Combo Box, and that query I've been using is what gets the list of all the bloggers. A second query could easily figure out who made the last 5 posts, and put those people at the top of the list.

 

However, I'm hoping to execute one query that will get all of my bloggers and put them in the right order, so that I can just just use the standard while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) right away to add the names to the Combo.

[fenway]

I'm not sure what you mean... all you'd need would be a MAX() and a group by -- one query.

[JackR]

Ah! I hadn't even thought to do it that way! It works perfectly. For those interested, the result for my situation was:

 

SELECT u.username, MAX(p.postnum) as post FROM xphpbb_users u, blog_posts p WHERE u.user_id = p.author GROUP BY u.user_id ORDER BY post DESC

 

Thank you very much for your help.