Marky B Marky B - 4 years ago 218
SQL Question

PHP there's only 1 row is returned then duplicates it

I was wondering if anyone can explain to me why its only returning 1 row when there are 5 different rows in mysql table.

It's only showing the 1st row in all 5 rows, because I placed it in a while loop (HTMLcode) so that it can print all the other rows not just the first one.

Image that shows the problem




THANK YOU IN ADVANCE :)




PHP CODE

$id = session_id();
if ($id == "") {
session_start();
}
if (!isset($_SESSION['username'])) {
header("Location: login.php");
}

// making the connection to the database
try {
$host = '127.0.0.1';
$dbname = 'webdev_2014376';
$user = 'root';
$pass = '';
# MySQL with PDO_MYSQL
$DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
} catch(PDOException $e) {echo 'Error';}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////

// selecting the row from the database
$sqlQuery = $DBH->prepare("SELECT * FROM users");

// running the SQL
$sqlQuery->execute();
// pulling the data into a variable
$row = $sqlQuery->fetch(PDO::FETCH_ASSOC);

// taking each individual piece
$UserID = $row['UserID'];
$username = $row['Username'];
$firstname = $row['FirstName'];
$lastname = $row['LastName'];

?>






HTML CODE

<?php
echo 'hello, ' . $_SESSION['username'];
echo ' ';
echo $_SESSION['id'];
?>

<div>
<table class="table table-bordered table-hover">
<thead>
<tr>
<th class="TableCol1"> Username </th>
<th class="TableCol2"> First Name </th>
<th class="TableCol3"> Last Name </th>
<th class="TableColOpt"> Options </th>
</tr>
</thead>
<tbody>
<?php
while ($check) {
echo '<tr>';
echo '<td class="prEach1">' . $username . '</td> ';
echo '<td class="prEach1">' . $firstname . '</td> ';
echo '<td class="prEach3">' . $lastname . '</td> ';
echo '<td class="prEach4 optlinks"> '
. '<a href="profile.php?UserID='.$UserID.'">View</a> '
. '</td>';
echo '</tr>';

$check = $sqlQuery->fetch(PDO::FETCH_ASSOC);
}
?>
</tbody>
</table>
</div>






-------------------------------------------------------

EDIT




I FOUND THE SOLUTION


SOLUTION IMAGE



PHP CODE

<?php
$id = session_id();
if ($id == "") {
session_start();
}
if (!isset($_SESSION['username'])) {
header("Location: login.php");
}

// making the connection to the database
try {
$host = '127.0.0.1';
$dbname = 'webdev_2014376';
$user = 'root';
$pass = '';
# MySQL with PDO_MYSQL
$DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
} catch(PDOException $e) {echo 'Error';}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////

// selecting the row from the database
$sqlQuery = $DBH->prepare("SELECT * FROM users");

// running the SQL
$sqlQuery->execute();
// pulling the data into a variable
$check = $sqlQuery->fetch(PDO::FETCH_ASSOC);



?>




HTML CODE

<?php
echo 'hello, ' . $_SESSION['username'];
echo ' ';
echo $_SESSION['id'];
?>

<div>
<table class="table table-bordered table-hover">
<thead>
<tr>
<th class="TableCol1"> Username </th>
<th class="TableCol2"> First Name </th>
<th class="TableCol3"> Last Name </th>
<th class="TableColOpt"> Options </th>
</tr>
</thead>
<!-- This is the category fields on the list. -->
<tbody>
<?php
//$check = $sqlQuery->fetch(PDO::FETCH_ASSOC);
while ($check) {

echo '<tr>';
echo '<td class="prEach1">' . $check['Username'] . '</td> ';
echo '<td class="prEach1">' . $check['FirstName'] . '</td> ';
echo '<td class="prEach3">' . $check['LastName'] . '</td> ';
echo '<td class="prEach4 optlinks"> '
. '<a href="profile.php?UserID='.$check['UserID'].'">View</a> '
. '</td>';
echo '</tr>';

$check = $sqlQuery->fetch(PDO::FETCH_ASSOC); //THIS SHOULD BE AT THE BOTTOM JUST BEFORE THE WHILE LOOP ENDS
}
?>
</tbody>
<!-- This is the get methods of the properties, where the output of the user put in the Property form will be shown -->
</table>
</div>

Answer Source

I was wondering if anyone can explain to me why its only returning 1 row when there are 5 different rows in mysql table.

Look what's happening inside while loop,

while ($check)  {
    echo '<tr>';
    echo '<td class="prEach1">' . $username . '</td> ';
    echo '<td class="prEach1">' . $firstname . '</td> ';
    echo '<td class="prEach3">' . $lastname . '</td> ';
    echo '<td class="prEach4 optlinks"> '
    . '<a href="profile.php?UserID='.$UserID.'">View</a> '
    . '</td>';
    echo '</tr>';

    $check = $sqlQuery->fetch(PDO::FETCH_ASSOC);
}

Initially you fetched only one row from the result set using $row = $sqlQuery->fetch(PDO::FETCH_ASSOC); and put the values in the respective variables. In the while loop you're looping through the result set but actually echoing same old variables.

Solution:

If you want to display all the result set rows(including the first row) inside your table cells, then first delete these four lines,

$row = $sqlQuery->fetch(PDO::FETCH_ASSOC);

$UserID = $row['UserID'];
$username = $row['Username'];
$firstname = $row['FirstName'];
$lastname = $row['LastName'];

And then loop through the result set like this,

// your code

<?php
    while ($row = $sqlQuery->fetch(PDO::FETCH_ASSOC)){
        echo '<tr>';
        echo '<td class="prEach1">' . $row['Username'] . '</td> ';
        echo '<td class="prEach1">' . $row['FirstName'] . '</td> ';
        echo '<td class="prEach3">' . $row['LastName'] . '</td> ';
        echo '<td class="prEach4 optlinks"> '
        . '<a href="profile.php?UserID='.$row['UserID'].'">View</a> '
        . '</td>';
        echo '</tr>';

    }
?>

// your code
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download