3 
    $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.

Improve this question
3

3 Answers 3

Reset to default
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.

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

7 Comments

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!
@TareqMahmood OP has specified this line, i got specific userid from specific event column... in the question.
There should be multiple userid for each event
@RajdeepPaul there is a little error in your code at the end of the second line.
@PhiterFernandes Good catch. Fixed it.
|
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>";
   }
}
Improve this answer

3 Comments

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..
@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.
{...} is used here so that the single quotes inside that part doesn't make any problem with our outer single quotes.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.