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>'; 
    }

CodePudding user response：

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.