[RESOLVED] mysql advancing two rows on fetch

vpeppers

I am a newbie at php. I am experienced in ASP and ASP.net, but this is my first time on php.

I have a site that is using a mysql database. I have everything working, except that I seem to be fetching two rows at a time, or at least every other row. The first time I do a fetch, it is outside of the do loop. It works fine. Then I enter the do loop, and I get every other record.

When I run the following code, I get every other row after the first one. If I am looking for ten verses, I will get verse 1, 2, 4, 6, 8, 10. I know that the database has the verses in it, because if I look for verse 2-11, I will get 2,3,5,7,9,11. I can find no reason for this.

//Open bible table
		$sSQL="SELECT ID, Book, BookNo, Chapter, VerseNo, VerseText FROM KJV ORDER BY ID;";
		$result=mysql_query($sSQL);
//Move to start reference
	//Get the start book.
		do	{
			$row=mysql_fetch_array($result);
			}
		while($row['BookNo']!=$startbookno);
	//Get the start chapter.
		while($row['Chapter']!=$startchap)
			{
			$row=mysql_fetch_array($result);
			}
		if ($row['Chapter']!=$startchap)
			{
			mysql_free_result($result);
			print "That chapter does not exist.<br>"."\r\n";
			return $function_ret;
			} 
	//Get the start verse.
		while($row['VerseNo']!=$startverse)
			{
			$row=mysql_fetch_array($result);
			}
		if ($row['VerseNo']!=$startverse)
			{
			mysql_free_result($result);
			print "That verse does not exist.<br>"."\r\n";
			return $function_ret;
			} 
		$flag=false;
		$bflag=false;
        $lastbookno=0;
        $exitflag=false;
      do
      {
        if ($row["BookNo"]>$lastbookno)
        {
          printf('<div class="book">%s</div>',$row["Book"]);
          $lastbookno=$row["BookNo"];
          $lastchapter=0;
          $flag=false;
        } 
        if ($row["Chapter"]>$lastchapter)
        {
          if ($row["Book"]=="Psalms")
          {
            print '<div class="chapter">Psalm '.$row["Chapter"].'</div>';
          }
            else
          {
            print '<div class="chapter">Chapter '.$row["Chapter"].'</div>';
          } 

      $lastchapter=$row["Chapter"];
    } 

    print '<div class="verseno">'.$row["VerseNo"].'</div>';
    print '<div class="verse">'.$row["VerseText"].'</div>';
    printf ("  ID before fetch=%s<br/>",$row["ID"]);
    $row=mysql_fetch_array($result);
    printf ("  ID after fetch=%s<br/>",$row["ID"]);
    if (EndOfRef($result)) 
        {$endflag=true; 	//Not at end of recordset
        print "End of Ref";}
  	if ($row["BookNo"]>intval($endbookno)) {$endflag=true;	//Current book number does not exceed the end book number
  		printf ("BookNo=%s endbookno=%s",$row["BookNo"],$endbookno);}
	if (($row["BookNo"]==intval($endbookno)) && 	//Current book = last book no and
  		($row["Chapter"]>intval($endchap))) {$endflag=true;			//Current chapter does not exceed end chapter number.
  		printf ("BookNo=%s endbookno=%s Chapter=%s endchap=%s",$row["BookNo"],$endbookno,$row["Chapter"],$endchap);}
	if (($row["BookNo"]==intval($endbookno)) && ($row["Chapter"]==intval($endchap)) && ($row["VerseNo"]>intval($endverse)))
		 {$endflag=true;
  		printf ("<br/>BookNo=%s endbookno=%s Chapter=%s endchap=%s VerseNo=%s endverse=%s ID=%s",$row["BookNo"],$endbookno,$row["Chapter"],$endchap,$row["VerseNo"],$endverse,$row["ID"]);}

  } while (!$endflag);
  mysql_free_result($result);

I know that you experts out there can write the code much more efficiently than what I have, but right now, I don't need efficiency. I need it to work. Any help will be appreciated. Comments on my code will also be appreciated, as I try to learn this new language, but I need concentration on my problem.

bradgrafelman

The logic of the code is rather confusing to me; it looks like you're trying to skip ahead in the result set until you find the 'BookNo' you want, etc. etc. for the rest of the fields. If you know which BookNo you want and have a range of chapters/verses, why not add all of those conditions into a WHERE clause of the SELECT query? That way, you'd simply loop through the result set as it is without a bunch of confusing do/while loops that are skipping rows all over the place.

vpeppers

I know that it is confusing to get to the loop. The reason that I have all of that is that when I built the SQL query string, it didn't work. This did. I have no trouble getting to the book, chapter and verse that I want, but then the problem starts. It is in that do-while loop. I need to get that fixed. Regardless of how many rows my SQL statement returns, I need it to not skip rows. If I can get this working, then I can clean up the rest of it later. What I currently have returns the rows that I need. It may return more than I need, but it does get what I need. Now I need to be able to traverse the rows.

wmcelwee

$sSQL="SELECT ID, Book, BookNo, Chapter, VerseNo, VerseText FROM KJV ORDER BY ID;";
$result=mysql_query($sSQL);
while($row = mysql_fetch_array($result)){
...
}

This will loop through the entire result set where $row will contain each row returned by your query. mysql_fetch_array() will move the pointer forward automatically so whenever you call it, you are moving to the next row. If you call it multiple times or within the loop you may "skip" rows.

vpeppers

Thanks for your replies. I found the problem, but I still don't understand why it did what it did. In my condition for the "while", I was calling a funtion that evaluated the complex condition of completion. The complication is that chapter number and verse number can be reset to one within a given reference. Only the bookno could be consistently counted on to increase. If the reference was, for example, John 19:7-Acts 1:15, you can't stop at any given verse number. A test had to check and it had three parts. I had mistakenly tried to pass the result from mySQL as a function parameter, and then did another mysql_fetch_array. This was, of course, advancing the row one. What I don't understand is why, when I wrote out the row ID just before the fetch and just after it, it advanced 2, and not 1, with the other row advance happening when I hit the condition function. I may never understand, but that is alright. I learned a lot with this little exercise. Problem is solved, and everything is working. You may check out the result at www.princevillebaptist.org/read.html.