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
Moderator: General Moderators
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";Is that necessary?ridshack wrote:I have a table with the date stored as a year, month and day in separate fields
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...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.
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
Code: Select all
SELECT
*
FROM
salessheet
WHERE
STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') <= STR_TO_DATE('$datestring', '%Y-%m-%d')
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.The best that Ive gathered I should have only one field containing the whole date.?
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)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.
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?Anyway, now that you can construct a date, you can write your query as: ($datestring = "$byear-$bmonth-$bday";)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
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.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')
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>";
}
?>
Code: Select all
SELECT STR_TO_DATE(CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d') FROM salessheetThe 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: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
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')";
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.
}
}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...
}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';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')";Code: Select all
STR_TO_DATE(Code: Select all
CONCAT(ToYear, '-', ToMonth, '-', 'ToDay'), '%Y-%m-%d')Code: Select all
<= STR_TO_DATE('$datestring', '%Y-%m-%d')";Ridshack is suppose to read the manual so he can answer those questions himself?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?
First check if you can build a yyyy-mm-dd string using concat (select concat(....) from table)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...
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";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]