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

Question

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

Good code indentation would help us read the code and more importantly it will help you debug your code Take a quick look at a coding standard for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — RiggsFolly
– RiggsFolly, Commented Nov 1, 2021 at 16:16

RiggsFolly · Accepted Answer · 2021-11-01 16:35:23Z

1

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.

edited Nov 1, 2021 at 16:35

answered Nov 1, 2021 at 16:29

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Devinci code Over a year ago

This is a good advice, thank you

RiggsFolly Over a year ago

Alternatively, you could get the PHP code to build the HTML you want to show on the page and just return a string containing that HTML. You can then just replace a block of code in the page, say everything within a <div id="replaceMe"></div>

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related