Outputs all minus one

Question

I use this code to output all threads with a specific forum ID

$query = mysql_query("SELECT * FROM forums JOIN threads ON threads.fid = forums.id WHERE     forums.id = ".intval($_GET['forumID']));
$forum = mysql_fetch_assoc($query);

?>

<h1><a>Forums</a> &gt; <?=$forum['name']?></h1>

<?php while ($thread = mysql_fetch_array($query)): ?>

<?=$thread['title']?>

<?php endwhile; ?>

forums
id, name, description

threads
id, title, message, fid

Thing is that it outputs all threads exept one. If I remove one it just hides another. Why is that?

Would really appreciate some help!

sorry my english is not that good, lets say i have 4 records in threads with fid = (in this case 1), it only outputs 3 of them

Answer 1

You are using the first row returned in the mysql_fetch_assoc in line 2. In your while loop condition, the mysql_fetch_array function will starts with the second row of the sql query, not the first. Therefore, the id from the first row is missing in your list. However, if you use the column name, you should also use mysql_fetch_assoc, not mysql_fetch_array. This should not give you any ids.

Answer 2

The reason you have one fewer threads than you're expecting is that you're fetching the first thread in line 2, but only using it to display the forum name. You then fetch the second row in the while loop and start from there to list the threads - missing the first row.

I'd refactor your code using the do..while control structure in PHP, as follows:

$query = mysql_query("SELECT * FROM forums JOIN threads ON threads.fid = forums.id WHERE forums.id = ".intval($_GET['forumID']));
$thread = mysql_fetch_assoc($query);

if ($thread != null) {
    echo "<h1><a>Forums</a> &gt; $thread['name'] </h1>";

    do {
        echo $thread['title'];
    } while ( $thread = mysql_fetch_array($query)) );
}

Answer 3

As txwikinger pointed out, the internal array pointer is moved forward by mysql_fetch_assoc(), so the first time you use mysql_fetch_array(), you'll get the second row. You can reset the array pointer with mysql_data_seek() before using the array a second time.

 mysql_data_seek($query, 0);

I'd rather restructure the code to use two queries though, one to get the forum name in one tiny query, and one to get all the threads in another query.