CODEWITHSUNDEEP
phpmysql
Ron Schlegel
Ron Schlegel

Reputation: 11

php mysql only returning first record

I have this following code in a switch statement since the query can vary based on screen-name(1 word), full name (2 words...maybe 3), or the email address which "explode"'s to 3 words (I put it back together later, no biggy). However, cannot locate the problem in the following code:

$query = "SELECT * FROM personal WHERE FirstName='$searchName' OR LastName='$searchName' OR Email='$searchName' OR displayName='$searchName'";
$result = mysql_query($query) or die(mysql_error());

echo "<center><table border='1' width='70%'>";
if (mysql_num_rows($result)) {
    $row = mysql_fetch_assoc($result);
    $myBuddy = $row['FirstName'].
    " ".$row['LastName'];
    $picName = $row['FirstName'].$row['LastName'].$row['RecNum'];

    if (file_exists('../members/'.$picName.
        '/profile.jpg')) {
        $picLine = "<img src = '../members/".$picName.
        "/profile.jpg' alt='profile' height='100' width='100' />";
    }

    echo "<tr>";
    echo "<td style='text-align:center'>".$picLine.
    "</td>";
    echo "<td style='text-align:center; color:red'>".$myBuddy.
    "</td>";
    echo "<td style='text-align:center'><input type='button' value='Add Buddy'></td>";
}

echo "</table>";
break;

Problem is, I have several people in my MySql database with the same last name (Jones), however in the code above only returns the first occurance of someone with the last name Jones. Trying to return them all, not just one. I know I am just overlooking something small & stupid - been doing php/mysql for 2 years now - never had to do a search page before. Any help is greatly appreciated.

Upvotes: 0

Views: 138

Answers (2)

Exwolf
Exwolf

Reputation: 157

You have too loop the results, like in the following example:

echo "<center><table border='1' width='70%'>";
if (mysql_num_rows($result) )
{
    while($row = mysql_fetch_assoc($result)) {
        $myBuddy = $row['FirstName'] . " " . $row['LastName'];
        $picName=$row['FirstName'].$row['LastName'].$row['RecNum'];

        if (file_exists('../members/' . $picName . '/profile.jpg'))
        {
            $picLine = "<img src = '../members/" . $picName . 
                       "/profile.jpg' alt='profile' height='100' width='100' />";
        }

        echo "<tr>";
        echo "<td style='text-align:center'>" . $picLine . "</td>";
        echo "<td style='text-align:center; color:red'>" . $myBuddy . "</td>";
        echo "<td style='text-align:center'><input type='button' value='Add Buddy'></td>";
    }

    echo "</table>";
    break;
}

Above, I have used a while loop.

while($row = mysql_fetch_assoc($result)) {
   // code
 }

Upvotes: 1

Davit
Davit

Reputation: 1375

while ($row = mysql_fetch_assoc($result)) { 
   //...
   //use $row
}

instead of 

$row = mysql_fetch_assoc($result);

Upvotes: 3

Related Questions