Retrieving the date...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

Retrieving the date...

Post by Steveo31 »

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?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

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 »

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?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

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 »

Awesome! Thanks Mac.
Post Reply