Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

Lets say i have an array in my mysql row:

a:3:{i:1;a:3:{i:0;s:1:"1";i:1;s:1:"3";i:2;s:1:"5";}i:4;a:3:{i:0;s:2:"21";i:1;s:2:"25";i:2;s:2:"29";}i:5;a:1:{i:0;s:2:"33";}}

It looks like this:

Array
(
    [1] => Array
        (
            [0] => 1
            [1] => 3
            [2] => 5
        )

    [4] => Array
        (
            [0] => 21
            [1] => 25
            [2] => 29
        )

    [5] => Array
        (
            [0] => 33
        )

)

Now, i am passing an array through _GET and i want to print out all rows that contain same values both in my mysql and passed array. For example, if i pass this array:

    Array
    (
        [1] => Array
            (
                [0] => 5
            )
)

A result should be shown, because my passed array contains option 5. I tried to do it like this:

$pecul = serialize($array);
$q=mysql_query("SELECT id from table WHERE options like '%$pecul%'")or die(mysql_error());

but it only prints out results with identical arrays.

share|improve this question
1  
The LIKE operator compares strings. It has no notion of PHPs serialized blobs. And the short blob containing a 0:5 won't match unless the original serialized blob contained a single subarray containing only a 5 at array key 0. Think of a better way to store your data if you need to run queries on it. – mario Nov 6 '12 at 15:43

3 Answers

up vote 0 down vote accepted

You probably want to unserialize the data that's in your database first. Once it's in a PHP array you can perform a check for those options, so say:

if(in_array("5",$array)) {
  $q=mysql_query("SELECT id from table WHERE options=5")or die(mysql_error());
}

If you need to do a query for all of the options, you can do a loop like so:

foreach($array as $option) {
  $q=mysql_query("SELECT id from table WHERE options='$option'")or die(mysql_error());
}

But like mario said, you may want to think the options mechanism over and perhaps serialized data isn't what you need. This should hopefully work for you the way it is though.

share|improve this answer
But how can i unserialize my data before printing results..? I mean, i have to CHECK IT first, then do something with results, if values match.. – John Stockton Nov 6 '12 at 15:56
You would normally do your select command grabbing the serialized data first - Once you have that serialized data in your PHP code you unserialized that row. Example: $q="SELECT serialized_column FROM table ... Then store the result: $row=mysql_query($q); then create your unserialized array: $array=unserialize($row['serialized_column']); and now you can run your foreachloop on the array as printed above. – Ben Ashton Nov 6 '12 at 16:54
up vote 0 down vote

the like operator match only match same it does not understand for example you have dem in like than it will also show the demolish so

i think for that you need first unserlize and than find by php by either loop or in_array() function

what %like% do suppose this is table Persons and you used the query

SELECT * FROM Persons WHERE City LIKE '%tav%'

+-------------+-------------+
|   id        |  city       |
+-------------+-------------+
|   1         |   Sandnes   |        
+-------------+-------------+
|   2         |   Stavanger |
+-------------+-------------+

so the result will be 

+-------------+-------------+
|   id        |  city       |
+-------------+-------------+
|   2         |   Stavanger |  <----it has tav (s-tav-anger)
+-------------+-------------+
share|improve this answer
But i can't unserialize results before finding them (with 'where') – John Stockton Nov 6 '12 at 15:47
isn't the result of your example a:3:{i:1;a:3:{i:0;s:1:"1";i:1;s:1:"3";i:2;s:1:"5";}i:4;a:3:{i:0;s:2:"21";i:1;s:‌​2:"25";i:2;s:2:"29";}i:5;a:1:{i:0;s:2:"33";}} retrieved? That's serialized data and you must unserialize that before your where command. – Ben Ashton Nov 6 '12 at 15:51
@JohnStockton i dont think where will be helpful since it check exactly mathcing and you wont get since your colon have many data – NullPoiиteя Nov 6 '12 at 15:52
Yes, but it is serialized inside my database and i have to print out only those rows which contain my passed values. So, i can't unserialize my data which is inside my database before checking if it is corrcet or not. – John Stockton Nov 6 '12 at 15:54
@JohnStockton i know that but this is only option ....with serialize ..but i think you can do this by making another table and than join table and than find so that there will only one value that time you can either use %like% if you want identical or = – NullPoiиteя Nov 6 '12 at 15:59
up vote 0 down vote

IN() Check whether a value is within a set of values

mysql> SELECT 2 IN (0,3,5,7);
        -> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
        -> 1

SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');

View: IN MySql

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.