php mysql only returning first record

Question

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.

Who want's to decode the awfully indented source code. Please, take more time when posting your next question. Anyway, I edited a bit... — SteAp
– SteAp, Commented Aug 31, 2013 at 0:13
2 years and you've never had to make a table out of a database query before? Search pages aren't the only time we do this. — Barmar
– Barmar, Commented Aug 31, 2013 at 0:14

Davit · Accepted Answer · 2013-08-31 00:11:01Z

3

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

instead of

$row = mysql_fetch_assoc($result);

answered Aug 31, 2013 at 0:11

Davit

1,3985 gold badges22 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Filipe Silva · Accepted Answer · 2013-08-31 00:28:22Z

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
 }

Collectives™ on Stack Overflow

php mysql only returning first record

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related