Blog

Details
Category: Databases

JSON format is becoming very popular for exchanging data between systems. Latest versions of MySQL supports json data types and here are some samples on how you can implement queries:

 

Assuming you have a table named users with a JSON column named data, and the data in the data column looks like this:

id name data
1 John {"age": 30, "email": "This email address is being protected from spambots. You need JavaScript enabled to view it."}
2 Jane {"age": 25, "email": "This email address is being protected from spambots. You need JavaScript enabled to view it."}
3 Michael {"age": 35, "email": "This email address is being protected from spambots. You need JavaScript enabled to view it."}

 

 

Select all rows where a specific key exists in the JSON data:

SELECT * FROM users WHERE JSON_CONTAINS(data, '{"email": "This email address is being protected from spambots. You need JavaScript enabled to view it."}');

This query will return the row with id 1, as it contains the specified email address in the JSON data.

 

Select all rows where a specific key exists and its value matches:

Details
Category: PHP Software Development

To query a MySQL table with JSON columns from PHP, you can use the PDO (PHP Data Objects) extension, which provides a consistent interface to work with various databases, including MySQL. Here's an example of how to do it:

Assuming you have a MySQL table named my_table with a JSON column named json_data, and you have already established a connection to the database using PDO, you can follow these steps:

 

Prepare the query:

Construct a SQL query that includes the JSON column and any other conditions you need.

Execute the query:

Execute the prepared statement and fetch the results.

Process the results:

Since the JSON column data will be returned as a JSON-encoded string, you'll need to decode it to work with it as a PHP array or object.

PHP code example: