How to Search from Serialize Field in MySQL Database

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 . "%'");

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

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.

Mysql: get all distinct values from field serialized by PHP

You should unserialize your data

$data = 'a:3:{i:0;s:9:"Animation";i:1;s:9:"Adventure";i:2;s:5:"Drama";}';
$data = unserialize($data);
print_r($data);

and you will get

Array
(
    [0] => Animation
    [1] => Adventure
    [2] => Drama
)

If you need to search the entire table for "Drama" to decide which shows/movies to display, you could always use wildcards in your search

select * from table where column like '%Drama%'

but of course make sure to take appropriate database precautions.

---

Related Topics