Display JSON datas in column with PHP then return selection in the same format

I am currently working on a ticket reservation script (With date, time of reservation then quantity available).

I am trying to communicate with an API which is supposed to send me the data from the db in JSON format so that I can interpret it and display in PHP in a column in which I should display the day in the header of the column, the hour in each cells and a default quantity ( In my example we start from 60/60 which will be decremented by 1 when selecting the user.

For the moment, I’m just trying to manage to automatically create a column for each date, with the values ​​to select and the value of the remaining quantity (Knowing that a selection decrements by 1) then return the result in JSON format to the API to update the database and save the selection by assigning the user’s IP to it.

I started a small script which retrieves the elements of the database in JSON format for the example, I think I have to create a foreach loop to create my columns but I’m stuck at the moment. Thank you for the leads or any help you think that you could bring me.

here’s a picture of what i am trying to do:

enter image description here

<?php
    try{
        $pdo=new PDO(
            'mysql:host=localhost;dbname=date_booking',
            'root','',
            array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
    } catch(PDOException $e){
        echo $e->getMessage();
    }

    $statement=$pdo->prepare("SELECT * FROM tbl_booking");
    $statement->execute();
    $datas = array();
    while($res=$statement->fetch(PDO::FETCH_ASSOC)) {
        $datas[]=$res;
    }
    $someArray = $datas; // Replace ... with your PHP Array
    foreach ($someArray as $key => $value) {
        echo '<pre>';
        echo $value["date_booking"]. ' | ' . $value["hour_booking"]. ' | ' . $value["nb_booking"];
    echo '</pre>'; 
    }

Answer

Start by selecting ONLY what you want from the table, then you can simply use fetchAll() to return the complete resultset as an array of arrays or objects, I used Objects in the below example.

It is then simple to make that into a JSON String to return to the caller using json_encode()

<?php
    try{
        $pdo=new PDO(
            'mysql:host=localhost;dbname=date_booking',
            'root','',
            array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
    } catch(PDOException $e){
        echo $e->getMessage();
    }

    $statement=$pdo->prepare("SELECT date_booking, hour_booking, nb_booking 
                                FROM tbl_booking");
    $statement->execute();
    $rows = $statement->fetchAll(PDO::FETCH_OBJ);
    
    echo json_encode($rows);

Then in you javascript you have an array of objects that you can place into your page however you want.