Mysql Select question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

Mysql Select question

Post 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';
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

[mysql_man]date[/mysql_man]

Code: Select all

select * from shows where date(datetime) = '$datetime'
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

Post by bytte »

cooool! thanks.
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

does it return any rows?
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

Post by bytte »

no:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Post by protokol »

Try this to see what happened:

Code: Select all

<?php
echo mysql_error();
?>
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

Post 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?
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

Post 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 :(
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

Post 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
Post Reply