Paul Posted June 16, 2012 Share Posted June 16, 2012 Hi all, I have the following code: $q = "SELECT * FROM saved_curtains WHERE quote_no = '$quote_no' DATE_FORMAT(saved_date, '%D %M %Y') "; It does not come up with an error but it doesn't format the saved_date column either. Now I've had a rummage around Google etc. to try and find out what I'm doing wrong and I'm getting the impression that if I want to format a single column whilst wanting all I need to list out ever single column and format the date one instead of using '*'. Larry's book only gives examples of selecting specific columns and formatting them, not all columns and formatting one of them. Is that correct? I can't believe it is. There must be another way. Cheers Paul Link to comment Share on other sites More sharing options...
HartleySan Posted June 16, 2012 Share Posted June 16, 2012 Try giving the formatted date an alias (i.e., use an AS clause) to make it accessible. Link to comment Share on other sites More sharing options...
Paul Posted June 16, 2012 Author Share Posted June 16, 2012 HartleySan, I wasn't sure what you meant. I thought an alias was for particular columns or groups of columns that had been selected. But I tried: $q = "SELECT * AS sc FROM saved_curtains WHERE quote_no = '$quote_no' DATE_FORMAT(saved_date, '%D %M %Y') "; It didn't work but thanks for the suggestion. If I don't hear anything I'll try formatting the output in the table itself but I'll be surprised if it can't be done. Cheers Paul Link to comment Share on other sites More sharing options...
margaux Posted June 16, 2012 Share Posted June 16, 2012 this should work $q = "SELECT *, DATE_FORMAT(saved_date, '%D %M %Y') FROM saved_curtains WHERE quote_no = '$quote_no'"; 2 Link to comment Share on other sites More sharing options...
HartleySan Posted June 17, 2012 Share Posted June 17, 2012 Ah, yes, margaux is right, I think. I looked at this post last night on my mobile device (while half drunk), but yeah, the DATE_FORMAT part needs to be in the SELECT part of the statement. Good call. I'm still thinking that you will need to use an alias though to access the formatted date in the returned DB results. Honestly, I'm not too sure about that, but I always use an alias, so not sure if the same can be done without one. I suppose I could test it out, but... kinda busy now. Sorry for not being of more assistance. 1 Link to comment Share on other sites More sharing options...
Antonio Conte Posted June 17, 2012 Share Posted June 17, 2012 I'm still thinking that you will need to use an alias though to access the formatted date in the returned DB results. Yeah, you have to. For the sake of argument, the query won't fail, but accessing it will be brutal. (If even possible) Just imagine the code! echo $row['DATE_FORMAT(saved_date, '%D %M %Y')']; So... You kind of have to. Link to comment Share on other sites More sharing options...
Paul Posted June 18, 2012 Author Share Posted June 18, 2012 Whoa guys, now I'm confused. HartleySan, thanks for your suggestion I appreciate being pointed in the right direction, don't feel you need to spend time testing stuff. I hope you had a good night! Margaux, I tried your suggestion and it didn't work, the date still came out as the complete date and time. But Antonio's post has got me. If I use DATE_FORMAT in the sql command (and it worked) then why do I need an alias or to do the same formatting in $row. Once it's formatted in the SQL query doesn't it just output that way? I also thought alias's were just a way to save typing a long name. Larry quotes 'An alias is merely a symbolic renaming of a thing in a query.' I'm not understanding the connection between this query and an alias. I also tried looking up whether I could format the date in PHP (as part of $row) . I used: <td>'. $row['date_format(saved_date, 'd/m/y')'] .'</td> It didn't like it. Cheers Paul Link to comment Share on other sites More sharing options...
Antonio Conte Posted June 18, 2012 Share Posted June 18, 2012 No. Look at the result in something like phpMyAdmin. The column name will literally be what I described. That is why you use aliases. You ALWAYS use aliases if you use a function in the SELECT-part of the query. SELECT *, DATE_FORMAT(saved_date, '%D %M %Y') as saved_date FROM saved_curtains 1 Link to comment Share on other sites More sharing options...
Paul Posted June 18, 2012 Author Share Posted June 18, 2012 Antonio, Ahh, I see now. The date formatting worked and I extended the same logic to the ROUND function for 2 decimal places and that worked. Learnt something today. Thanks Paul Link to comment Share on other sites More sharing options...
Recommended Posts