Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Greater than equal MySQL statement in PHP

Why is my query not working?  It is not showing results based on SearchPrice being >= 500000

Everything else works fine, it simply ignores this criteria?
$query = @mysql_query("SELECT * FROM RESI WHERE CITY='Fort Myers' OR CITY='Cape Coral' AND SearchPrice >= '500000' AND datediff(NOW(),EntryDate) > 7  ORDER BY EntryDate DESC LIMIT 0,10");

Open in new window

0
pda4me
Asked:
pda4me
  • 7
  • 7
  • 2
  • +1
3 Solutions
 
Ray PaseurCommented:
Maybe the data definition is wrong?  Is that >= 500000 or '500000' ?
0
 
pda4meAuthor Commented:
right now I am using '' to encapsulate the price value.  I tried removing it and still same thing.
0
 
pda4meAuthor Commented:
SearchPrice is a decimal to 12 length FYI...bascially trying to return just values over 500K
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Ray PaseurCommented:
What is being returned by the query?  Anything?
0
 
pda4meAuthor Commented:
Yes, records of any value...for example first record is SearchPrice of 43000
0
 
Ray PaseurCommented:
Do you have phpMyAdmin?  Can you look at the decimal value and see what it looks like?  The CREATE TABLE statement would tell you the exact definition.
0
 
pda4meAuthor Commented:
decimal value is 0
0
 
Ray PaseurCommented:
I'm pretty sure you have the query syntax right, so I think it must be something about the data.
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_greater-than-or-equal
0
 
HainKurtSr. System AnalystCommented:
use parantheses around first two condition

CITY='Fort Myers' OR CITY='Cape Coral' ...
-->
(CITY='Fort Myers' OR CITY='Cape Coral') ...

nothing to do with 50000, and don't use ' around 500000
0
 
Ray PaseurCommented:
Try it like this: '500000.00' and tell us what happens, thanks.
0
 
HainKurtSr. System AnalystCommented:
I mean this:

$query = @mysql_query("SELECT * FROM RESI WHERE (CITY='Fort Myers' OR CITY='Cape Coral') AND SearchPrice >= 500000 AND datediff(NOW(),EntryDate) > 7 ORDER BY EntryDate DESC LIMIT 0,10");
0
 
Ray PaseurCommented:
@HainKurt: Good catch, if that's what the query is really supposed to do!
0
 
pda4meAuthor Commented:
@HainKurt, that did it.  Thanks guys....what was the deal?  I have to use () around previous sets of criteria?
0
 
LowfatspreadCommented:
your query
was city='fort myers'
or city='cape coral' and price > 50000...

so you asked for either fort myers or the price restricted cape coral data...

use IN

city in ('fort myers','cape coral')

and try to avoid mixing AND and OR's unless you explicitly bracket...

0
 
Ray PaseurCommented:
The () are just like in math and logic problems.  If you want to make two conditionals in a set, wrap them up!
0
 
pda4meAuthor Commented:
Okay, I just realized this query is still not working.  I changed the ORDER BY to ASC and it is displaying results from 2006.

I only want results from 7 days to current day?

$query = @mysql_query("SELECT * FROM RESI WHERE (CITY='Fort Myers' OR CITY='Cape Coral') AND SearchPrice >= 500000 AND datediff(NOW(),EntryDate) > 7 ORDER BY EntryDate ASC LIMIT 0,10");

Open in new window

0
 
pda4meAuthor Commented:
Thanks Guys, great help!
0
 
LowfatspreadCommented:
$query = @mysql_query("SELECT * FROM RESI WHERE CITY in ('Fort Myers','Cape Coral')
                           AND SearchPrice >= 500000
                              AND datediff(NOW(),EntryDate) <= 7
                     ORDER BY EntryDate ASC LIMIT 0,10");


you have the datediff test the wrong way around....
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now