Page 1 of 1
Mysql Select question
Posted: Fri Dec 10, 2004 4:15 am
by bytte
I want to select the rows where the first part of my datetime column is equal to the first part of a datetime variable (php).
Assuming this is the datetime in the database:
2005-01-19 21:00:00
I want to select that row as well if I search for this datetime:
2005-01-19 20:14:00
So the date characters should be exact the same. The time characters don't matter.
How can I do this?
I'm aware that I could have used only 'date' as my field type, but that would not be compatible with other code so I chose for datetime.
// SELECT * FROM shows WHERE datetime = '$datetime';
Posted: Fri Dec 10, 2004 4:24 am
by Weirdan
[mysql_man]date[/mysql_man]
Code: Select all
select * from shows where date(datetime) = '$datetime'
Posted: Fri Dec 10, 2004 4:26 am
by bytte
cooool! thanks.
Posted: Fri Dec 10, 2004 5:15 am
by bytte
hmm. i cried victory too soon.
i get this:
supplied argument is not a valid MySQL result resource
This is my query:
SELECT * FROM shows_shows WHERE datetime >= CURDATE() AND date(datetime)='2005-01-01' AND city='New York' AND country='USA' ORDER BY datetime ASC
Posted: Fri Dec 10, 2004 5:19 am
by Weirdan
does it return any rows?
Posted: Fri Dec 10, 2004 5:22 am
by bytte
no:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource
Posted: Fri Dec 10, 2004 7:34 am
by protokol
Try this to see what happened:
Posted: Fri Dec 10, 2004 7:46 am
by bytte
THis is what it says:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(datetime)='2005-01-19' AND city='New York' AND country='USA' O
hmmm... any idea?
Posted: Fri Dec 10, 2004 7:54 am
by bytte
Allright, I got it:
mysql.org wrote:DATE(expr)
Extracts the date part of the date or datetime expression expr. mysql> SELECT DATE('2003-12-31 01:02:03');
-> '2003-12-31'
DATE() is available as of MySQL 4.1.1.
I'm running 4.0.12

Posted: Fri Dec 10, 2004 8:05 am
by bytte
I've done it this way now:
Code: Select all
SELECT * FROM shows_shows WHERE datetime >= CURDATE() AND datetime='2005-01-01%' AND city='New York' AND country='USA' ORDER BY datetime ASC