[SOLVED] select between DATETIME

[RavenStar]

Hi all,

I've been searching for hours and tried countless variations and haven't come close to getting what I need, which is pretty simply but MySQL can be a pain sometimes.

 

Basically I want to list and results between the two user-defined dates, which it seems to do except it shows the same result 4 times(once for each record in the "division" table as far as i can tell).

Obviously my date_when cell is a DATETIME.

 

$start_date = "2006-11-22";
$end_date =2007-09-29";
$query =  "SELECT DISTINCT report.id AS 'ID', report.date_when, division.name, report.head_host, report.category FROM report, division WHERE report.date_when ";
$query .= "BETWEEN CAST('" . $start_date . " 00:00:00' AS DATETIME) AND CAST('" . $end_date . " 00:00:00' AS DATETIME) ORDER BY report.date_when DESC";

 

It cant be my code I use to list all the results into a table because I use the exact same code to display the most recent results.

 

If anyone could provide some insight on this or point me in the right direction I would greatly appreciate it.

 

Thanks in advanced~

[aschk]

Try and avoid the use of DISTINCT in SQL.

 

Your query:

SELECT DISTINCT report.id AS 'ID'
         , report.date_when
         , division.name
         , report.head_host
         , report.category
FROM report, division 
WHERE report.date_when BETWEEN CAST('" . $start_date . " 00:00:00' AS DATETIME) AND CAST('" . $end_date . " 00:00:00' AS DATETIME) 
ORDER BY report.date_when DESC

 

What is the DDL for your table (do SHOW CREATE TABLE <tablename>  , for report and division).

Also you're doing an inner join based on NOTHING, so for EVERY row in report you are joining to EVERY row in division.

 

What is the key between reports and divisions? i.e. what links them? Give me this and you have your answer.

[RavenStar]

Where div_id (integer in reports) is the id of a row in divisions table.

 

division table:
id 	name
1 	Bloodlust
2 	Riches
3 	Elixir
4 	TAG

 

reports table:
id 	date_when 	 	     head_host 	 	div_id
44 	2007-03-20 01:00:00 	JoeBlogs 	     3

 

Output:
Date: 2007-03-20 01:00:00
By: JoeBlogs
Division: Elixir

[obsidian]

Try and avoid the use of DISTINCT in SQL.

 

Why do you want to avoid using a tool provided within the database for your use? I agree that if your SQL statement can be optimized to not have to use it, that's better, but there are always times where DISTINCT is necessary. I believe this is what you meant by your statement, but I did want to clarify to avoid confusion.

 

If anyone could provide some insight on this or point me in the right direction I would greatly appreciate it.

 

It looks as though you are not limiting your results based on the div_id. You are simply collecting the full division name report. You need to have another where (or else, use a join) to limit the names to just the matching one. Also, when using BETWEEN, you don't need to cast. Also, since you're doing a date search, I would recommend casting your date_when column to a DATE and comparing to the dates only. This way, you pick up events that happen during the entire day of your end date, not just those that happen at midnight ('00:00:00'). Try this:

SELECT r.id AS 'ID', r.date_when, d.name, r.head_host, r.category
FROM report r LEFT JOIN division d ON r.div_id = d.id
WHERE DATE(r.date_when) BETWEEN '{$start_date}' AND '{$end_date}'
ORDER BY r.date_when DESC

 

Hope this helps.

[RavenStar]

Is there really any need to use JOIN, isn't there a different way? I've been using queries like;

$query =  "SELECT DISTINCT report.id AS 'ID', date_when, division.name, head_host, category FROM report, division WHERE report.div_id = division.id ORDER BY date_when DESC LIMIT 0, 35";

 

I've never used JOIN and it seems to be a little tacky?

Any suggestions would be great, also is this how it should look;

$query  = "SELECT report.id AS 'ID', report.date_when, division.name, report.head_host, report.category ";
$query .= "FROM report LEFT JOIN division ON report.div_id = division.id ";
$query .= "WHERE DATE(report.date_when) BETWEEN '{$start_date}' AND '{$end_date}' ORDER BY report.date_when DESC";

 

As on the 2nd line you wrote it has "report r LEFT JOIN division d ON r.div........." and I wasn't sure what the "r" and "d" were for?

 

Thanks, I appreciate it~

[aschk]

The reason to avoid using distinct is that most people don't understand how it works, and also that it involves a roll-up after a roll-out.

 

Here is your query without a JOIN:

SELECT r.id AS 'ID'
         , r.date_when
         , (SELECT name
             FROM division d
             WHERE r.div_id = d.id
           ) as name
         , r.head_host
         , r.category
FROM report r 
WHERE DATE(r.date_when) BETWEEN '{$start_date}' AND '{$end_date}'
ORDER BY r.date_when DESC

[aschk]

In answer to your question regarding "r" and "d". These are alises to the database table, i.e. shortnames.

[RavenStar]

Thanks aschk, though it still isnt working.

$query = "SELECT report.id AS 'ID', report.date_when, division.name, report.head_host, report.category ";
$query .= "FROM report, division WHERE DATE(report.date_when) BETWEEN '{$start_date}' AND '{$end_date}' ORDER BY report.date_when DESC";

 

Output: (all with same id - 362)

2007-06-30 23:06:00  	Bloodlust  	QuickSand  	
2007-06-30 23:06:00 	TAG 	          QuickSand 	
2007-06-30 23:06:00 	Riches         	QuickSand 	
2007-06-30 23:06:00 	Elixir         	QuickSand 	

[obsidian]

The reason to avoid using distinct is that most people don't understand how it works, and also that it involves a roll-up after a roll-out.

 

Here is your query without a JOIN:

SELECT r.id AS 'ID'
         , r.date_when
         , (SELECT name
             FROM division d
             WHERE r.div_id = d.id
           ) as name
         , r.head_host
         , r.category
FROM report r 
WHERE DATE(r.date_when) BETWEEN '{$start_date}' AND '{$end_date}'
ORDER BY r.date_when DESC

 

Rather than having an embedded query, I'd recommend doing something like this (still using an implicit join):

SELECT r.id AS 'ID', r.date_when, d.name, r.head_host, r.category
FROM report r, division d
WHERE DATE(r.date_when) BETWEEN '{$start_date}' AND '{$end_date}'
AND r.div_id = d.id
ORDER BY r.date_when DESC

[RavenStar]

Woohoo, it works.

 

$query = "SELECT report.id AS 'ID', report.date_when, division.name, report.head_host, report.category ";
$query .= "FROM report, division WHERE DATE(report.date_when) BETWEEN '{$start_date}' AND '{$end_date}' AND report.div_id = division.id ORDER BY report.date_when DESC";

 

I finally added the "AND report.div_id = division.id" and it all works fine, it display 1 of each result as it should with no problems as I can see so far(yet again, i am really tired =P)

 

Thanks guys, you helped a lot!