CODEWITHSUNDEEP
phpmysqlprintinghtml-table
user2811921
user2811921

Reputation: 3

PHP: MySQL to table

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

Upvotes: 0

Views: 145

Answers (3)

Jessica
Jessica

Reputation: 6957

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);

Upvotes: 0

Alex
Alex

Reputation: 1593

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.

Upvotes: 1

Charles
Charles

Reputation: 4538

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.

Upvotes: 0

Related Questions