Display returned Mysql data as actual days and hours as opposed to bare numbers

[Jonny125]

Hi there,

 

I'm a beginner with PHP and MySQL so please bare with me if this is easier or harder than I imagine. 

 

I currently have a HTML page with a form that includes the select feature, so that the user can enter what hour of the day, and day of the week they would like to query. The submit button takes the user to the PHP page where the mysql select statement is processed and displays the data from the db. This all works fine. However, the days are stored as tinyint field type in the database and are entered as 1 to 7, rather than days of the week. 

 

What this means is that when the user sees the retrieved data on the php page, the hour and day is just a number rather than the actual hour and/or day. 

 

Is there a way, in PHP, to do what the HTML select feature is doing on the HTML page (shows 01:00 to 24:00 for hours whereas in the db its 1 to 24, and Monday to Sunday for days whereas in the db it is 1 to 7) But do it on the PHP page with the returned sql data? Obviously it wouldn't actually need to let the user select, an option, but show the user the useful information (hours & days) as opposed to raw data (1-7 & 1-24).

 

Thank you for reading, any help is much appreciated. 

Edited March 22, 2013 by Jonny125

[Barand]

$h = 17;
echo date('H:i', mktime($h,0,0));  //--> 17:00

use an array for days

[Jonny125]

thank you.

[Jonny125]

Would you mind elaborating on on your answer little? I'm not entirely sure where to place or how to use this. My Code: 

<?php
$connection = mysql_connect("localhost", "username", "password"); //connect to server with these creds, store in $connection variable
if (!$connection)
{die('Could not connect: ' . mysql_error());} //if $connection can not connect give error
mysql_select_db("dbname", $connection); //select database name for $connection

//-------------sql select query for daily stats
$sql ="SELECT storeid, HOUR, SUM( qty ) AS 'Total Quantity', SUM( value ) AS 'Total Value', AVG( qty ) AS 'Average Quantity', AVG( value ) AS 'Average Value', SUM( value ) / SUM( qty ) AS 'Average Value Per Item'
FROM depthour
GROUP BY HOUR"; 
//echo "SQL Query used: "; echo $sql;

$query = mysql_query($sql); //give resource the variables

echo "<table border='1' cellpadding='2' cellspacing='3' width='100%'>"; //
echo "<tr><th>Hour</th><th>Total Quantity</th><th>Total Value</th><th>Average Quantity</th><th>Average Value</th><th>Average Value per Item</th></tr>";
while ($row = mysql_fetch_array($query)) {  //display results for hour defined by SQL
if (!$query) { // add this check.
    die('Invalid query: ' . mysql_error());
} //-------------------End of SQL for daily stats

echo "<tr><td>" .$row['HOUR'];
echo "</td><td>" .$row['Total Quantity'];
echo "</td><td>" .$row['Total Value'];
echo "</td><td>" .$row['Average Quantity'];
echo "</td><td>" .$row['Average Value'];
echo "</td><td>" .$row['Average Value Per Item'];
echo "</td></tr>";
}
echo "</table>";
?>

 

 

 

 

echo "<tr><td>" .$row['HOUR']; is where the hour numbers 1 to 24 are displayed, and would like them to display as 01:00 to 24:00. 

 

Apologies for the novice understanding. 

Edited April 10, 2013 by Jonny125

[Barand]

change

echo "<tr><td>" .$row['HOUR'];

to

echo "<tr><td>" . date('H:i', mktime($row['HOUR'],0,0));

[Jonny125]

Thanks for the help, much appreciated.