mysql LIKE returning only one row when three match.

Question

When I query my database with the following in my file, search.php, it only returns the first result it comes across.

$qry = "SELECT business_id FROM business WHERE zip like '%91326%'";

$rs = mysql_query($qry);
$rec = mysql_fetch_array($rs);
echo $session->showContents($rec);

showContents is just a utility function...

function showContents($array)
{
        echo "<pre>";
        print_r($array);
        echo "</pre>";
}

showContents returned this:

Array
(
    [0] => 3
    [business_id] => 3
)

The crazy thing is, when I put the same query in sqlbuddy it gives me:

business_id
3
5
6

I am at a loss

Answer 1

mysql_fetch_array fetches only a single row. You want to use it several times to build an array with the entire result set:

$rec = array();

while(($row = mysql_fetch_array($rs)) !== FALSE) {
    $rec[] = $row;
}

If you just want the ID's you want to select the ID:

$rec = array();

while(($row = mysql_fetch_array($rs)) !== FALSE) {
    $rec[] = $row[0];
}

Answer 2

Try this:

$qry = "SELECT business_id FROM business WHERE zip like '%91326%'";
$rs = mysql_query($qry);
while ($rec = mysql_fetch_array($rs)) {
    echo $session->showContents($rec);
}

Answer 3

That's because mysql_fetch_array only fetches a single row from the result set.

Typically you use it like this (from the manual):

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    printf("ID: %s  Name: %s", $row[0], $row[1]);  
}