homepage Welcome to WebmasterWorld Guest from 54.196.181.109
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
A simple question for the experts ;)
Matthew1980




msg:4267292
 12:18 pm on Feb 15, 2011 (gmt 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

 

brotherhood of LAN




msg:4267294
 12:22 pm on Feb 15, 2011 (gmt 0)

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.

topr8




msg:4267295
 12:22 pm on Feb 15, 2011 (gmt 0)

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.

Matthew1980




msg:4267321
 1:45 pm on Feb 15, 2011 (gmt 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

Matthew1980




msg:4267323
 1:47 pm on Feb 15, 2011 (gmt 0)

Thanks, that does the job nicely:-

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

Thanks,
MRb

rocknbil




msg:4267454
 5:27 pm on Feb 15, 2011 (gmt 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];

Matthew1980




msg:4267498
 6:50 pm on Feb 15, 2011 (gmt 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved