MySQL Select Query Within a Serialized Array

mysql select query within a serialized array

So you mean to use MySQL to search in a PHP array that has been serialized with the serialize command and stored in a database field? My first reaction would be: OMG. My second reaction would be: why? The sensible thing to do is either:

1. Retrieve the array into PHP, unserialize it and search in it
2. Forget about storing the data in MySQL as serialized and store it as a regular table and index it for fast search

I would choose the second option, but I don't know your context.

Of course, if you'd really want to, you could try something with SUBSTRING or another MySQL function and try to manipulate the field, but I don't see why you'd want to. It's cumbersome, and it would be an unnecessary ugly hack. On the other hand, it's a puzzle, and people here tend to like puzzles, so if you really want to then post the contents of your field and we can give it a shot.

PHP/Mysql: Searching serialized data for a string

SELECT * FROM table WHERE allData LIKE '%"xxxxxgu7@gmail.com"%' AND allData LIKE '%"ebdeb85d094cefd102b630fa9e69d9ca"%' LIMIT 1;;

This example should allow you to search for a specific HASH and senderEmail in the table and return that entry if a match of both fields exist in the serialized data. This would be useful to put into a view so that it can be retrieved quickly and easily, and you of course can see where you would want to change the email and hash in the query. In this example it will return that exact entry.

More information:

MySQL Like Function

Search inside serialize data with MYSQL

Search inside serialize data with MYSQL

Your query is formatted a little wrong. If you are trying to pass $id_serialize to your query, it needs to be formatted like this:

$id_serialize = $id;
$req = requette("SELECT id FROM table WHERE col LIKE '%" . $id_serialize . "%'");

Query data from within a string or a serialized array

No, you should normalize your table schema. Yes, you can even cope with the undefined number of values.

Make a separate table that will hold the relationship references.

table_languages

ref_id | language

---

Sample data would be:

user_id | language
-------------------
   1   | Language 1
   1   | Language 2
   1   | Language 3
   2   | Language 2

---

Sample query would be:

    SELECT *
      FROM users u
INNER JOIN languages l
        ON u.user_id = l.user_id
       AND l.language = 'language2'
     WHERE u.country = 'abc'
       AND u.city = 'def';

Retrieving a Specific Value From A Serialized Array MySQL

Create query, read data from SQL, unserialize() metaValue and access it like an array. You can also try to use regular expressions if you need to extract it inside mysql, but that's a not very good approach.

---

Related Topics