CODEWITHSUNDEEP
phphtmlmysql
Maikel Hulst
Maikel Hulst

Reputation: 13

MySQL only showing last data

i'm trying to get the website read a table from my database but its only showing the last row of my table. this table is for test purposes to learn how to read out my database

what i'm trying to do is when a username is created the table will be updated and showed on the website.

searched stackoverflow for the correct answer but nothing works. What am i doing wrong?

here is my code:

  <body>

  <?php
    $host="localhost"; // Host name
    $username=""; // Mysql username
    $password=""; // Mysql password
    $db_name="test"; // Database name
    $tbl_name="members"; // Table name

    $conn = new mysqli($host, $username, $password, $db_name);

    // check connection
    if ($conn->connect_error) {
      trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
    }

    //select rows 
    $sql= 'SELECT id, username, country FROM members';
    $result = mysqli_query($conn, $sql);

   if (mysqli_num_rows($result) > 0) 
   {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) 
    {
        $f1 = $row['id'];
        $f2 = $row['username'];
        $f3 = $row['country'];
    }
   } else {
    echo "0 results";
   }

mysqli_close($conn);
?> 
    <table border="0" cellspacing="2" cellpadding="2">
    <tr>
      <td>
        <font face="Arial, Helvetica, sans-serif">id</font>
      </td>
      <td>
        <font face="Arial, Helvetica, sans-serif">username</font>
      </td>
      <td>
        <font face="Arial, Helvetica, sans-serif">country</font>
      </td>
    </tr>
    <tr>
      <td>
        <font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font>
      </td>
       <td>
        <font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font>
      </td>
       <td>
        <font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font>
      </td>
    <td>
  </body>

Upvotes: 1

Views: 204

Answers (6)

tejashree mahadik
tejashree mahadik

Reputation: 315

<body>
  <?php
    $host="localhost"; // Host name
    $username=""; // Mysql username
    $password=""; // Mysql password
    $db_name="test"; // Database name
    $tbl_name="members"; // Table name
    $conn = new mysqli($host, $username, $password, $db_name);
    // check connection
    if ($conn->connect_error) {
      trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
    }
    //select rows 
    $sql= 'SELECT id, username, country FROM members';
    $result = mysqli_query($conn, $sql);
    ?>
    <table border="0" cellspacing="2" cellpadding="2">
    <tr>
      <td>
        <font face="Arial, Helvetica, sans-serif">id</font>
      </td>
      <td>
        <font face="Arial, Helvetica, sans-serif">username</font>
      </td>
      <td>
        <font face="Arial, Helvetica, sans-serif">country</font>
      </td>
    </tr><?php
   if (mysqli_num_rows($result) > 0) 
   {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) 
    {
        $f1 = $row['id'];
        $f2 = $row['username'];
        $f3 = $row['country'];
    ?>

    <tr>
      <td>
        <font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font>
      </td>
       <td>
        <font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font>
      </td>
       <td>
        <font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font>
      </td>
    </tr>
     <?php    } else {
    ?>
    <tr>
        <td colspan="3">NO Record Found</td>
    </tr>
    <?php
   }
   }
   mysqli_close($conn); ?> 
    </table>
  </body>

Upvotes: 0

Chaya Sandamali
Chaya Sandamali

Reputation: 667

The following while loop will execute when the page loads. You are over-writing the variables $f1, $f2, $f3 in each iteration and finally when the program comes out of the while loop, the values of each of these variable will be the id, username and the country of the last row of your query results array.

// output data of each row
while($row = mysqli_fetch_assoc($result)) 
{
    $f1 = $row['id'];
    $f2 = $row['username'];
    $f3 = $row['country'];
}

What you should do is, adding the table data inside this while loop, like this

<?php
while($row = mysqli_fetch_assoc($result)) 
{
    $f1 = $row['id'];
    $f2 = $row['username'];
    $f3 = $row['country'];
 ?>

  <td>
    <font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font>
  </td>
   <td>
    <font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font>
  </td>
   <td>
    <font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font>
  </td>
<?php } ?>

Upvotes: 1

JimmyB
JimmyB

Reputation: 12610

You need to output one table row per record, e.g. like so:

<table border="0" cellspacing="2" cellpadding="2">

    <tr>
      <td>
        <font face="Arial, Helvetica, sans-serif">id</font>
      </td>
      <td>
        <font face="Arial, Helvetica, sans-serif">username</font>
      </td>
      <td>
        <font face="Arial, Helvetica, sans-serif">country</font>
      </td>
    </tr>

<?php
   // output data of each row
 while($row = mysqli_fetch_assoc($result)) {
    $f1 = $row['id'];
    $f2 = $row['username'];
    $f3 = $row['country'];

    ?> 

    <tr>
      <td>
        <font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font>
      </td>
       <td>
        <font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font>
      </td>
       <td>
        <font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font>
      </td>
    </tr>

<?php

}
 mysqli_close($conn);

?>
    </table>

Upvotes: 0

zanderwar
zanderwar

Reputation: 3730

You keep resetting the values of $f1,$f2 and $f3 in the while loop

Try this:

<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="members"; // Table name

$conn = new mysqli($host, $username, $password, $db_name);

// check connection
if ($conn->connect_error) {
    trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
}

//select rows 
$sql= 'SELECT id, username, country FROM members';
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    $Results = array();
    while($row = mysqli_fetch_assoc($result)) {
        $Results = array('f1' => $row['id'], 'f2' => $row['username'], 'f3' => $row['country']);
    }

    var_dump($Results);
} 
else {
echo "0 results";
}

mysqli_close($conn);

Upvotes: 0

David
David

Reputation: 218827

You only output the last row. You're reading every row here:

while($row = mysqli_fetch_assoc($result)) 
{
    $f1 = $row['id'];
    $f2 = $row['username'];
    $f3 = $row['country'];
}

But you're reading them into the same variables. So every time that loop iterates, you overwrite the previous values in those variables. As a result, those variables will contain only the last row's data.

Put the output step into the loop as well. Structurally, it would look something like this:

/* output HTML table header here */
while($row = mysqli_fetch_assoc($result)) 
{
    $f1 = $row['id'];
    $f2 = $row['username'];
    $f3 = $row['country'];
    /* output HTML table row here */
}
/* output HTML table footer here */

Upvotes: 2

Hassaan
Hassaan

Reputation: 7662

All of you <TD> and Variables like $f1, $f2 and $f3 must be in the while loop.

Try example

<body>

<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="members"; // Table name

$conn = new mysqli($host, $username, $password, $db_name);

// check connection
if ($conn->connect_error)
{
    trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
}

//select rows 
$sql= 'SELECT id, username, country FROM members';
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) 
{
?>

<table border="0" cellspacing="2" cellpadding="2">
    <tr>
        <td>
            <font face="Arial, Helvetica, sans-serif">id</font>
        </td>
        <td>
            <font face="Arial, Helvetica, sans-serif">username</font>
        </td>
        <td>
            <font face="Arial, Helvetica, sans-serif">country</font>
        </td>
    </tr>

<?php
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) 
    {
    $f1 = $row['id'];
    $f2 = $row['username'];
    $f3 = $row['country'];
    ?>
    <tr>
        <td>
            <font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font>
        </td>
        <td>
            <font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font>
        </td>
        <td>
            <font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font>
        </td>
    </tr>
    <?php
    }
echo "</table>";
}
else
{
    echo "0 results";
}

mysqli_close($conn);
?> 

</body>

Upvotes: 0

Related Questions