0

For example, when a table has a record column named 'product' that contain value such as: 'Laptop, Desktop, Case'. How can I validate these 3 values that break down with a comma against two PHP variables value with $var1='Laptop' and $var2='Desktop' ? So that this row can be found! However, the two variables could be passed in the order of 'Desktop', 'Laptop' as well. Meanwhile, the column could have pattern of 'Case, Desktop, Laptop'. I wonder if there is a solution in MySQL for this kind of scenario that somehow, pick up each element like PHP could and match them with each var individually.

benomatis
  • 5,536
  • 7
  • 36
  • 59
ey dee ey em
  • 7,991
  • 14
  • 65
  • 121
  • i don't think this is possible in mysql, but if you wanted to use PHP, you could use [explode](http://php.net/manual/en/function.explode.php) for this... – benomatis Oct 11 '14 at 14:19
  • the 'find_in_set' function may be useful: [436591/how-can-i-search-within-a-table-of-comma-separated-values](http://stackoverflow.com/questions/4436591/how-can-i-search-within-a-table-of-comma-separated-values). – Ryan Vincent Oct 11 '14 at 14:19
  • Do not wonder any longer. Everything comparison related Mysql has to offer is also documented in the mysql manual. http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html - http://dev.mysql.com/doc/refman/5.6/en/string-functions.html – hakre Oct 11 '14 at 15:06

2 Answers2

0

Without knowing anything about your table structure this is a quick example of what you can do. 

SELECT * FROM table WHERE $var1 IN (SELECT product FROM table WHERE something = somethingelse) AND  $var2 IN (SELECT product FROM table WHERE something = somethingelse)
Brian
  • 903
  • 1
  • 7
  • 13
  • how will this get the OP the values separated by a comma into separate variables? – benomatis Oct 11 '14 at 14:20
  • the product field is already in csv format from what the op said. So when it returns csv the original query checks if var1 is in that csv. Then it checks if var2 is in that csv – Brian Oct 11 '14 at 14:21
  • this makes no sense: if you check 'Laptop' against 'Laptop, Desktop, Case' with equality, how will that every be true? – benomatis Oct 11 '14 at 14:22
  • Its not checking equality. IN is not the same as = – Brian Oct 11 '14 at 14:23
  • I use this method all the time. It works fine. – Brian Oct 11 '14 at 14:24
  • ...but `something = somethingelse` does... and so your query will never work... you're probably using it without looking inside a specific value, not among many values, not the same – benomatis Oct 11 '14 at 14:25
  • that is obviously because he only wants one row to get the product list from. Like I said I dont know his table structure. I dont know his field names. I used those as an example. It would be replaced with something to get that certain row. That certain row has the field with the csv. – Brian Oct 11 '14 at 14:28
  • @Brian For this piece `$var1 IN (SELECT product FROM table WHERE something = somethingelse)` if there is a IN matched, that means this phrase will equivalent of a TRUE, is that correct? – ey dee ey em Oct 11 '14 at 14:31
  • yes. If var1 is Laptop and the subquery returns Laptop, Desktop, Case it will be true – Brian Oct 11 '14 at 14:32
  • @Brian does SQL can ever break down comma seperated text to validate each seperate string part with a select you wrote? – ey dee ey em Oct 11 '14 at 14:32
  • so what is `something` or `somethingelse` in this query going out from the example provided by the OP? – benomatis Oct 11 '14 at 14:34
  • Not sure I know what you mean. Are you asking if you can use a comma seperated value as the variable to check against the csv from the product field? – Brian Oct 11 '14 at 14:35
  • @webeno THe something and somethingelse are column names and what after it could be the variables I think :) – ey dee ey em Oct 11 '14 at 14:35
  • sorry, this makes no sense at all to me... – benomatis Oct 11 '14 at 14:36
  • Yes see Chen understands. Obviously theres more than one row. There has to be some way to determine which row you are reading the products field from. – Brian Oct 11 '14 at 14:37
  • @Brian Its ok, I think this solution is good enough atm :) THanks! – ey dee ey em Oct 11 '14 at 14:37
  • This answer simply doesn't answer the question, either one of them is wrong, sorry. @boulder_02's answer is the one that actually answers this properly, to my view. – benomatis Oct 11 '14 at 14:39
  • @Brian Comes out, seem boulder_02 will also work. I am pretty undecided in this case. I will get back to you all once I impliment into a actual test run! THanks – ey dee ey em Oct 11 '14 at 14:48
  • lol webeno just because you dont understand something doesnt mean it doesnt work. This method works perfectly fine. If another method works too, great. But don't try to make someone else as ignorant as you just because you dont understand it. – Brian Oct 11 '14 at 14:49
  • 1
    And the LIKE method will only work if none of the words are similar. If you have Desktop and Desktop1 and DesktopComputer, all of them will match to %Desktop% – Brian Oct 11 '14 at 14:51
  • @Brian That is really a good point hum... really interesting! – ey dee ey em Oct 11 '14 at 15:14
0

As I understood, you want the data to be found, if the column 'product' contains 'Laptop' or 'Desktop'. Write this with the LIKE operator in your query:

"SELECT * FROM table WHERE `product` LIKE '%Desktop%' OR `product` LIKE '%Laptop%'"

If you pass the variables it would be:

"SELECT * FROM table WHERE `product` LIKE '%$var1%' OR `product` LIKE '%$var2%'"

Make sure to use the % sign before and after the searched string, so that it will match even if the keyword is anwhere inside the product content.

boulder_02
  • 301
  • 1
  • 6
  • But this also involve possibility of that var 1 could be laptop and var2 could be desktop, which is the reverse, while this query will not able to match... Await your revised answer :) – ey dee ey em Oct 11 '14 at 14:29
  • It will match. You defined an OR switch. If condition 1 is false, it will go on with condirion 2. One of them will be true, so the product will be selected – boulder_02 Oct 11 '14 at 14:32
  • I just read, that you want match, if both variables are in the product description. In that case you have to change OR against AND. It's irrelevant, which keyword is fed into the query as first. – boulder_02 Oct 11 '14 at 14:44
  • Hum... interesting. Both you guys solution seem could work. I will actually implimented and then back for the mark on answer! Thanks :) – ey dee ey em Oct 11 '14 at 14:47
  • This will not work if you are trying to match a word that is contained in multiple words. If you are checking for Desktop and yhe list includes Desktop, Desktop1, Dektop2, etc they will all match %Desktop% – Brian Oct 11 '14 at 14:52