mysql_query("SELECT * FROM ct_news ORDER BY `datetime` DESC");
$SQL = mysql_query("SELECT * FROM ct_news");
while ($Data = mysql_fetch_assoc($SQL) && i <3)
{
mysql_query("SELECT DATE_FORMAT(datetime, ''%Y-%m-%d %T'')
FROM ct_news");
//Display entry
i++;
}
Thanks, as it stands now this is some pretty uglee code i have written and i am sure that with your help i can fix it...
Well i have gotten one lil' step further...though i should have noticed this before... but is still CAN'T figure out how to add the dateformat into one query
First, change that select * to a select with each field enumerated: i.e.
select fieldA
, fieldB
, fieldC
....etc.
from sometable
where.....
order by somefield desc
Name the fields and format in a readable way - this make it more readable and save you time and grief when you have to look at the script in six months time and won't remember what fields are in this table!
Next - take a look at the mysql manual at http://www.mysql.com and see what functions are available that could be used in your sequel. Much you can do with SQL, depending on the flavor!
As fractalvibes said, you need to specify which fields you want to return from the database, then you can have one query which looks something like this:
$SQL = mysql_query("SELECT bodytext,author, DATE_FORMAT('%M %e, %Y %H:%i',datetime) AS datetime, datetime AS order_date FROM ct_news ORDER BY order_date DESC LIMIT 3");
datetime just happens to be the field name not to be confused by the field type.
Why do i have to assign an alias to my DATE_FORMAT()? Any suggestions to why it's not budging? Also is it necessary to assign an alias to the datetime itself to sort? I mean does MySQL only sort dates that are in the "accepted" format?
<?php
$sql = "SELECT bodytext, author, DATE_FORMAT('%M %e, %Y %H:%i', datetime) AS datetime, datetime AS order_date FROM ct_news ORDER BY order_date DESC LIMIT 3";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
while ($row = mysql_fetch_assoc($result)) {
echo 'date and time: '.$row['datetime'].'<br />';
}
?>
You need to assign an alias to the result of the DATE_FORMAT() so that it's easier to grab out of the array later. Where I've formatted a date and then sorted on that same date field, I've found that MySQL sorts the formatted date as a string which causes problems.
Do you have a copy of phpMyAdmin I tend to copy my SQL statements into there and play about with them to see what I get.
I do have phpadmin but lately i wasn't able to execute queries... but i really needed this one done... everything runs fine... i basically get all the other fields... but the datetime field is always blank for some reason.
This is really strange.
$SQL = mysql_query("SELECT bodytext,author, DATE_FORMAT('%M %e, %Y %H:%i',date_time) AS datetime, date_time AS order_date FROM ct_news ORDER BY order_date DESC LIMIT 3");
while ( $Data = mysql_fetch_assoc($SQL) )
Yea the cryptic thing is that the output is actually *blank* I also output author and bodytext and they are displayed just fine... also, if i output order_date i get the date displayed in the dateformat format, that is the format the MySQL table stores it in and not the formatted way i would like it in.
Have you ever had this problem? because if i am the only one with it... i mean i might as well use 2 fields... one to store the date so that MySQL can order them and another (a string) that contains an already formatted date.
I hope i don;t have to settle for this as i think i am really close to the solution. I swear my problem is like some comma or something or funny syntax because i don't know why this is acting like this....
I GOT IT I GOT IT....WOHOOO...
*runs out the house, shirt over his head*
I GOT IT I GOT IT
*street bypassers, "he is definitely a programmer"*
Okay this is ridiculous the time i spent trying to figure this out... but on the verge of breaking my head whils baging it on the desk i had the idea to start shuffling things around, and then it CAME!!!
$SQL = mysql_query("SELECT bodytext,author, DATE_FORMAT(date_time, '%M %e, %Y %H:%i') AS datetime, date_time AS order_date FROM ct_news ORDER BY order_date DESC LIMIT 3");
Thanks twigletmac for your patience... it was really sad that all i got were blank fields... i really didn't know what was wrong, however, the problem was in a simple misplacement of the values passed to DATE_FORMAT();