How to send PHP data from database back to JQuery and insert to a Table

Question

As the title says, I'm trying to display data from a database based on user input and display it on a HTML table. In short I want the user input to filter the query request to the database, and then display the result in a table format.

My issue is that I'm not exactly sure how to return the query result back to JQuery and then transform that data into a table in order to display it in HTML.

This has been my approach so far but it doesn't work. I've already been successful in doing this with regular Javascript. But I want to try and use JQuery for this particular problem as it simplifies the code much more.

JQuery:

 var ourObj = {};

  ourObj.data = "Ranges";
  ourObj.arPoints = [{"SID":TABLID},{"T1": T1,"T2": T2},{"H1": H1,"H2": H2},{"P1": P1,"P2": P2},{"C1": C1,"C2": C2},
                     {"Y1": Y1,"Y2": Y2},{"M1": M1,"M2": M2},{"D1": D1,"D2": D2},{"m1": m1,"m2": m2}];

  $.ajax({
    type:"post",
    url:"GetQuery.php",
    data:{"data" : JSON.stringify(ourObj)},
    success: function(response){  

      $('SensorData').html(response);

    }
  });

PHP

<?php
if (isset($_POST["data"])) {

    // Decode our JSON into PHP objects we can use
    $data = json_decode($_POST["data"]);

    $id = $data->arPoints[0]->SID;

    $T1 = $data->arPoints[1]->T1;
    $T2 = $data->arPoints[1]->T2;
    $H1 = $data->arPoints[2]->H1;
    $H2 = $data->arPoints[2]->H2;
    $P1 = $data->arPoints[3]->P1;
    $P2 = $data->arPoints[3]->P2;
    $C1 = $data->arPoints[4]->C1;
    $C2 = $data->arPoints[4]->C2;

    $Y1 = $data->arPoints[5]->Y1;
    $Y2 = $data->arPoints[5]->Y2;
    $M1 = $data->arPoints[6]->M1;
    $M2 = $data->arPoints[6]->M2;
    $D1 = $data->arPoints[7]->D1;
    $D2 = $data->arPoints[7]->D2;
    $m1 = $data->arPoints[8]->m1;
    $m2 = $data->arPoints[8]->m2;

    // echo "Sensore ID: " . $id;
    // echo "Temperature: " . $T1 . " : " . $T2;
    // echo "Humidity: " . $H1 . " : " . $H2;
    // echo "Pressure: " . $P1 . " : " . $P2;
    // echo "CO: " . $C1 . " : " . $C2;

    $conn = mysqli_connect('localhost', 'root', '', 'sensors');

    if(empty($Y1) || empty($Y2) || empty($M1) || empty($M2) || empty($D1) || empty($D2) || empty($m1) || empty($m2)){

        $query = "SELECT * FROM sensors, sensorsdata WHERE sensors.SensorID = sensorsdata.SensorID 
        AND sensorsdata.SensorID = '$id' 
        AND sensorsdata.Temperature BETWEEN $T1 AND $T2
        AND sensorsdata.Humidity BETWEEN $H1 AND $H2
        AND sensorsdata.Air_Pressure BETWEEN $P1 AND $P2
        AND sensorsdata.Carbon_Monoxide BETWEEN $C1 AND $C2
        order by sensorsdata.Date Desc"; 
    }
    else{
        $time1= mktime(12,$m1,0,$M1,$D1,$Y1);
        $time1= date("Y-m-d h:i:s", $time1);

        $time2= mktime(12,$m2,0,$M2,$D2,$Y2);
        $time2= date("Y-m-d h:i:s", $time2);

        $query = "SELECT * FROM sensors, sensorsdata WHERE sensors.SensorID = sensorsdata.SensorID 
        AND sensorsdata.SensorID = '$id' 
        AND sensorsdata.Temperature BETWEEN $T1 AND $T2
        AND sensorsdata.Humidity BETWEEN $H1 AND $H2
        AND sensorsdata.Air_Pressure BETWEEN $P1 AND $P2
        AND sensorsdata.Carbon_Monoxide BETWEEN $C1 AND $C2
        AND sensorsdata.Date BETWEEN '$time1' AND '$time2'
        order by sensorsdata.Date Desc"; 
    }

    // Get Result
    $result = mysqli_query($conn, $query);

    // Fetch Data
    while ($row = mysql_fetch_array($result))
    {
       ?>
          <tr><td><?php echo $row['Temperature']?></td>
          <td><?php echo $row['Humidity']?></td>
          <td><?php echo $row['Air_Pressure']?></td>
          <td><?php echo $row['Carbon_Monoxide']?></td>
          <td><?php echo $row['Date']?></td></tr>;
        <?php   
    }

    mysqli_close($conn);
}
?>

HTML table

  <table class="table">
                <thead>
                    <tr>
                        <th>Temperature</th>
                        <th>Humidity</th>
                        <th>Air Pressure</th>
                        <th>Carbon Monoxide</th>
                        <th>Date</th>
                    </tr>
                </thead>
                <tbody id="SensorData">

                </tbody>
            </table>

Im not entirely sure what you’re asking. Do you want a better method or is the code above not working properly? — Luke.T
– Luke.T, Commented May 17, 2020 at 10:39
I've edited the problem description for better understanding. But in short I simply want to send a query result from my PHP file back to JQuery and then insert the data in a table. — Ruben Jose Diaz
– Ruben Jose Diaz, Commented May 17, 2020 at 10:50
Can you try console.logging your response or see if the data from your php endpoint is showing in the network tab? Also $(‘SensorData’) should be $(‘#SensorData’) — Luke.T
– Luke.T, Commented May 17, 2020 at 11:04
Ah it's always the small things, nevertheless I managed to solve the problem. Thanks again for that small typo, I always miss those. — Ruben Jose Diaz
– Ruben Jose Diaz, Commented May 17, 2020 at 11:42

Ruben Jose Diaz · Accepted Answer · 2020-05-17 11:43:30Z

Wow ok managed to solve the issue, it's always the small things.

Issue #1: (Thanks Luke T.) a simple issue of changing $(‘SensorData’) to $(‘#SensorData’)

Issue #2: the problem was in how I was going through each row of the data in the PHP file, changed it from

while ($row = mysql_fetch_array($result))
    {
        ?>
        <tr><td><?php echo $row['Temperature']?></td>
        <td><?php echo $row['Humidity']?></td>
        <td><?php echo $row['Air_Pressure']?></td>
        <td><?php echo $row['Carbon_Monoxide']?></td>
        <td><?php echo $row['Date']?></td></tr>;
      <?php   
    }

to

foreach ($result as $row)
    {
        ?>
        <tr><td><?php echo $row['Temperature']?></td>
        <td><?php echo $row['Humidity']?></td>
        <td><?php echo $row['Air_Pressure']?></td>
        <td><?php echo $row['Carbon_Monoxide']?></td>
        <td><?php echo $row['Date']?></td></tr>;
      <?php   
    }

The rest pretty much stayed the same, it now displays the data requested with no issues in the table.

Collectives™ on Stack Overflow

How to send PHP data from database back to JQuery and insert to a Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related