Fetch and display multiple rows and columns from mysql using php

Question

This is my database table:

I want to display this table (5 columns) on my page when clicking a button (List Users).

But I'm getting the following as output:

My code is:

<?php
$db = "*";  //masked for security
$host = "*"; //masked for security
$user = "*";//masked for security
$pwd = "*; //masked for security
$con = mysqli_connect($host,$user,$pwd,$db);
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

$sql="SELECT login_id,user_name,password,user_role,status_id FROM login";
$select = mysqli_query($con,$sql);
$num_rows = mysqli_num_rows($select);
echo "Number of rows : ";
echo $num_rows;
$row = mysqli_fetch_array($select, MYSQLI_ASSOC);
echo "<table>
<tr>
<th>Login ID</th>
<th>User name</th>
<th>Password</th>
<th>User Role</th>
<th>Status ID</th>
</tr>";

foreach ($row as $rows)
{
    echo "<tr>";
    echo "<td>" . $rows['login_id'] . "</td>";
    echo "<td>" . $rows['user_name'] . "</td>";
    echo "<td>" . $rows['password'] . "</td>";
    echo "<td>" . $rows['user_role'] . "</td>";
    echo "<td>" . $rows['status_id'] . "</td>";
    echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>

Please help me find the error in this code.

once print_r($row) this and check what exactly you are getting — Rahul
– Rahul, Commented Aug 21, 2017 at 12:24
Its hurting my brain to see foreach ($row as $rows) instead of foreach($rows as $row). But seriously, do what @RahulMeshram says so we can see what we are working with. — Lewis Browne
– Lewis Browne, Commented Aug 21, 2017 at 12:25
what's the point of foreach loop there if you getting only one row with mysqli_fetch_array ? You should use while($row= mysqli_fetch_array($select, MYSQLI_ASSOC) { ... } — ArtOsi
– ArtOsi, Commented Aug 21, 2017 at 12:32
see your mistake now? You looping same row as many times as it has columns — ArtOsi
– ArtOsi, Commented Aug 21, 2017 at 12:37

ArtOsi · Accepted Answer · 2017-08-21 12:39:51Z

1

you getting only one row with mysqli_fetch_array($select, MYSQLI_ASSOC); you should loop through results

while($row= mysqli_fetch_array($select, MYSQLI_ASSOC) { your code here ... }

now if foreach loop you going through same row as many times as it has columns.

answered Aug 21, 2017 at 12:39

ArtOsi

9436 silver badges13 bronze badges

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

Comments

Shafiqul Islam · Accepted Answer · 2017-08-21 12:59:02Z

you have mistake some condition so use this code which will work, only while ($rows = mysqli_fetch_array($select, MYSQLI_ASSOC)) will work for listing data so no need double loop

<?php
$db = "*";  //masked for security
$host = "*"; //masked for security
$user = "*";//masked for security
$pwd = "*"; //masked for security
$con = mysqli_connect($host, $user, $pwd, $db);
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

$sql = "SELECT login_id,user_name,password,user_role,status_id FROM login";
$select = mysqli_query($con, $sql);
$num_rows = mysqli_num_rows($select);
echo "Number of rows : ";
echo $num_rows;

echo "<table>
<tr>
<th>Login ID</th>
<th>User name</th>
<th>Password</th>
<th>User Role</th>
<th>Status ID</th>
</tr>";
if ($num_rows > 0) {

    while ($rows = mysqli_fetch_array($select, MYSQLI_ASSOC)) {
        echo "<tr>";
        echo "<td>" . $rows['login_id'] . "</td>";
        echo "<td>" . $rows['user_name'] . "</td>";
        echo "<td>" . $rows['password'] . "</td>";
        echo "<td>" . $rows['user_role'] . "</td>";
        echo "<td>" . $rows['status_id'] . "</td>";
        echo "</tr>";
    }
}
echo "</table>";
mysqli_close($con);
?>

for more information

https://www.w3schools.com/php/php_mysql_select.asp

Collectives™ on Stack Overflow

Fetch and display multiple rows and columns from mysql using php

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