How to query MySql table with JSON columns from PHP

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:

<?php
// Replace these with your actual database credentials
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_username';
$password = 'your_password';

try {
    // Establish the database connection using PDO
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Prepare the query
    $query = "SELECT * FROM my_table WHERE json_data->'$.key' = :value"; // Replace key and value with your actual JSON key-value pair

    // Bind the parameters (if needed)
    $key = 'example_key';
    $value = 'example_value';

    // Execute the query
    $stmt = $conn->prepare($query);
    $stmt->bindParam(':value', $value);
    $stmt->execute();

    // Process the results
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Decode the JSON data to work with it as a PHP array or object
    foreach ($results as &$row) {
        $row['json_data'] = json_decode($row['json_data'], true); // true to convert to associative array, false for object
    }

    // Now $results contains the rows, and each 'json_data' key holds the JSON data as an array

    // Example: Loop through the results and print the JSON data
    foreach ($results as $row) {
        echo "ID: " . $row['id'] . "<br>";
        echo "JSON Data:<pre>" . print_r($row['json_data'], true) . "</pre><br>";
    }

} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Refer to this article on how you can implement other queries usin json data: How to query JSON data in MySQL