PHP: MySQL to table

Question

I just wrote simple PHP code that would print some of these rows

That's how does my table structure looks like:

That's my PHP code:

$user = mysql_query("SELECT usr FROM ava_members");
$id = mysql_query("SELECT id FROM ava_members");
$email = mysql_query("SELECT email FROM ava_members");
$dt = mysql_query("SELECT dt FROM ava_members");

//$result = mysql_query("SELECT id,email,dt,regIP FROM ava_members ORDER BY dt LIMIT 5");
$final = "";
$final .= "<table border='1'><tr>";


/* TABELA */
$final .= "<td>Nick</td>";
$final .= "<td>ID</td>";
$final .= "<td>Email</td>";
$final .= "<td>Data</td>";
//$final .= "</tr>\n";
/* TABELA */

//user
while($row = mysql_fetch_row($user))
{
    $final .= "<tr>";

    foreach($row as $cell)
        $final .= "<td>$cell <a href=\"\?usrdel=$cell\"\>[DELETE]</a></td>";
        $final .= "</tr>\n";
}

//ID
while($row = mysql_fetch_row($id))
{
   $final .= "<tr>";

    foreach($row as $cell)
        $final .= "<td>$cell</td>";
        $final .= "</tr>\n";
}

//email

while($row = mysql_fetch_row($email))
{
    $final .= "<tr>";

    foreach($row as $cell)
        $final .= "<td>$cell</td>";
        $final .= "</tr>\n";
}

//dt
while($row = mysql_fetch_row($dt))
{
   $final .= "<tr>";

    foreach($row as $cell)
        $final .= "<td>$cell</td>";
        $final .= "</tr>\n";
}

mysql_free_result($user);
mysql_free_result($id);
mysql_free_result($email);
mysql_free_result($dt);

echo '<center>' . $final . '</center>';

And there's output:

But as you can guess that's not what I want... Output that I want should look like this:

It's pretty simple - just learn in2 html tables

PSA: The mysql_* functions are deprecated in PHP 5.5. It is not recommended for writing new code as it will prevent you from upgrading in the future. Instead, use either MySQLi or PDO and be a better PHP Developer. — Jason McCreary
– Jason McCreary, Commented Nov 5, 2013 at 19:36

Alex · Accepted Answer · 2013-11-05 19:44:37Z

1

You should learn SQL:

$data = mysql_query("SELECT usr, id, email, dt FROM ava_members");
while ($row = mysql_fetch_row($data))
{
    $final .= "<tr>";

    foreach($row AS $k => $cell) {
        $final .= '<td>' . htmlspecialchars($cell);
        if ($k == 'usr') {
            $final .= '<td><a href="?usrdel=' . $row['id'] . '">[DELETE]</a>';
        }
        $final .= '</td>';
    }
    $final .= "</tr>\n";
}

And yes, do not use mysql_. Use mysqli_ instead.

answered Nov 5, 2013 at 19:44

Alex

1,59311 silver badges14 bronze badges

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

Comments

Charles · Accepted Answer · 2013-11-05 19:40:41Z

Here's a function that I wrote to display the data from a MySQL database as an HTML table:

/* Returns all of the results from a query in one nice table */
/* Example usage: echo $db->allResults("taxi0", "time, latitude, longitude",40,50); */
function allResults($table,$cols,$limstart,$lim) {
    if(isset($cols)) {
        $query = "SELECT $cols FROM $table LIMIT $limstart,$lim";
    }
    else {
        $query = "SELECT * FROM $table LIMIT $limstart,$lim";
    }
    $result = $this->query($query);             
    $output = '<table class="allResults">';
    $data = array();
    while($row = $this->fetchAssoc($result)) {
        $data[] = $row;
    }
    $colNames = array_keys(reset($data));
    $output .= "<tr>";
    foreach($colNames as $colName) {
        $output .= "<th>$colName</th>";
    }
    $output .= "</tr>";
    foreach($data as $row) {
        $output .= "<tr>";
        foreach($colNames as $colName) {
            $output .= "<td>".$row[$colName]."</td>";
        }
        $output .= "</tr>";
   }
    $output .= '</table>';
    return $output;
}

Hope it will help you out.

Jess · Accepted Answer · 2013-11-05 21:03:11Z

PDO example, as it is both not deprecated and more fun:

// precondition: $row should be a numbered array of column data
function table_row_from_db_row($row) {
    $result = '';
    foreach($row as $key => $value) {
        $result .= "<td>$value</td>\n";
    }

    return "<tr>\n$result</tr>\n";
}

// precondition: $headers should be an associative array representing the 
//   first row
function table_head_from_first_row($row) {
    $result = '';
    foreach($row as $name => $unused) {
        $result .= "<th>$name</th>\n";
    }

    return "<tr>\n$result</tr>\n";
}

// precondition: $sth is a PDO statement handle from a query that returns 
//   more than 0 rows.
// postcondition: if there were no rows, returns NULL.  Otherwise returns 
//   an HTML table as a string.
function table_from_query_handle($sth) {
    $result = '';

    // Fetch the first row so we can get column header names.
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    if(!$row) {
        return NULL;
    }

    $result .= table_head_from_first_row($row);

    do {
        $result .= table_row_from_db_row($row);
    }
    while($row = $sth->fetch(PDO::FETCH_ASSOC));

    return "<table>\n$result</table>\n";
}

// Alias the column names to the table headers we want to use on the page.
// If we wanted to build the table manually it would still help that we don't
// have two different names for each field (e.g. `dt` and `data`).
$query = "select usr Nick, id ID, email Email, dt Data from ava_members";

// Connect to the database.  This belongs in another file in production code.
$dbh = new PDO('mysql:host=localhost;dbname=test');

// Execute the query and get a statement handle.
$sth = $dbh->query($query);

echo table_from_query_handle($sth);

Collectives™ on Stack Overflow

PHP: MySQL to table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related