MySQL SELECT query syntax - not working as expected?

Question

I can't seem to get this to work,

Basically, what I want to do is return all the rows in which the column timestamp is more than the variable $timestamp, and the column hit_counter is less than the column max_hits.

Getting the first part to work isn't a problem, but the second part seems to be, as it still fetches rows when hit_counter is more than max_hits.

Here's my code:

$query = "SELECT * FROM links WHERE timestamp >= '$timestamp' AND hit_counter >= 'max_hits' AND overflow = 'NO'";
$result = mysql_query($query);
$size = mysql_num_rows($result);

That seems to return all the correct rows for timestamp, however still continues to select rows where hit_counter is more than max_hits (even though it shouldn't select any at all as >= is more than or equal to), while to inverse >= to <= returns no results, even though mathematically, it's valid.

I think perhaps I'm referencing max_hits as a value incorrectly, as I don't understand why it's working in the first place?

I know my question is a little iffy, so if you need any clarification please, do ask :).

Any answers/help would be greatly appreciated!

UPDATE:

I've updated the query as per the many great answers below, (removing the '' encasing max_hits and changing >= to <= but it still returns rows which are not meant to be included by the query.

Here's a pastebin to my full code: http://pastebin.com/V4vXJr1w

Here's a link to my table structure: https://docs.google.com/spreadsheet/ccc?key=0AoELUDjfbpSXdEUybkgwQmpxUnVCWlZOZnFJdzFaQmc&hl=en_GB

Hang on a second, what are you expecting hit_counter >= max_hits to return? You seem surprised that it's returns results where hit_counter is greater than max_hits...but that's exactly what you've asked. Sorry if I'm missing something, would you mind clarifying? — mal-wan
– mal-wan, Commented Sep 5, 2011 at 2:56
@mwan100: Changing >= to <= returns no results in this instance, at least >= was returning results, the reason why, and the problem is the fact that max_hits is encased in single quotes, i.e. interpreted as a string, as has been pointed out in the answers below :). — Avicinnian
– Avicinnian, Commented Sep 5, 2011 at 3:00
Can you give us a little more info about what rows are being returned? Do you get the same number of rows if you leave the hit_counter bit out entirely? — Alan Moore
– Alan Moore, Commented Sep 5, 2011 at 3:22
@Alan Moore: I get a different number of rows, but it continues to change. For example, even though all rows in the database had hit_counter values higher than max_hits, the first time I echoed $size it would return 1, the second 0, which then trips if($size == 0) and for some reason the script on a perpetual loop (keeps reloading the page). — Avicinnian
– Avicinnian, Commented Sep 5, 2011 at 3:33
ANother question, are max_hits and hit_counter both setup as int fields in the database structure? — Alan Moore
– Alan Moore, Commented Sep 5, 2011 at 3:38

Alan Moore · Accepted Answer · 2011-09-05 03:27:16Z

3

I think it will work if you remove quotes around 'max_hits':

$query = "SELECT * FROM links WHERE timestamp >= '".$timestamp."' AND hit_counter <= max_hits AND overflow = 'NO'";

the problem being that you are referring to the string 'max_hits' instead of the column value.

edited Sep 5, 2011 at 3:27

answered Sep 5, 2011 at 2:52

Alan Moore

6,5756 gold badges58 silver badges69 bronze badges

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

3 Comments

iCreateAwesome Over a year ago

$query = "SELECT * FROM links WHERE timestamp >= '$timestamp' AND hit_counter <= max_hits AND overflow = 'NO'";

Avicinnian Over a year ago

Still doesn't work :(. I've updated my OP with a little more information, I've been trying to get to the bottom of this infernal code (knocking kinks out) for like 3 hours, hopefully this'll be the final issue :). I've you'd like to take a look, it would be VERY much appreciated :), if not, thanks for your input anyway :)!

Alan Moore Over a year ago

I don't know if this will matter, but you might try wrapping the $timestamp -- I'll change my code to show what I mean.

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-09-05 02:53:00Z

1

$query = "SELECT * FROM links WHERE timestamp >= '$timestamp' AND hit_counter >= 'max_hits' AND overflow = 'NO'";

If max_hits is a column, you need to lose the quotes.

$query = "SELECT * FROM links WHERE timestamp >= '$timestamp' AND hit_counter >= max_hits AND overflow = 'NO'";

answered Sep 5, 2011 at 2:53

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

1 Comment

Avicinnian Over a year ago

Thanks for your input, but it still doesn't work (also >= should be <= :)). I've updated my OP if you'd like to take a look?

rbone · Accepted Answer · 2011-09-05 02:54:43Z

0

Two things look wrong to me at a glance.

If you want to compare one column against another you don't want to quote the column name, mysql just see's that as comparing your hit_counter column against the string 'max_hits'.
Also perhaps I'm misunderstanding you, but I think you've got one of your greater than signs backwards. It should roughly be:

SELECT * FROM links WHERE timestamp >= '$timestamp' AND hit_counter < max_hits AND overflow = 'NO'

answered Sep 5, 2011 at 2:54

rbone

934 bronze badges

1 Comment

Avicinnian Over a year ago

Yeah, that's what I'm looking for, but it still doesn't work :(. I've updated my OP if you'd like to take a look? Thanks for your input ;).

Collectives™ on Stack Overflow

MySQL SELECT query syntax - not working as expected?

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related