Page 1 of 1

Greater than in mysql query

Posted: Fri Jun 17, 2005 6:05 pm
by spicag

Code: Select all

$result = mysql_query(&quote;SELECT * FROM grants WHERE YEAR(Disposition_Date) = '$year' AND Grant_Amount > '$AmountValue'   $orderby&quote;,$db);
I am having an issue with using ">" in a mysql query. I get the correct results when I use the "=" symbol. Am I using the correct syntax?

Posted: Fri Jun 17, 2005 6:12 pm
by Chris Corbyn
Ok, What is $orderdy ? Is that in the correct syntax (ORDER BY `field_name`)?

Secondly, depending on your column types (post an SQL export please) you might need to do that without the single quotes on $AmountValue.

Another thing is that you might need to escape your values before including them in the query (although with numbers there's nothing to escape).

Posted: Fri Jun 17, 2005 6:52 pm
by spicag
Doing without the single quotes on $AmountValue worked. Why is this?

Code: Select all

/*
MySQL Backup
Source Host:           10.94.80.234
Source Server Version: 4.0.21-nt
Source Database:       geographical_grantsdb
Date:                  2005/06/17 16:47:09
*/

SET FOREIGN_KEY_CHECKS=0;
use geographical_grantsdb;
#----------------------------
# Table structure for grants
#----------------------------
CREATE TABLE `grants` (
  `Disposition_Date` varchar(255) default NULL,
  `Program_Area` varchar(255) default NULL,
  `Legal_Name` varchar(255) default NULL,
  `Project_Title` varchar(255) default NULL,
  `Geographical_Area_1` varchar(255) default NULL,
  `Geographical_Area_2` varchar(255) default NULL,
  `Grant_Amount` varchar(255) default NULL,
  `City` varchar(255) default NULL,
  `Status` varchar(255) default NULL,
  `WWW_Address` varchar(255) default NULL,
  `Internal` varchar(255) default NULL,
  FULLTEXT KEY `Keyword` (`Project_Title`,`Legal_Name`),
  FULLTEXT KEY `Program_Area` (`Program_Area`,`Legal_Name`,`Project_Title`,`City`,`Status`,`Internal`)
) TYPE=MyISAM;ї

Posted: Fri Jun 17, 2005 7:02 pm
by Chris Corbyn
spicag wrote:Doing without the single quotes on $AmountValue worked. Why is this?
I didn't read your table structure sorry I'm drunk - it's 1am here in the UK on a friday night but basically if you want to do these sorts of checks on numbers then singles quotes make mysql treat them as strings and that's not valid for things such as greater than... "=" is a bit different since it's a slightly forgiving when it's not "identical" but is the same....