Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Steveo31
Forum Contributor
Posts: 416 Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA
Post
by Steveo31 » Fri Apr 09, 2004 3:33 pm
In PhpMyAdmin, I can get the value I need with this:
Code: Select all
SELECT DATE_FORMAT( `input_date` , '%c-%e-%y' ) FROM `input` WHERE name = 'Admin'
input_date is the name of the row, and 'input' is the table name. Before my comp crashed, I had this working. But now, I can't figure out how to output this in PHP.
Any ideas?
twigletmac
Her Royal Site Adminness
Posts: 5371 Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK
Post
by twigletmac » Fri Apr 09, 2004 4:09 pm
By using an alias for the result of the DATE_FORMAT() you can make it easier to get the info out via PHP, so you could have:
Code: Select all
SELECT DATE_FORMAT(`input_date`, '%c-%e-%y') AS display_date FROM `input` WHERE name = 'Admin'
Then once you've extracted the results of the query into a variable (e.g. $row) you can just access the variable as $row['display_date'].
Mac
Steveo31
Forum Contributor
Posts: 416 Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA
Post
by Steveo31 » Fri Apr 09, 2004 4:52 pm
Ok, thanks. Makes sense
But here's my next prob. I already have $row assigned for a getAll statement, like this:
Code: Select all
<?php
//
$getAll = mysql_query("SELECT * FROM input");
$getDate =mysql_query("SELECT DATE_FORMAT( `input_date` , '%c-%e-%y' ) AS rev_date FROM `input` WHERE name = '".$row['username']."' LIMIT 0, 30");
while($row = mysql_fetch_array($getAll)) {
//tables....
}
?>
...so I'm unsure of how to set this one up. Another while loop causes everything to disappear.
There are two statements... is there a way I can combine the two?
twigletmac
Her Royal Site Adminness
Posts: 5371 Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK
Post
by twigletmac » Fri Apr 09, 2004 5:09 pm
The best thing you can do is start specifying all the names of the fields you want to return, it will make debugging easier in the long run and saves database resources because you won't return columns you don't need. Then you end up with a single SQL statement that looks something like:
Code: Select all
SELECT field1, field2, field3, field4, DATE_FORMAT(input_date, '%c-%e-%y') AS rev_date FROM input WHERE ...
Mac
Steveo31
Forum Contributor
Posts: 416 Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA
Post
by Steveo31 » Fri Apr 09, 2004 5:58 pm
Awesome! Thanks Mac.