Outputting multiple rows from database using MySQL and PHP

Question

I've written some simple php code designed to retrieve data from a database based on the session id. If the session ID is 1 then I query the database to find all entries with the UserID of 1 and then output them.

This works to a certain extent, I can get the query to output the correct entries but for some reason it never outputs the most recent entry, instead it skips it and outputs the rest instead.

My code is below, can anyone spot what I'm doing wrong?

Thanks

$id = $_GET['id'];
if ($id) 
{
$sql = "SELECT * FROM `forum_topics` WHERE `uid`='" . $id . "'";
$res = mysql_query($sql) or die(mysql_error());

if (mysql_num_rows($res) == 0) 
{
    echo "You haven't got any adverts to display";
} 
else 
{
    $row = mysql_fetch_assoc($res);

    echo "<table border=\"0\" cellspacing=\"3\" cellpadding=\"3\" width=\"100%\">\n";

    echo "<tr align=\"center\"><td class=\"forum_header\">Title</td><td class=\"forum_header\">User</td><td class=\"forum_header\">Date Created</td></tr>";

    while ($row2 = mysql_fetch_assoc($res)) 
    {
        echo "<tr align=\"center\"><td><a href=\"index.php?act=topic&id=" . $row2['id'] . "\">" . s($row2['title']) . "</a></td><td>" . uid($row2['uid']) . "</td><td>" .                 $row2['date'] . "</td></tr>";
    }
    echo "</table>\n";



}
}

you have a very nasty SQL-injection bug there. – Johan Apr 27 '11 at 21:42 — Johan, Apr 27 '11 at 21:42

score 2 · Answer 1 · edited May 23 '17 at 10:33

First things first

$id = $_GET['id'];
if ($id)

The code above has an SQL-injection bug! Change it and all code like it everywhere to

$id = mysql_real_escape_string($_GET['id']);
if ($id)

For info on SQL-injection see: How does the SQL injection from the "Bobby Tables" XKCD comic work?

Secondly your question

I think your problem is might be transactions.

The most recent entry of your user has not been committed yet.
This means that whilst it has not been committed, only the user that posted it can see it (because the entry is in his transaction).

People in other sessions (including you) will not see that entry until after the transaction of that user is closed.

score 1 · Accepted Answer · answered Apr 27 '11 at 21:35

1

It looks to me like this line is the problem: $row = mysql_fetch_assoc($res);

That line is fetching the first row, so your while loop starts at the second row (since you already fetched the first row, and the pointer was moved to the next row).

Try removing the line I mentioned and see if it works.

answered Apr 27 '11 at 21:35

Compeek

909
5
13

Ah yes, such a simple mistake and I missed it! That's what happens when you're staring at code for too long. Thanks for your help, greatly appreciated! – woolm110 Apr 27 '11 at 21:37
Haha, I know exactly what you mean. No problem! – Compeek Apr 27 '11 at 21:38

Outputting multiple rows from database using MySQL and PHP

2 Answers2