Page 1 of 1

Simple SQL query help

Posted: Thu Jul 01, 2004 3:01 pm
by aace
This is what my table (TimeTable) looks like...

name time
allen 400
horn 300

What I am trying to do is to pull allen and 300 out of the db and horn and 300. Before I display them so the screen I need to do some math with the time. How do I pull out individuals rows then work with one of the attributes? The display will look like...

Allen 3hrs 2 minutes (arbitrary numbers here)
Horn 2hrs 12 minutes (arbitrary numbers here)


Thanks in advance.

Posted: Thu Jul 01, 2004 3:21 pm
by feyd

Code: Select all

SELECT name, time FROM tablename
:roll:

Posted: Thu Jul 01, 2004 3:23 pm
by Deemo
you could use a while loop to cycle through all the rows. something like this:

Code: Select all

<?php
$query = "SELECT * FROM Table";
$result = mysql_query($query) or die("Couldn't Execute Query ".mysql_error());

while ($row = mysql_fetch_array($result))
{
  //Do whatever calculations you want. Here $row is an array
  //where all of the indexes are column names
  //for example: $row['name'] would output allen the first loop, then horn the second time
}
?>
All this does is it goes through the table one row at a time and u can do whatever you want in the while loop

Posted: Thu Jul 01, 2004 3:48 pm
by scorphus
If you are using MySQL you could use one of these queries:

query 1) In case 'time' is in seconds:

Code: Select all

select name, sec_to_time(time) as time_fmt from tablename;
which returns:

Code: Select all

+-------+----------+
| name  | time_fmt |
+-------+----------+
| allen | 00:05:00 |
| horn  | 00:06:40 |
+-------+----------+
3 rows in set (0.09 sec)
query 2) In case 'time' is in minutes:

Code: Select all

select name, sec_to_time(time*60) as time_fmt from tablename;
which returns:

Code: Select all

+-------+----------+
| name  | time_fmt |
+-------+----------+
| allen | 05:00:00 |
| horn  | 06:40:00 |
| horn  | 06:40:00 |
+-------+----------+
3 rows in set (0.00 sec)
Regards,
Scorphus.