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

LVL 1
RationalRabbitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.