Page 1 of 1

[Solved]SQL query - date range not working correctly

Posted: Sat Nov 04, 2006 2:54 am
by ridshack
I have a table with the date stored as a year, month and day in separate fields and I’m trying to figure out how to query a date range. This is what I have but it doesn’t quite work. Any ideas appreciated.

Code: Select all

$query  = "SELECT * FROM salessheet WHERE (ToYear='$byear' and ToMonth <= '$bmonth' and ToDay <= '$bday') AND (ToYear='$ayear' and ToMonth >= '$amonth' and ToDay >= '$aday') ORDER BY 'salessheet' ASC";

Thank you,
Jason

Re: SQL query - date range not working correctly

Posted: Sat Nov 04, 2006 3:07 am
by volka
ridshack wrote:I have a table with the date stored as a year, month and day in separate fields
Is that necessary?

Re: SQL query - date range not working correctly

Posted: Sat Nov 04, 2006 5:01 am
by timvw
ridshack wrote:I have a table with the date stored as a year, month and day in separate fields and I’m trying to figure out how to query a date range. This is what I have but it doesn’t quite work.
That's what you get for not using the available datatypes your sql dbms offers... Usually you can combine the column values in order to build a real date value...

Code: Select all

-- mysql
SELECT STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') FROM salessheet
-- oracle
SELECT TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), 'YYYY-MM-DD') FROM salessheet
For other sql dbms i suggest that you consule the section on date and time functions in it's manual...

Anyway, now that you can construct a date, you can write your query as: ($datestring = "$byear-$bmonth-$bday";)

Code: Select all

SELECT
               *
FROM 
              salessheet
WHERE
               STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') <= STR_TO_DATE('$datestring', '%Y-%m-%d')

Posted: Sat Nov 04, 2006 1:06 pm
by ridshack
First off thank you for your replies, I am a bit new to php/databases. Ok, Im completely new. But instead of trying to make work what is not right let me find out the best way to have done it.

The best that Ive gathered I should have only one field containing the whole date.?

Do you recommend a format for that date? Year month day like 20061106? BTW I’m submitting the date via a form so its not auto generated by the current system time.

And again thank you, I really appreciate your guidance.


Jason

Posted: Sat Nov 04, 2006 1:20 pm
by timvw
The best that Ive gathered I should have only one field containing the whole date.?
Actually, i was trying to say that you should use the most appropriate datatypes for the data you have.. Eg: if a property of your data represents a date it makes sense to use a DATE type instead of using 3 integers.

(Eg: identify the nature of the properties on your data, and then try to find the one in your dbms that most ressembles that nature. For MySQL you would start here: http://dev.mysql.com/doc/refman/5.0/en/data-types.html)
Do you recommend a format for that date? Year month day like 20061106? BTW I’m submitting the date via a form so its not auto generated by the current system time.
Usually, your dbms recognizes only one format. Because a lot of people prefer to use their own format most sql dbmses have a wide range of functions to help you in the conversion of your custom format to their representation. I've already posted a an example that converts a custom string to a date... In a PHP/MySQL context you'll probably also want to convert a unixtimestamp to a mysql date using the from_unixtime function. And offcourse you can also convert the dbms format to your custom format using the date_format function. (For Mysql those functions can be found here: http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html)

Re: SQL query - date range not working correctly

Posted: Sat Nov 04, 2006 7:02 pm
by ridshack

Code: Select all

-- mysql
SELECT STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') FROM salessheet
-- oracle
SELECT TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), 'YYYY-MM-DD') FROM salessheet
Anyway, now that you can construct a date, you can write your query as: ($datestring = "$byear-$bmonth-$bday";)
Im sorry my brain doesn’t get it. Do I run this select statement first? Does it store anything in a variable? Do I specify any values. What ever happens it is suppose to combine my 3 separate fields into a proper date format. Is it just a representation of my data but not actual data?

Ok

Code: Select all

SELECT
               *
FROM 
              salessheet
WHERE
               STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') <= STR_TO_DATE('$datestring', '%Y-%m-%d')
I don’t want have anyone write my code for me but since Im completely green I learn from a working script or at least a general representation of how these two select statements work together. Would you mind showing me how these two statements fit into a php page to pull out the desired results? I start by giving it a stab.


Code: Select all

<?php
//Start Date
$ayear =  $_GET["ayear"];
$amonth = $_GET["amonth"];
$aday = $_GET["aday"];
//End Date
$byear = $_GET["byear"];
$bmonth = $_GET["bmonth"];
$bday = $_GET["bday"];

$datestring = "$byear-$bmonth-$bday";

$dbhost = 'localhost';
$dbuser = 'name';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
echo "$conn";
echo "$datestring";

mysql_select_db("Office",$conn);

$query = "SELECT  * FROM salessheet WHERE           STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') <= STR_TO_DATE('$datestring', '%Y-%m-%d')";

$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    echo "Year, Month, Day :<b>{$row['ToYear']}" . "/{$row['ToMonth']}" . "/{$row['ToDay']}</b> <br>" .
        "Name : {$row['Name']} <br>" .
"Server Hours : {$row['Server']} <br>" .
"Desktop Hours : {$row['DTtotal']} <br>" .
        "SS# : {$row['id']} <br>" .
         "Tech : {$row['Tech']} <br><br>";

}
?>

Actually I don’t really know how to integrate this piece of code.

Code: Select all

SELECT STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') FROM salessheet
Thanks again for your help.

Jason

Re: SQL query - date range not working correctly

Posted: Sat Nov 04, 2006 8:26 pm
by timvw
ridshack wrote:Actually I don’t really know how to integrate this piece of code:

Code: Select all

SELECT STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') FROM salessheet
The was simply an example to show how you can create a date type. The code you posted seems that you have understood how the mechanism works, and that you were able to integrate that...

ridshack wrote:

Code: Select all

$query = "SELECT  * FROM salessheet WHERE           STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') <= STR_TO_DATE('$datestring', '%Y-%m-%d')";
:)

Re: SQL query - date range not working correctly

Posted: Sat Nov 04, 2006 8:40 pm
by timvw
I believe it's better to verify if the variables are available.. So i start like this:

Code: Select all

$required_variables ('ayear', 'amonth', 'aday', 'byear', 'bmonth', 'bday');
foreach($required_variables as $required_variable) {
 if (!array_key_exists($_GET[$required_variable])) {
  // handle the fact that a value is missing:
  // provide a default value or request the to provide all the values by redirecting him to the input form.
 }
}
First we verify if the year, month and day are valid values (this sample uses very simple validation)

Code: Select all

if (!preg_match('#^\d{4}$#', $_REQUEST['ayear']) || !preg_match('#^\d{2}$#', $_REQUEST['amonth']) || !preg_match('#^\d{2}$#', $_REQUEST['aday'])) {
 // invalid user input...
}
Since we know for sure that the 3 values are numeric no further preparation is required (otherwise now would be the time to prepare the data with http://www.php.net/mysql_real_escape_string).

Code: Select all

$datestring = $_REQUEST['ayear'] . '-' . $_REQUEST['amonth'] . '-' . $_REQUEST['aday'];
$query = "SELECT * FROM salessheet WHERE STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') <= '$datestring';

Posted: Sat Nov 04, 2006 9:23 pm
by ridshack

Code: Select all

$query = "SELECT  * FROM salessheet WHERE STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') <= STR_TO_DATE('$datestring', '%Y-%m-%d')";
Everything is starting to look good. With the current code the sql statement dosnt pull any data. Maybe I need to try and understand the query.

Code: Select all

STR_TO_DATE(
STR_TO_DATE Is suppose to query the data attempting to present the data in a valid date format?

Code: Select all

CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d')
CONCAT Is suppose to string the data together?

Code: Select all

<= STR_TO_DATE('$datestring', '%Y-%m-%d')";
This section again is taking my user submitted variables and putting it in a proper date format.

Any ideas why it doesn’t work? Is there anyway I can puit this in smaller steps to see what is working and what isnt...

Jason

Posted: Sat Nov 04, 2006 10:37 pm
by timvw
ridshack wrote: STR_TO_DATE Is suppose to query the data attempting to present the data in a valid date format?
CONCAT Is suppose to string the data together?
Ridshack is suppose to read the manual so he can answer those questions himself?

ridshack wrote: Any ideas why it doesn’t work? Is there anyway I can puit this in smaller steps to see what is working and what isnt...
First check if you can build a yyyy-mm-dd string using concat (select concat(....) from table)
Then check if you can convert a yyyy-mm-dd string to a date (select str_to_date( datestring, format) from table)

Posted: Sun Nov 05, 2006 12:19 am
by ridshack
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Im not yet able to understand the solution provided. 

It seems to me that simpler is better so I created a Date Field and add the date as MYSQL would expect. From there I guessed at the query below which so far seems to work fine.

Code: Select all

//Start Date
$ayear =  $_GET["ayear"];
$amonth = $_GET["amonth"];
$aday = $_GET["aday"];
//End Date
$byear = $_GET["byear"];
$bmonth = $_GET["bmonth"];
$bday = $_GET["bday"];

$bdatestring = "$byear-$bmonth-$bday";
$adatestring = "$ayear-$amonth-$aday";

$query  = "SELECT * FROM salessheet WHERE fulldate <= '$bdatestring' AND fulldate >= '$adatestring' ORDER BY 'fulldate' ASC";
I do want to thank you timvw for encouraging me to fish rather then just giving me a fish to eat.

Ridshack


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]