MySQL Select Where


#1

The WHERE function seems broken. For example, the following code returns all records, when I only want to see those where ‘status’ (a TINYINT) is zero thru five:

SELECT * FROM jobs
WHERE ‘status’ <6
ORDER BY date_in ASC

What am I doing wrong?

(actually, it’s only six that I don’t want. But, I get the same result -all records- with =4, =5, =6, <>6, !=6, etc.)

I’ve found lots of ways to get a syntax error, but isn’t this right?

Further, "WHERE ‘client’ = also returns all records, and ‘client’ is a text field. What am I doing wrong?


#2

Remove the quotes from around the column name. Try this:

SELECT * from jobs WHERE status < 6 ORDER BY date_in ASC ---------------
Simon Jessey
Keystone Websites | si-blog


#3

SELECT * FROM jobs
WHERE status <6
ORDER BY date_in ASC
LIMIT 0, 100

Very good; this works. Thank you!

Okay, now…
Why is the single-quote required around some field names sometimes, but not other times?
The above snippet was generated by DH’s phpMyAdmin, with me inserting the WHERE line. Okay, maybe “why” gets into metaphysics, but is there a rule about why 2 of the 3 names have quotes but it fails if the 3rd one does, so I don’t have to ask you 20 times for 20 different queries?


#4

Well speaking for myself, I’ve never put single quotes around field names. Do not confuse the backtick ` with the single quote, by the way. Column names that may be reserved words, like DATE for example, should be surrounded with backticks, not single quotes.


Simon Jessey
Keystone Websites | si-blog


#5

With the font in use (DH/phpMyAdmin/browser/WinXP, who knows?), I see absolutely no difference between the label-symbol that phpMyAdmin puts in the SQL edit window, and the mark I get when I press the single-quote key in the same edit window. They all appear to be a backward-leaning single quote; the ones that phpMyAdmin enters, and both the one before the label and the one after it that I type in. How can I avoid confusing two things if they look the same?

Certainly, the SQL server doesn’t seem to see any difference between it’s character and the ones I type.


#6

Backticks (left of number 1 on a US keyboard) go around column and database names, and singles quotes go around column values (if they are strings). The difference should be obvious in a monospaced font like Courier. For example:

SELECT `name` FROM `employees` WHERE `id` < 5 AND `dept` = 'Main despatch' ORDER BY `id` DESC;---------------
Simon Jessey
Keystone Websites | si-blog