How to show all data one after another from mysql_fetch_array() in php

Question

    $sql="SELECT userId FROM eventmember WHERE eventid='$event_id';";`
    $resultset = mysql_query($sql);
    $row = mysql_fetch_array($resultset); 

I got specific userid from specific event column like eventid==> eid-01(us-0, us-3,...),

    $num_row = mysql_num_rows($resultset);
    while($row) {           
      for($i = 0; $i<$num_row; $i++) {          
        $sql = "SELECT userId, firstName FROM userinfo WHERE userId='$row[$i]';";
        $resultset = mysql_query($sql);
        $row22 = mysql_fetch_array($resultset);
        $us_id = $row22['userId'];
        $us_name = $row22['firstName'];

        echo "<tr>";
        echo "<td>ID:</td> <td class='text2' align='center' colspan='2'>
        <b> $us_id </b>
        </u></td>";
        echo "</tr>";
        break;
      }
      $row = mysql_fetch_array($resultset);
    }

On that code I got only one userid info but there is more userid against one event.

Answer 1

Simple Solution

You need to get multiple userIds from eventmember table which have multiple users against each event. But you are fetching only once from that query with $row = mysql_fetch_array($resultset);, So you should get only one user, what you are getting now. Hence, the problem is, you actually have put the while loop in a wrong place. The loop should be set like this :

$sql="SELECT userId FROM eventmember WHERE eventid='$event_id';";
$resultset = mysql_query($sql);
$num_row = mysql_num_rows($resultset);
if($num_row) {
   while($row = mysql_fetch_array($resultset)) {               
       $sql22 = "SELECT userId, firstName FROM userinfo WHERE userId='{$row['userId']}';";
        $resultset22 = mysql_query($sql22);
        $row22 = mysql_fetch_array($resultset22);
        $us_id = $row22['userId'];
        $us_name = $row22['firstName'];

        echo "<tr>";
        echo "<td>ID:</td> <td class='text2' align='center' colspan='2'>
        <b> $us_id </b>
        </u></td>";
        echo "</tr>";
        //You shouldn't use a break here. This will again give you single result only.
   }
}

A Better Solution

Instead of using multiple queries to get the data from userinfo table, use JOIN to get all data with one query. Like this :

$sql="SELECT u.userId,u.firstName FROM eventmember e JOIN userinfo u ON u.userId = e.userId WHERE e.eventid='$event_id';";
$resultset = mysql_query($sql);
$num_row = mysql_num_rows($resultset);
if($num_row) {
   while($row = mysql_fetch_array($resultset)) {               

        $us_id = $row['userId'];
        $us_name = $row['firstName'];

        echo "<tr>";
        echo "<td>ID:</td> <td class='text2' align='center' colspan='2'>
        <b> $us_id </b>
        </u></td>";
        echo "</tr>";
   }
}

The Best and Most Secure Solution

As you should have already known mysql_* functions are removed in PHP 7 and this functions are highly harmful for your security. So, you should either move to PDO or mysqli_* functions. I am giving here an example with mysqli_* functions and additionally I am fetching all rows at once instead of doing fetch for each row, which is better for performance.

//First setup your connection by this way.
$link = mysqli_connect(localhost, "my_user", "my_password", "my_db");
//Now you can use mysqli
$sql="SELECT u.userId,u.firstName FROM eventmember e JOIN userinfo u ON u.userId = e.userId WHERE e.eventid=?;";
$stmt = mysqli_prepare($link, $sql);
$stmt->bind_param('s', $event_id);
$stmt->execute();
$resultset = $stmt->get_result();
$resultArray = $resultset->fetch_all();
$num_row = count($resultArray);
if($num_row) {
   foreach($resultArray as $row) {               

        $us_id = $row['userId'];
        $us_name = $row['firstName'];

        echo "<tr>";
        echo "<td>ID:</td> <td class='text2' align='center' colspan='2'>
        <b> $us_id </b>
        </u></td>";
        echo "</tr>";
   }
}

Answer 2

First of all, use if statement to check whether the returned result set contains any row or not, like this:

if($num_row){
    // your code
}

Second, use a while loop to loop through the result set and display it's contents, like this:

while($row22 = mysql_fetch_array($resultset)){
    // your code
}

And finally, please don't use mysql_ database extensions, they were deprecated in PHP 5.5.0 and were removed in PHP 7.0.0. Use mysqli or PDO extensions instead. And this is why you shouldn't use mysql_ functions.

So your code should be like this:

<?php
    $sql="SELECT userId FROM eventmember WHERE eventid='$event_id'";
    $resultset = mysql_query($sql);
    $row = mysql_fetch_array($resultset); 

    $num_row = mysql_num_rows($resultset);

    if($num_row){                     
        $sql = "SELECT userId, firstName FROM userinfo WHERE userId='" . $row['userId'] . "'";
        $resultset = mysql_query($sql);
        ?>
        <table>
        <tr>
            <td>User ID</td>
            <td>First Name</td>
        </tr>
        <?php
        while($row22 = mysql_fetch_array($resultset)){
            echo "<tr><td>{$row22['userId']}</td><td>{$row22['firstName']}</td></tr>";
        }
        ?>
        </table>
        <?php
    }
?>

For better readability I have displayed the data in table cells.

Answer 3

mysql_fetch_array() will retrieve a single result from a result set. Therefore you'll need to wrap it in a loop to get each row/result.

Here's an example I ripped straight from the PHP documentation:

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

In your case you'd wrap the HTML code in the while loop.

An additional improvement would be to ensure that $resultset is a resource; mysql_query() can return false if your query is invalid (and true in other success cases like INSERTS). You can also use mysql_num_rows() to determine if you have >0 rows to display (and custom display a 'No rows returned' message).

Perhaps more importantly is that mysql_* functions were deprecated in PHP 5.5.0, and additionally removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL.

By continuing to write mysql_ code you'll make upgrading substantially more difficult for yourself. It's also worth noting that 5.5 will also reach security EOL in 6 months, leaving you to rely on your OS vendor to backport security updates from then on.