Page 1 of 1
BIG sql query help
Posted: Thu Jul 31, 2003 8:46 am
by mikusan
I would like to combine the following queries into one big one and i am hoping for suggestions that would make it better:
Code: Select all
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...
Posted: Thu Jul 31, 2003 5:59 pm
by mikusan
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
here is what i got:
Code: Select all
$SQL = mysql_query("SELECT * FROM ct_news ORDER BY `datetime` DESC");
while ( ($Data = mysql_fetch_assoc($SQL)) && ($i <3) )
{
//Do stuff
$i++;
}
But where does this one fit in?
Code: Select all
mysql_query("SELECT DATE_FORMAT(''%M %e, %Y %H:%i'') FROM ct_news");
Posted: Thu Jul 31, 2003 11:11 pm
by fractalvibes
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!
Phil J.
Posted: Fri Aug 01, 2003 3:33 am
by twigletmac
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:
Code: Select all
SELECT field1, field2, field3, DATE_FORMAT('%M %e, %Y %H:%i', field4) AS field4, field4 AS order_date FROM ct_news ORDER BY order_date
Note that when you put this SQL into the mysql_query() call, you should not escape the single quotes,
Code: Select all
mysql_query("SELECT DATE_FORMAT(''%M %e, %Y %H:%i'', datefield) FROM ct_news");
should be
Code: Select all
mysql_query("SELECT DATE_FORMAT('%M %e, %Y %H:%i', datefield) FROM ct_news");
Mac
Posted: Fri Aug 01, 2003 8:25 am
by mikusan
Hmmm i am still having problems, mainly i am not displaying the date at all. Here is some of my latest improvements, as you suggested:
Code: Select all
$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?
Thanks again for your help...
Posted: Fri Aug 01, 2003 9:50 am
by twigletmac
What do you get if you try something like:
Code: Select all
<?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.
Mac
Posted: Fri Aug 01, 2003 10:43 am
by mikusan
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.
Posted: Sat Aug 02, 2003 7:26 am
by twigletmac
Have you tried renaming the field? Sometimes DBs can get sniffy about naming a field with the name of a field type.
Mac
Posted: Sun Aug 03, 2003 8:12 am
by mikusan
I tried that...now my field is claled date_time... i called it other stuff.. but this is now getting really frustrating...it still isn't working...
Code: Select all
$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) )
I get an error:
Code: Select all
mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource
Which i think means that my mysql_query is funny...
Posted: Sun Aug 03, 2003 2:47 pm
by twigletmac
mikusan wrote:Which i think means that my mysql_query is funny...
So you need to add some error handling, try the example I posted a little while ago:
twigletmac wrote:Code: Select all
<?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 />';
}
?>
and see what MySQL says.
Mac
Posted: Sun Aug 03, 2003 6:16 pm
by mikusan
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....
Thanks twigletmac
Posted: Mon Aug 04, 2003 3:17 am
by twigletmac
If you're still getting this error:
Code: Select all
mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource
then there is an issue with the query or the database selection or connection - have you added the
mysql_error() call?
Code: Select all
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
Mac
Posted: Mon Aug 04, 2003 9:24 am
by mikusan
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!!!
Code: Select all
$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();
geez!!!