Re-factoring this mysql table display code

Question

I'm not wholly proficient with PHP but I'm having a few problems echoing a gathered value from a MySQL query function.

I believe I know where the problem lies but I'm not competent enough to fix it, if you could please help it would be appreciated.

PHP Function (Works perfectly).

<?php
$dbhost = 'localhost';
$dbuser = 'user';
$dbpass = 'password';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT Name, Role, Salary FROM `users-table`';

mysql_select_db('user_records');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
    die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "--------------------------------<br>",
         "Name: {$row['Name']}  <br> ".
         "Role: {$row['Role']} <br> ".
         "Salary : {$row['Salary']} <br> ".
         "--------------------------------<br>";
}
mysql_close($conn);
?>

This displays the data perfectly! However I'm now trying to include this in a nicely formatted HTML table. (Which is why I'm closing the php tag above).

I'm then trying to use a table like this:

<table >
        <tr>
            <td>
                <?php echo $row['Name'] ?>
            </td>
....

It outputs nothing, - I think this problem is caused because I close the first function and then try to reference $row and it doesn't know what to do...?

I think I need to tap in to while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) How can I re-factor this so that I can echo content from the above function to my table?

[Please, stop using mysql_* functions](http://stackoverflow.com/q/12859942/1238019) in new code, they are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Instead of, have a look on [prepared statements](http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html), and use [Mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php). — zessx, Jul 16 '14 at 15:52
@zessx I will move away, I'm just tinkering and learning as I go. I will look into MySQLi, this is just a problem that's annoying me. — Samuel Nicholson, Jul 16 '14 at 15:55

score 0 · Answer 1 · answered Jul 16 '14 at 15:57

0

You didn't write the code you tried, but here is something functional:

$link = mysqli_connect("myhost", "myuser", "mypassw", "mybd");
echo "<table>";
while ($row = mysqli_fetch_array($link, $retval, MYSQL_ASSOC)) {
    echo "
        <tr>
            <td>{$row['Name']}</td>
            <td>{$row['Role']}</td>
            <td>{$row['Salary']}</td>
        </tr>
    ";
}
echo "</table>";

PS: Since MySQL is deprecated, I replaced your code with MySQLi, and I suggest you do the same :)

answered Jul 16 '14 at 15:57

Vlad Preda

9,780
7
36
63

What if I wanted to include a table style sheet? – Samuel Nicholson Jul 16 '14 at 16:01
You mean a stylesheet ? Nothing is stopping you :) – Vlad Preda Jul 16 '14 at 16:05
Sorry, just trying to understand, this interweave of php and html confuses me ;) - I can't see where you're declaring the MySQL table in the above snippet. You're declaring the DB in the first line but what about the actual table? – Samuel Nicholson Jul 16 '14 at 16:10

Filo · Answer 2 · 2014-07-16T16:05:27.780

As @zessx said, yous should use MySQLi or PDO.

anyway to answer to your question, you have to change the loop in your php code from

while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "--------------------------------<br>",
         "Name: {$row['Name']}  <br> ".
         "Role: {$row['Role']} <br> ".
         "Salary : {$row['Salary']} <br> ".
         "--------------------------------<br>";
}

to

echo '<table>';
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "  <tr>" . 
         "    <td>Name: {$row['Name']}</td>".
         "    <td>Role: {$row['Role']}</td>".
         "    <td>Salary : {$row['Salary']}</td>".
         "  </tr>";
}
echo '</table>';

Re-factoring this mysql table display code

2 Answers2