Current time: 04-23-2014, 09:19 AM Hello There, Guest! (LoginRegister)

Post Reply 
MySQL WHERE syntax help?
06-29-2005, 05:03 AM
Post: #1
MySQL WHERE syntax help?
I know this is going to sound like a totally unoptimized query but I need to do something like:

SELECT * FROM Products WHERE % LIKE criteria

where % is a wildcard or some other entity denoting all fields in the specified table.

I cannot seem to find an answer to this one anywhere on the web.
Maybe thats because it's an idea so ridiculous in practice that no logical person would want to do it... but for the sake of extensible code in this instance... anybody have an answer?

("You're an idiot." Is an acceptable answer)

John
Find all posts by this user
Quote this message in a reply
06-29-2005, 06:13 AM
Post: #2
MySQL WHERE syntax help?
SELECT * FROM tb_name WHERE tb_column LIKE '%word%'

The * can be replaced with column names seperated by a comma. It's much better with tables with lots of columns to only select the ones you need. You should also do it if you only need one or two columns in your code. Doing so will speed up your queries and reduce resource usage.

The db_name should be the table you are looking to use.

The db_column should be the column you are hoping to match records with. If you need more than one, seperate them by the word AND
...WHERE first_name LIKE '%john%' AND last_name LIKE '%smith%'

LIKE is used for string matches. The % matches zero or more characters and I think it's either . (period) or _ (underscore) for matching one character only. You should be careful on how you use the %, and if you need to put it at the end and the beginning of string searches. You also need to be careful on case as it's case insensitive (by default). So in your PHP (guessing?), you might want to use strtolower($input_string) to convert input to lower case, and then in your SQL put
WHERE LOWER(first_name) LIKE '%john%'

You can just use =, <, > for numbers and they don't need to enclosed in ' (single quotes). Strings need to be enclosed in quotes, numbers don't.

Probably more information than you need at the minute though Smile
Visit this user's website Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump: