[SOLVED] Problem With Query Limits

[bsknoelk]

<?php

  $fetch_mostrecentarticles = mysql_query('SELECT * FROM articles ORDER BY id DESC LIMIT 0,10');
  $display_mostrecentarticles = mysql_fetch_array($fetch_mostrecentarticles);

  while ($display_mostrecentarticles = mysql_fetch_array($fetch_mostrecentarticles))
   {
   echo '<li><a href="/articles/view.php?id='. $display_mostrecentarticles['id'] .'">' . $display_mostrecentarticles['date'] . '<br />' . $display_mostrecentarticles['title'] . '</a></li>';
   }

?>

 

I'm not really sure why all of my most recent articles aren't being displayed.  I have exactly 8 articles in my database, so all of them should be called up into this list, however, I get a return of #'s 7 through 1.  Where is my #8 article?

 

I have tried the mysql_error() but as I am getting some results, no error was reported.

[Psycho]

The reason is simple. You extract the first record from the result set and don't use it. Then when you start the WHILE loop you are starting with the second record and continuing through the rest of the result set

 

<?php

  $query = "SELECT id, date, title FROM articles ORDER BY id DESC LIMIT 0,10";
  $result = mysql_query($query) or die(mysql_error());

  while ($record = mysql_fetch_assoc($result))
  {
    extract($record);
    echo "<li><a href=\"/articles/view.php?id={$id}\">{$date}<br />{$title}</a></li>\n";
  }

?>

[bsknoelk]

That cleared up my problem, of course, so thank you.

 

I upgraded to using fetch_assoc rather than fetch_array.  That was easy.  And I've read now about "extract".  But I'm still wondering what you meant when you say I extracted the first record  from the result and didn't use it.  The first result (my article # went somewhere, I suppose.

 

If not knowing the answer to this question doesn't change anything, then I won't bother because the php is working, but I would still like to know.  I'm quite the intrepid learner.

 

Thanks.

[Psycho]

Well, using fetch_assoc() and extract were only added to show ways to make the code more efficient/readable - they had nthing to do with the real solution. The real problem was that the original code was extracting the first record, then when the while() loop starts it would extract the next record (thus the first record was dropped)

 

Here's the original code with comments to explain why the first record in the result set was not displayed

<?php

  $fetch_mostrecentarticles = mysql_query('SELECT * FROM articles ORDER BY id DESC LIMIT 0,10');

  //First record in result set is extracted and assigned to $display_mostrecentarticles
  $display_mostrecentarticles = mysql_fetch_array($fetch_mostrecentarticles);

  //Nothing is done with $display_mostrecentarticles before the while loop

  //The while loop STARTS by extracting the NEXT record from the result set. So, it will start
  //from the second record since the first one was already extracted above. The loop will then
  //continue through the remainder of the records in the result set. In other words, this loop will
  //only process records 2-10 (assuming there are 10 records in the result set). The solutin was
  //to remove the mysql_fetch_array() that comes before the WHILE loop.
  while ($display_mostrecentarticles = mysql_fetch_array($fetch_mostrecentarticles))
   {
   echo '<li><a href="/articles/view.php?id='. $display_mostrecentarticles['id'] .'">' . $display_mostrecentarticles['date'] . '<br />' . $display_mostrecentarticles['title'] . '</a></li>';
   }

?>

[bsknoelk]

//First record in result set is extracted and assigned to $display_mostrecentarticles

 

O I C.  Rather, oh, I see.

 

Thanks again.