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.

Put it into a while loop: `while($row = mysql_fetch_array($resultset)){/*code*/}` — Rizier123, Dec 27 '15 at 12:21
[**Please, don't use `mysql_*` functions in new code**](http://stackoverflow.com/q/12859942). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [*prepared statements*](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). — Rizier123, Dec 27 '15 at 12:21
a foreach loop should also work `foreach ($row as $value){ }` — danidee, Dec 27 '15 at 12:26

score 3 · Answer 1 · edited May 23 '17 at 10:33

3

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.

edited May 23 '17 at 10:33

Community

1
1

answered Dec 27 '15 at 12:34

Rajdeep Paul

16,887
3
18
37

OP is trying to get multiple rows from the `eventmember` table and then based on those rows get the user info for each row. I don't think your code is doing so! – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Dec 27 '15 at 12:47
@TareqMahmood OP has specified this line, **i got specific userid from specific event column...** in the question. – Rajdeep Paul Dec 27 '15 at 12:50
There should be multiple userid for each event – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Dec 27 '15 at 12:50
@RajdeepPaul there is a little error in your code at the end of the second line. – Phiter Dec 27 '15 at 13:08
@PhiterFernandes Good catch. Fixed it. – Rajdeep Paul Dec 27 '15 at 13:10
thanks @Rajdeep Paul for answer but i got some error on that $row['userId'] line please give me some another solution.. – Hamza Rahman Dec 27 '15 at 13:42
@HamzaRahman There was a syntax error on that line. I've updated my answer. Now it should work fine. – Rajdeep Paul Dec 29 '15 at 15:54

Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ · Accepted Answer · 2015-12-27T14:10:27.047

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>";
   }
}

thanks for answer @Tareq Mahmood but i don't get it that where dose the '$row[$i]' $i variable you have use.. please give me a solution.. — Hamza Rahman, Dec 27 '15 at 13:40
@HamzaRahman, Sorry, I missed to update your code there. You can use either `{$row['userId']}` or `$row[0]` there, I prefer the first one since it will remove the confusion of what and how you are getting. I updated my answer. — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ, Dec 27 '15 at 13:44
`{...}` is used here so that the single quotes inside that part doesn't make any problem with our outer single quotes. — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ, Dec 27 '15 at 13:46

developerjack · Answer 3 · 2015-12-27T12:41:29.533

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.

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

3 Answers3

Simple Solution

A Better Solution

The Best and Most Secure Solution