Re: [PHP-DB] Select where date is in period
"boclair" <[EMAIL PROTECTED]> wrote in message 005101c0b2d2$4e6b3920$[EMAIL PROTECTED]">news:005101c0b2d2$4e6b3920$[EMAIL PROTECTED]... | | - Original Message - | From: boclair <[EMAIL PROTECTED]> | To: <[EMAIL PROTECTED]> | Sent: Thursday, March 22, 2001 10:45 PM | Subject: Re: [PHP-DB] Select where date is in period | | | > | > ___Morris___ | > | > The database has a table of equipments with date fields for | > date_online and date_offline. | > | > The requirement is to find which equipments were available on a date | > specified by the user, this being a variable created by the user. | > | > My attempts at scripting for the condition where the date_online <= | > 'specified variable' and the date_offline => 'specified variable' | > totally fail for syntax says mySQL (and possibly for method) | > | > ___Richard___ | > | > Have you checked at the BETWEEN (x and y) clause? | > | > e.g. | > | > SELECT * FROM table WHERE searchdate BETWEEN (date_online AND | > date_offline) | > | > Not tested this on MySQL, but the above is perfectly legal SQL92. | > | > ___Morris_ | > | > Thanks for that. I searched the Manual, yet again and could not | find | > this or like syntax. I tried it though and got | > Warning: Supplied argument is not a valid MySQL result resource. | > | > My attempt, one of many, | > | > $result = mysql_query("select * from plant.eqpt where date_online <= | > $checkdate AND where date_offline => $checkdate order by type, | > size"); | > | > generated the same warning. | | | | Please check this solution for syntax and method. MySQL didn't give | errors or warnings and results were as required. | | $result = mysql_query("select * from plant.eqpt where date_online <= | $checkdate && $checkdate <= date_offline order by type, size"); | | or can use AND in lieu of && | | or can place where conditions in brackets as | | where (date_online <= $checkdate && $checkdate <= date_offline) Sorry. Careless as usual. The variable $checkdate was enclosed in single quotes as $result = mysql_query("select * from plant.eqpt where date_online <= '$checkdate' && '$checkdate' <= date_offline order by type, size"); Tim Morris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Select where date is in period
- Original Message - From: Steve Brett <[EMAIL PROTECTED]> To: boclair <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, March 22, 2001 11:04 PM Subject: RE: [PHP-DB] Select where date is in period > i think i also used >= to indicate 'equal or greater to than' instead of => > like you have ... > > Steve Brett > Internal Development > EMIS Ltd. This seems to have been the problem. All I did for success was to reorder the where conditions. The created date variable and the field values for date_online and date_offline were of the mySQL date type and single quoted. Thanks Tim Morris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Select where date is in period
- Original Message - From: boclair <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, March 22, 2001 10:45 PM Subject: Re: [PHP-DB] Select where date is in period > > ___Morris___ > > The database has a table of equipments with date fields for > date_online and date_offline. > > The requirement is to find which equipments were available on a date > specified by the user, this being a variable created by the user. > > My attempts at scripting for the condition where the date_online <= > 'specified variable' and the date_offline => 'specified variable' > totally fail for syntax says mySQL (and possibly for method) > > ___Richard___ > > Have you checked at the BETWEEN (x and y) clause? > > e.g. > > SELECT * FROM table WHERE searchdate BETWEEN (date_online AND > date_offline) > > Not tested this on MySQL, but the above is perfectly legal SQL92. > > ___Morris_ > > Thanks for that. I searched the Manual, yet again and could not find > this or like syntax. I tried it though and got > Warning: Supplied argument is not a valid MySQL result resource. > > My attempt, one of many, > > $result = mysql_query("select * from plant.eqpt where date_online <= > $checkdate AND where date_offline => $checkdate order by type, > size"); > > generated the same warning. Please check this solution for syntax and method. MySQL didn't give errors or warnings and results were as required. $result = mysql_query("select * from plant.eqpt where date_online <= $checkdate && $checkdate <= date_offline order by type, size"); or can use AND in lieu of && or can place where conditions in brackets as where (date_online <= $checkdate && $checkdate <= date_offline) Tim Morris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Select where date is in period
i think i also used >= to indicate 'equal or greater to than' instead of => like you have ... Steve Brett Internal Development EMIS Ltd. "Privileged and /or Confidential information may be contained in this message. If you are not the original addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, please delete this message, and notify us immediately. Opinions, conclusions and other information expressed in this message are not given or endorsed by my firm or employer unless otherwise indicated by an authorised representative independently of this message." Egton Medical Information Systems Limited. Registered in England. No 2117205. Registered Office: Park House Mews, 77 Back Lane, Off Broadway, Horsforth, Leeds, LS18 4RF > -Original Message- > From: boclair [mailto:[EMAIL PROTECTED]] > Sent: 22 March 2001 12:45 > To: [EMAIL PROTECTED] > Subject: Re: [PHP-DB] Select where date is in period > > > > ___Morris___ > > The database has a table of equipments with date fields for > date_online and date_offline. > > The requirement is to find which equipments were available on a date > specified by the user, this being a variable created by the user. > > My attempts at scripting for the condition where the date_online <= > 'specified variable' and the date_offline => 'specified variable' > totally fail for syntax says mySQL (and possibly for method) > > ___Richard___ > > Have you checked at the BETWEEN (x and y) clause? > > e.g. > > SELECT * FROM table WHERE searchdate BETWEEN (date_online AND > date_offline) > > Not tested this on MySQL, but the above is perfectly legal SQL92. > > ___Morris_ > > Thanks for that. I searched the Manual, yet again and could not find > this or like syntax. I tried it though and got > Warning: Supplied argument is not a valid MySQL result resource. > > My attempt, one of many, > > $result = mysql_query("select * from plant.eqpt where date_online <= > $checkdate AND where date_offline => $checkdate order by type, > size"); > > generated the same warning. > > Still searching for a solution > > Tim Morris > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: > [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Select where date is in period
how are the times stored in the db ? if they are dates or timestamps the comparison will need to be single quoted instead of: >My attempt, one of many, >$result = mysql_query("select * from plant.eqpt where date_online <= >$checkdate AND where date_offline => $checkdate order by type, >size"); >generated the same warning. >Still searching for a solution try $result = mysql_query("select * from plant.eqpt where date_online <='".$checkdate."' AND date_offline => '".$checkdate."' order by type,size"); you also had 2 WHERE clauses. there can be only one. like the highlander. so if $checkdate was 2001-03-22 then the query would look like this to the db: select * from plant.eqpt where date_online <='2001-03-22' AND date_offline =>'2001-03-22' order by type,size i haven't used between on dates but it is valid SQL. good for ranges of numbers. Steve Brett Internal Development EMIS Ltd. "Privileged and /or Confidential information may be contained in this message. If you are not the original addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, please delete this message, and notify us immediately. Opinions, conclusions and other information expressed in this message are not given or endorsed by my firm or employer unless otherwise indicated by an authorised representative independently of this message." Egton Medical Information Systems Limited. Registered in England. No 2117205. Registered Office: Park House Mews, 77 Back Lane, Off Broadway, Horsforth, Leeds, LS18 4RF > -----Original Message----- > From: boclair [mailto:[EMAIL PROTECTED]] > Sent: 22 March 2001 12:45 > To: [EMAIL PROTECTED] > Subject: Re: [PHP-DB] Select where date is in period > > > > ___Morris___ > > The database has a table of equipments with date fields for > date_online and date_offline. > > The requirement is to find which equipments were available on a date > specified by the user, this being a variable created by the user. > > My attempts at scripting for the condition where the date_online <= > 'specified variable' and the date_offline => 'specified variable' > totally fail for syntax says mySQL (and possibly for method) > > ___Richard___ > > Have you checked at the BETWEEN (x and y) clause? > > e.g. > > SELECT * FROM table WHERE searchdate BETWEEN (date_online AND > date_offline) > > Not tested this on MySQL, but the above is perfectly legal SQL92. > > ___Morris_ > > Thanks for that. I searched the Manual, yet again and could not find > this or like syntax. I tried it though and got > Warning: Supplied argument is not a valid MySQL result resource. > > My attempt, one of many, > > $result = mysql_query("select * from plant.eqpt where date_online <= > $checkdate AND where date_offline => $checkdate order by type, > size"); > > generated the same warning. > > Still searching for a solution > > Tim Morris > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: > [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Select where date is in period
___Morris___ The database has a table of equipments with date fields for date_online and date_offline. The requirement is to find which equipments were available on a date specified by the user, this being a variable created by the user. My attempts at scripting for the condition where the date_online <= 'specified variable' and the date_offline => 'specified variable' totally fail for syntax says mySQL (and possibly for method) ___Richard___ Have you checked at the BETWEEN (x and y) clause? e.g. SELECT * FROM table WHERE searchdate BETWEEN (date_online AND date_offline) Not tested this on MySQL, but the above is perfectly legal SQL92. ___Morris_ Thanks for that. I searched the Manual, yet again and could not find this or like syntax. I tried it though and got Warning: Supplied argument is not a valid MySQL result resource. My attempt, one of many, $result = mysql_query("select * from plant.eqpt where date_online <= $checkdate AND where date_offline => $checkdate order by type, size"); generated the same warning. Still searching for a solution Tim Morris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Select where date is in period
BDY.RTF -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]