json data in mysql

Question

I have a mysql table called "Data",

+---------+------------------+------+-----+-------------------+----------------+

| Field   | Type             | Null | Key | Default           | Extra          |
+---------+------------------+------+-----+-------------------+----------------+

| id      | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| data    | text             | YES  |     | NULL              |                |
| created | timestamp        | NO   | MUL | CURRENT_TIMESTAMP |                |
+---------+------------------+------+-----+-------------------+----------------+

The field "data" has values like this:

606 | {"first_name":"JOHN","last_name":"SLIFKO","address":"123 main AVE","city":"LAKEWOOD","state":"OH","zip":"20190","home_phone":2165216359,"email":"john@gmail.com",} | 2012-12-04 16:37:23 |

So, it is saving the records in a JSON Format from a PHP Script that I have.

THIS IS THE THING:

How can I structure this table to make faster searchs or consults by every single field like doing searches or queries like:

SELECT * FROM Data WHERE first_name = john;

how can I do this???

Help please......

Answer 1

Yikes. Not a good design. About the best you could do is use the like keyword

Select * from Data Where data like '%"first_name":"JOHN"%'