Display multiple SQL records using JS to parse a multi-dimensional array

javascript jquery arrays json mysqli

51 观看

1回复

25 作者的声誉

I want to use JS/JQuery/PHP/MySQLi to auto-generate an HTML table to fill a div on a page which also includes a div allowing the user to enter search criteria. Eg: Enter a range of dates, click a button to $.post those dates to a PHP file that queries a MySQLi table and returns a multi-dimensional array to JS which then parses that array as it builds the HTML.

So from the user's perspective he enters a start date and stop date and, elsewhere on the same page, he sees a list of all bookings that were made between those 2 dates.

For code management purposes I want to keep my JS script as a separate file referenced in the HTML page header and my PHP script as a separate file referenced from the JS script.

My JS script looks something like this:

$(document).ready(function(){
    var startDate = new Date (2017,11,25);
    var stopDate = new Date (2017,11,31);
    displayBookings = function(startDate, stopDate) {
        $.post("php/getBookings.php", {
        startDate: startDate, 
        stopDate: stopDate
        },
        function(data){
            html = "<table>";   
            var i, j, result = JSON.parse(data); 
            while (i=0, i <= result.length, i++) {
               while (j=0, j <= result[i].length, j++) {          
                    html+= "<tr><td>"+result[i][j]+"</td></tr>";
                }
            }
            html += "</table>";
            $('#bookings').html(html);
        });
    };
});

My php script ('getBookings.php') looks like this:

<?php
$startDate = date('Y-m-d',strtotime($_POST["startDate"]));
$stopDate = date('Y-m-d',strtotime($_POST["stopDate"]));
$con = mysqli_connect("localhost","userid","password","database");
$sql = "SELECT * FROM bookings WHERE date BETWEEN '$startDate' AND '$stopDate'";
$result = mysqli_query($con,$sql);
$rows = mysqli_num_rows($result);
for ($i = 0; $i < $rows; $i++) {
    $row = mysqli_fetch_array($result);
    $newArray[$i]['reference'] = $row['reference'];
    $newArray[$i]['name'] = $row['name'];
    $newArray[$i]['price'] = $row['price'];
}
mysqli_free_result($result);
mysqli_close($con);
echo json_encode($newArray);
?>

My HTML code includes:

<button onClick='displayBookings()'>Press</button>
<div id="bookings"></div>

But when I press the button I get:

Uncaught TypeError: Cannot read property 'length' of null

I'm a beginner so I know I've probably made some basic mistake. Can anyone help?

作者: JulesGru 的来源 发布者: 2017 年 12 月 27 日

回应 1


0

15798 作者的声誉

Your problem is likely while (i=0, i <= result.length should probably be: while (i=0, i < result.length, the same goes for j.

It probably would be better to use map or reduce:

$(document).ready(function () {
  var startDate = new Date(2017, 11, 25);
  var stopDate = new Date(2017, 11, 31);
  displayBookings = function (startDate, stopDate) {
    $.post("php/getBookings.php", {
      startDate: startDate,
      stopDate: stopDate
    },
      function (data) {
        console.log(JSON.stringify(data,undefined,2));
        html = "<table>" +
          data.map(
            row=>{
              row.map(
                item=>`<tr><td>${item}</td></tr>`
              ).join("");
            }
          ).join("") + "</table>";
        $('#bookings').html(html);
      });
  };
});

In case your PHP returns an object with properties as JSON then you could try the following:

$(document).ready(function () {
  var startDate = new Date(2017, 11, 25);
  var stopDate = new Date(2017, 11, 31);
  displayBookings = function (startDate, stopDate) {
    $.post("php/getBookings.php", {
      startDate: startDate,
      stopDate: stopDate
    },
      function (data) {
        console.log(JSON.stringify(data,undefined,2));
        html = "<table>" +
          Object.keys(data).map(
            key=>{
              Object.keys(data[key]).map(
                rowKey=>`<tr><td>${data[key][rowKey]}</td></tr>`
              ).join("");
            }
          ).join("") + "</table>";
        $('#bookings').html(html);
      });
  };
});

The problem with object is that there is no specification about the order, for example:

Object.keys({
  first: 1,
  second:2
});

Will usually produce ["first","second"] but there is no guarantee it will.

So it probably will be best to use the first code block but change your PHP to do:

$newArray = array()
for ($i = 0; $i < $rows; $i++) {
  $row = mysqli_fetch_array($result);
  $newArray[$i] = array(
    $row['reference'],
    $row['name'],
    $row['price']
  );
}

I assume that your original data.map is not a function was not caused by php not converting arrays correctly so with that PHP you can use the JavaScript as in the first code block (data.map and row.map)

作者: HMR 发布者: 2017 年 12 月 27 日
32x32