Possible MySql syntax issue

Question

I'm trying to figure out why my query isn't working, but it doesn't make sense to me. I'm accessing a database via php that lists states and their affiliated cities/towns, and I have a javascript script which displays them as menus. Each state has an ID which is incremented by 100,000 (meaning in the database, "Alabama" is 100,000 and "Alaska" is 200,000.) So I tested my query out in MySQL and it works properly, and I tested the javascript value that was being sent to the php script and it was the correct one, but the returned menu is containing many more values than it should.

My query looks like:

mysql_query("SELECT * FROM fullstates WHERE code > '$state' AND code < '$state' + 100000");

where state is the ID of the previous state that was picked. The only thing I could think of is that the AND isn't working properly?

Can you post some existing data and expected results, as well as the table/column definition? — Nonym
– Nonym, Commented Dec 6, 2011 at 1:55
Also, if code is a multiple of 100000, then both code > $state and code < $state + 100000 will fail! — Ken Wayne VanderLinde
– Ken Wayne VanderLinde, Commented Dec 6, 2011 at 1:57

Jake Feasel · Accepted Answer · 2011-12-06 01:55:43Z

1

Are your codes numeric or text? Seems like they should be numeric, in which case you should drop your single quotes:

mysql_query("SELECT * FROM fullstates WHERE code > $state AND code < ($state + 100000)");

You should also parameterize this, but that's another concern.

answered Dec 6, 2011 at 1:55

Jake Feasel

17k5 gold badges55 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Over a year ago

You are correct sir! Knew it had to be a syntax issue, and yes I realize to group them as you did as well. Thanks!

user497849 · Accepted Answer · 2011-12-06 01:59:37Z

0

mysql_query("SELECT * FROM fullstates WHERE code > '$state' AND code < '$state' + 100000");

...WHERE 200,000 > 100,000 AND 200,000 < 100,000 + 100,000

if the value of state = 100,000 and code is 200,000, then the first boolean evaluation is true, but now you check if code(200,000) is LESS than state(100,000) + 100,000.

In other words you're checking if 200,000 < 200,000, so your check is WHERE (TRUE[first expression] AND FALSE[second expression])

answered Dec 6, 2011 at 1:59

user497849

Collectives™ on Stack Overflow

Possible MySql syntax issue

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related