SQL Loop - Multiple Rows

ironclaw · 17-02-2012 09:08AM #1

Hi Folks,

Currently migrating from the old SQL to the new SQL methods in PHP (I learnt from a very old book)

Here is my script:

//Set the Query
$query = "SELECT * FROM accounts WHERE name LIKE '%".$searchString."%'";
echo $query; //For debug

//Run the Query
$result = mysql_query($query, $link);
if (!$result) {
    echo "DB Error, could not query the database\n";
    exit;
}

$num_rows = mysql_num_rows($result); //Get the number of results
echo $num_rows; //Works fine. Get 2

for ($i=0; $i < $num_rows; $i++) {
$arry = mysql_fetch_assoc($row);
echo $arry['fieldName'];

}

The problem is the last bit. It only echoes one row and finishes. I know there are two results as my $num_rows echoes 2. The older script worked fine.

I would be looking for the eqvilent of this:

$result = $db->query($query);
$num_results = $result->num_rows;
$row = $result->fetch_assoc();

for ($i=0; $i < $num_results; $i++) {
$row = $result->fetch_assoc();
echo $row['name'];
}

Any help greatly appreciated. Its probably just one line somewhere.

Thank you!

lynchie · 17-02-2012 09:23AM

Look at the code you included. Where is $row declared.. It should be the resultset you are passing to it?

damoth · 17-02-2012 09:40AM

what about ...

while ($row= mysql_fetch_assoc($result)) {
     echo $row['fieldName'];
}

or

while ($row = $result->fetch_assoc()) {
     echo $row['name'];
}

seamus · 17-02-2012 09:40AM

As lynchie says, you have $row where $result should be.

Tbh, I find mysql_num_rows() and mysql_affected_rows() to be a little unreliable. I don't know why, I just don't really trust them

The way that I would always get rows from a resultset is as below

$result = mysql_query($query, $link);

while($row = mysql_fetch_assoc($result)) {
 // Do Stuff
}

When you put an assignment statement into the condition of a while loop, it first carries out the assignment, then checks the value of the assigned variable.

In this case, it does the operation $row = mysql_fetch_assoc and then checks the value of $row.
If mysql_fetch_assoc returns data, then $row will be TRUE (any value != 0 is TRUE in PHP) and the contents of the while statement are executed. If mysql_fetch_assoc does not return data (because you are at the end of the resultset or there is no data), then it returns 0 and sets $row = 0. This then results in a FALSE in the condition and the while loop exits.

The only benefit of doing it this way is slightly neater and more efficient code.

ironclaw · 17-02-2012 09:42AM

Got it working, in a nutshell:


$result = mysql_query($query, $link);//Run the query

while($row = mysql_fetch_array($result)) {
echo $row['name']; //Echos every row in turn
}

Thank you for the reply.

SQL Loop - Multiple Rows

Comments