Welcome to WebmasterWorld Guest from 54.159.50.111

Forum Moderators: open

Message Too Old, No Replies

A simple question for the experts ;)

     
12:18 pm on Feb 15, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


I am no mysql expert, but I know enough to get by, what I wish to know is this:-

Example

schema

id|name|date|updateed|status|position

SELECT * FROM `Atable`;

This selects the WHOLE contents of all the columns, all good, but now if I wanted to make use of the timestamped 'date' column I would use this:-

SELECT DATE_FORMAT(`Date`, '%a %D %b %Y %k:%i') AS `getDate` FROM `Atable`

But doing this means that I am only requesting the 'date' column.

I would like to know what the correct approach would be to asking for the date using date_format() as well as ALL the other fields without having to name them individually.

Cheers,
MRb
12:22 pm on Feb 15, 2011 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4842
votes: 1


SELECT DATE_FORMAT(`Date`, '%a %D %b %Y %k:%i') AS `getDate`,Atable.* FROM `Atable`

This would work, though it also gets the (unformatted) Date field again.
12:22 pm on Feb 15, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 19, 2002
posts:3171
votes: 8


it's inefficient to use the *

however you could do:
SELECT *,DATE_FORMAT(`Date`, '%a %D %b %Y %k:%i') AS `getDate` FROM `Atable`
which would select all and also select the formatted column, giving you effectively duplicate data, which would also be slightly inefficient however it would save you writing the column names if you wanted.
1:45 pm on Feb 15, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


>>giving you effectively duplicate data

This is precisely what my concern was, but I'm not too fussed about it's efficiency at the moment.

Thanks for the pointers anyway.

Cheers,
MRb
1:47 pm on Feb 15, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


Thanks, that does the job nicely:-

SELECT *,DATE_FORMAT(`Date`, '%a %D %b %Y %k:%i') AS `getDate` FROM `Atable`

Thanks,
MRb
5:27 pm on Feb 15, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


If you're using mysql_fetch_array, it returns both associative and indexed array, so there's really no need for "as" unless it's more digestible for you. (but you probably garnered that from posts in PHP . . .)

select id,status,date_format(`Date`, '%a %D %b %Y %k:%i') from table;

...

$id = $row[0];
$status = $row[1];
$dt = $row[2];
6:50 pm on Feb 15, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


Hi there Rocknbil,

>>If you're using mysql_fetch_array, it returns both associative and indexed array

Yup, I knew that, if this was using php I wouldn't have bothered, but as it's using the ADODB [msdn.microsoft.com] recordset object, I don't know if that's the same difference or not - though, it's functional :)

Cheers,
MRb