Problem retrieving data from database

[Lauj]

Below is what I have and I'm not quite about the retrieving data frm MySQL database.

require_once('Connections/alumniConnection.php'); 

mysql_select_db($database_alumniConnection, $alumniConnection);
$query_rsArchived = "SELECT DISTINCT Description FROM photoGallery WHERE category = 'archived'";
$rsArchived = mysql_query($query_rsArchived, $alumniConnection) or die(mysql_error());
$row_rsArchived = mysql_fetch_assoc($rsArchived);
$totalRows_rsArchived = mysql_num_rows($rsArchived);

echo "<?xml version=\"1.0\" encoding=\"iso-8859-1\"?".">";

And then in the body, I have this:

for($numOfCells=0; $numOfCells<=$totalRows_rsArchived; $numOfCells++){
		echo "Results2: ".$row_rsArchived["Description"]."<br>";
		while($row_rsArchived = mysql_fetch_assoc($rsArchived)){			
			echo "Results3: ".$row_rsArchived["Description"]."<br>";
			}
		}

The above body tag codes displayed this:

Results2: Appleton Gathering
Results3: Wausau Gathering
Results3: Retired Faculty/Staff Breakfast
Results2:
Results2:
Results2:

However, if use this body tag code:

for($numOfCells=0; $numOfCells<=$totalRows_rsArchived; $numOfCells++){
		echo "Results2: ".$row_rsArchived["Description"]."<br>";
		}

Then the result display this:

Results2: Appleton Gathering
Results2: Appleton Gathering
Results2: Appleton Gathering
Results2: Appleton Gathering

And if I use the body tag code:

while($row_rsArchived = mysql_fetch_assoc($rsArchived)){			
			echo "Results3: ".$row_rsArchived["Description"]."<br>";
			}

Then the result displayed:

Results3: Wausau Gathering
Results3: Retired Faculty/Staff Breakfast

Now, my question is how do I get it to display as show below?

Results2: Appleton Gathering
Results2: Wausau Gathering
Results2: Retired Faculty/Staff

What's the proper loop?

ljCharlie

[eoghain]

The proper loop for this is:

$rsArchived = mysql_query($query_rsArchived, $alumniConnection) or die(mysql_error());
//$row_rsArchived = mysql_fetch_assoc($rsArchived);  // Don't do this here
$totalRows_rsArchived = mysql_num_rows($rsArchived);

// Make sure you got some results
if ($totalRows_rsArchived > 0)
{
   while($row_rsArchived = mysql_fetch_assoc($rsArchived)){            

      echo "Results2: ".$row_rsArchived["Description"]."<br>";
      }
}

[Lauj]

Many thank for the help. It works, but will you explain to me why I shouldn't use the line $row_rsArchived = mysql_fetch_assoc($rsArchived); until I'm in the while loop?

ljCharlie

[Lauj]

One more thing. How do I do the opposite of while($row_rsArchived = mysql_fetch_assoc($rsArchived)){
}
? Meaning, how do I test to when there is nothing in the array? I want to do a loop like, while there is nothing in the $row_rsArchived = mysql_fetch_assoc($rsArchived), do something. Can this be done? If so, what's the proper coding?

Or perhaps, it's not even a while loop. I want to test when the array of the values are out or at the end.

ljCharlie

[eoghain]

Ok, when you call [man]mysql_fetch_assoc/man it removes the first row off of the result set and gives it to you. This means you have to do something with it. One solution is to get the row, print it out, and then start your loop that will print all of the rest of the rows. While this works and is a viable pattern for me its easier, and nicer, to just do all of the work in the loop.

As for doing something else when there is no data in the array, you just need to do that in the else part of the if statement I used:

// Make sure you got some results 
if ($totalRows_rsArchived > 0) 
{ 
   while($row_rsArchived = mysql_fetch_assoc($rsArchived)){   

      echo "Results2: ".$row_rsArchived["Description"]."<br>"; 
   } 
}
else
{
   echo "Sorry no data<br>";
}

Using [man]mysql_num_rows/man to determin if your query returned any data is a much better way to deal with this.