开发者

WHERE IN SQL condition problem

开发者 https://www.devze.com 2023-03-12 19:18 出处:网络
I have this query: \"SELECT * FROM i开发者_StackOverflow中文版nformations WHERE \". $id .\" IN (ids)\"

I have this query:

"SELECT * FROM i开发者_StackOverflow中文版nformations WHERE ". $id ." IN (ids)"

It only works if $id is the first value from ids... in ids values are "1,2,3,4,5".

Is there a way for it to work with the rest of the ids?


Would this work for you?

"SELECT *
 FROM Informations
 WHERE ids LIKE \"" . $id . ", %\" -- try to match against the first value in ids
 OR ids LIKE \"%," . $id . ",%\" -- try to match against a value in ids that is neither the first nor the last value
 OR ids LIKE \"%," .$id . "\"  -- try to match against the last value found in ids"


If ids is a field containing comma-delimited values, then your query is like:

SELECT * FROM `informations` WHERE 3 IN ("1,2,3,4,5")

Instead of what it should be:

SELECT * FROM `informations` WHERE 3 IN (1,2,3,4,5)

There is no automatic tokenisation (splitting on ,) performed; the one value of ids is not automatically converted into a list for you such that IN can work.

Unfortunately your table design has been your undoing here. Can you split the IDs into a separate table using the principle of database normalisation?

Then your query might look like:

SELECT * FROM `informations` WHERE 3 IN (
   SELECT `id`
     FROM `ids`
    WHERE `informations`.`id` = `ids`.`information_id`
)

BTW, "information" is a non-countable noun and, as such, "informations" is wrong.


Update (thanks for the idea, a1ex07!)

Although this is hackery and I still suggest fixing your table layout, I'll be kind and suggest a quick fix.

Willempie was close with:

$query = 'SELECT *
            FROM `informations`
           WHERE `ids` LIKE "%' . $id . '%"';

Unfortunately, a wildcard match isn't quite powerful enough. Consider if ids is like "1,6,9,12,35,4" and $id is like 3. You get a false positive. The LIKE statement needs to be aware of the commas.

You can add multiple cases:

$query = 'SELECT *
            FROM `informations`
           WHERE `ids` LIKE "%,' . $id . ',%"
              OR `ids` LIKE "%,' . $id . '"
              OR `ids` LIKE "' . $id . ',%"';

Or, for brevity, you can work around this with regular expressions:

$query = 'SELECT *
            FROM `informations`
           WHERE `ids` REGEXP "(^|,)' . $id . '(,|$)"';

For any $id you wish to find, before it must be the start of ids (^) or a comma; after it must be a comma or the end of ids ($). This ensures that $id must be found as a whole, comma-delimited token.

It's a little like "Whole Word Only" in word processor searches, but with commas separating "words" instead of spaces.


Update 2

Another way uses FIND_IN_SET, which performs a search within a comma-delimited string:

$query = 'SELECT *
            FROM `informations`
           WHERE FIND_IN_SET("' . $id . '", `ids`)';


Your query is technically correct but the values for 'ids' are not.

You should enclose the values of ids within single quotes. If I were to write the code without using ids, it would be like this:

"SELECT * FROM informations WHERE ". $id ." IN ('1','2','3','4','5')"

More info on this rule here: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in


I'm not sure what you are trying to achieve. If ids is a column in informations your code is just a weird way to express "SELECT * FROM informations WHERE ids = ". $id "; If it is a string, I don't see why you need WHERE at all : expression $id in (1,2,3,4,5) is constant, it doesn't require interaction with database; in any case you either grab all rows from informations or none.

UPDATE
Another suggestion :maybe ids is a string field in informations that contains "1,2,3,4,5". In this case you cannot get expected results by using WHERE ... IN. You need to use REGEXP to check if string contains your number.


It has to be column name then IN (comma separated values here).

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)


You did an error in sql syntax.

This is the correct syntax

"SELECT * FROM informations WHERE ids IN (". $id .")";
0

精彩评论

暂无评论...
验证码 换一张
取 消