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:

Code: Select all

<?php
echo mysql_error();
?>

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