• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118
  • Last Modified:

jQuery Ajax JSON Return Single Variable Plus Multidimensional Array

I am retrieving data using Ajax, json. The database may return no results, one record, or multiple records. I need to know the number of rows when I receive the data. If only one row, it will simply fill the form. If multiple rows, the user is directed to a list in order to make a selection. The number of rows are stored in the $Rows variable from the query. In other words, what I need to know is how to send the $Rows variable back along with the multidimensional array.

Basically, after query, my PHP looks like this:

    $Rows = mysql_num_rows($Result);
    if($Rows == 1)
    {
        $P = mysql_fetch_assoc($Result);
        $Output = json_encode($P);
    }
    elseif($Rows > 1)
    {
        $x = 0;
        while($ProgRow = mysql_fetch_array($Result))
        {
            // Of course, I could add the $Row var here, but I would need
            // To add it to each row, which seems a little daffy
            $P[$x]['ID'] = $ProgRow['ID'];
            $P[$x]['ProgCode'] = $ProgRow['ProgCode'];
            $P[$x]['ProgName'] = $ProgRow['ProgName'];
            $x++;
        }
        $Output = json_encode($P);
    }
    else
    {
        $P = json_encode(0);
        $Output = $P;
    }
    echo($Output);

Open in new window


And here is the jQuery:
    $.ajax(
    {
       type: 'POST',
       url: 'scripts/UtilAjax.php',
       data: 'Sec=EditPrograms',
       dataType: 'json',
       success: function(data,status)
       {
          // ideally what needs to happen here is to be able to
          // return the $Rows variable as well, to determine how
          // To deal with the return data.
          console.log(status);
          console.log(data.ID+' '+data.ProgCode+' '+data.ProgName);
       }
    });

Open in new window

0
RationalRabbit
Asked:
RationalRabbit
  • 3
  • 2
1 Solution
 
Brian TaoSenior Business Solutions ConsultantCommented:
Change your json_encode rows to something like below:
$Output = json_encode(array("rows"=>$Rows, "results"=>$P));

Open in new window


and then in your js code you can access the number of rows and the results like below:
if (data.rows == 0){
  // do nothing, maybe show a message saying nothing was found
}else if (data.rows == 1){
  // populate the form
  console.log(data.results.ID+' '+data.results.ProgCode+' '+data.results.ProgName);
}else{
  // multiple rows
  // take the user to the list
}

Open in new window

1
 
RationalRabbitAuthor Commented:
The answer here is pretty straightforward. i don't know how I can add to it.
0
 
RationalRabbitAuthor Commented:
Someone else came up with some interestingly efficient code. Haven't fully tested - data.length seems to be counting ever character rather than every record. Not sure why that is, so it is a work in progress, but other than that, it seems to get the job done with half the lines of code.

PHP
$out = []; // start with an empty array
while($ProgRow = mysql_fetch_array($Result))
{
    $out[] = $ProgRow; // push onto the array
}
header('Content-type: application/json');
echo json_encode($out);

Open in new window


jQuery
$.post('scripts/UtilAjax.php',
{
   Sec: 'EditPrograms'
}).then(data =>
{
   console.log('Number of results:', data.length)
   switch(data.length)
   {
      case 0:
         // handle no records
      break;
      case 1:
         fillOutForm(data[0]) // use the first record
      break;
      default:
         showList(data)
   }
})

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Julian HansenCommented:
There is a lot you can do here to optimise your code.

First of all however you are using the MySQL library which has been deprecated and removed from later versions of PHP. You need to get off this library and use either MySQLi or PDO.

Then some suggestions about your code
a) you do not need to distinguish between the single record case and the multi record case
b) as you have discovered you do not need to index your array
c) you do not need to specify a JSON header
e) I would use mysql_fetch_all() rather than looping through the results

Rows will always be the length of your array.
Here is a simple version of your code

PHP (using MySQLi)
<?php
// USE MySQLi NOT MySQL library
$conn = mysqli_connect('host','user','password','db');
$query = "SELECT * FROM `targetTable`";
$Result = mysqli_query($conn, $query);

// USE THE mysqli_fetch_all to get all the results
$rows = mysqli_fetch_all($Result, MYSQLI_ASSOC);

// Output JSON and force script termination
die(json_encode($rows));

Open in new window


jQuery
  $(function() {
    $('#getRows').click(function(e) {
      e.preventDefault();
      data = {
        Sec: 'EditPrograms'
      }

      $.ajax(
      {
         type: 'POST',
         url: 't3061.php',
         data: data,
         dataType: 'json',
         success: function(data,status)
         {
        var totalRecs = data.length;
        switch(totalRecs) {
          case 0: 
            alert('No records returned')
          break;
          case 1:
            alert('1 Record returned')
          break;
          default:
            alert(totalRecs + ' records returned');
          break;
        }
         }
      });
    
    });
  });

Open in new window


Working sample here
2
 
Julian HansenCommented:
Update to the above - mysqli_fetch_all() is sometimes not available in which case you can do like so
$Result = mysqli_query($conn, $query);
$rows = array();
while($row = mysqli_fetch_object($Result)) {
    $rows[] = $row;
}
// Output JSON and force script termination
die(json_encode($rows));

Open in new window

0
 
RationalRabbitAuthor Commented:
Thanks, Julian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now