Formatting MySQL data output

Question

I am converting MySQL table into HTML table using this PHP snippet, and it looks something like this [html]:

/----------------------------------------\
| Name | Subscribed           | Duration | <- Column names
|----------------------------------------|
| Bob  | 2011-08-20 04:07:01  | 60       |
|----------------------------------------| 
| Ben  | 2011-08-20 04:07:01  | 260      |
\----------------------------------------/

How can I output the table without column names? (Name, Subscribed, Duration) So that it starts from Bob.
Is it possible to convert that date into something like this: August 20, 2011 4:07:01?
Duration is in seconds, how can I convert it into minutes (Duration (s)/60) in PHP, so that it will show 1 instead of 60 seconds?

What have you tried? It should be simple if you are familiar with PHP and MySQL. For #1, try reading the snippet COMMENTS. — Evan Mulawski
– Evan Mulawski, Commented Aug 20, 2011 at 14:35

Shef · Accepted Answer · 2011-08-20 14:53:18Z

2

Here is the code, tailored to your requirements:

// sending query
$result = mysql_query("SELECT * FROM {$table}");
if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='1'>";
// printing table rows
while($row = mysql_fetch_assoc($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $key => $cell){
        if($key == 'Subscribed'){
            $cell = date('F j, Y H:i:s', strtotime($cell)); // format date August 20, 2011 4:07:01
        } elseif($key == 'Duration'){
            $cell = $cell/60; // format time in minutes
        }
        echo "<td>$cell</td>";
    }


    echo "</tr>\n";
}
echo '</table>';

answered Aug 20, 2011 at 14:53

Shef

45.6k15 gold badges82 silver badges91 bronze badges

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

1 Comment

Helen Neely Over a year ago

@Shef is one awesome guy. I have got lots of help from him as well. He's a one in a million :)

Michiel · Accepted Answer · 2011-08-20 14:39:52Z

0

About the date, try something like this:

echo strftime("%F %d, %Y %g:%i:%S", strtotime($v["datum"]));

answered Aug 20, 2011 at 14:39

Michiel

8,12117 gold badges67 silver badges114 bronze badges

Comments

Fad · Accepted Answer · 2011-08-20 14:49:20Z

You can use this code to remove the column names. This is the same snippets from the link you posted but with some lines removed. This will also print the duration in minutes.

<html><head><title>MySQL Table Viewer</title></head><body>
<?php
$db_host = 'localhost';
$db_user = 'root';
$db_pwd = 'lptm42b';

$database = 'sphinx';
$table = 'spheres';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("SELECT * FROM {$table}");
if (!$result) {
    die("Query to show fields from table failed");
}


echo "<h1>Table: {$table}</h1>";
echo "<table border='1'>";
// printing table rows
while($row = mysql_fetch_assoc($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $field => $value)
    {
        if ($field === 'Duration')
        {
            $value = $value / 60;
        }
        echo "<td>$value</td>";
    }
    echo "</tr>\n";
}
mysql_free_result($result);
?>
</body></html>

ComFreek · Accepted Answer · 2013-04-24 21:01:40Z

0

1.) Delete the for loop after // printing table headers.

2.) Use date() and strtotime() for the column "Subscribed" (second one if you use mysql_fetch_row())

3.) Divide the third column by 60.

But I would suggest using mysqli_fetch_array() instead of mysqli_fetch_row().

edited Apr 24, 2013 at 21:01

answered Aug 20, 2011 at 14:39

ComFreek

29.5k18 gold badges107 silver badges157 bronze badges

Collectives™ on Stack Overflow

Formatting MySQL data output

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related