PHP arrays with Ajax - Proper way to get the data?

Question

I'm a complete ajax/php idiot - lets get that out of the way right now; but I've gotten to a point where I need some help. I've been following various tutorials here and there and kind of putting together a report.

Ultimately I have a table that contains all the data I need. I can get that data and display it appropriately in a table without issue. I'm trying to add 'drop-down' boxes that will allow filtering based on the criteria (there is a column called 'verticals' in this table - so a dropdown will contain all the different 'verticals' from the database column - and filter the table based on the selection of the vertical)

I have the following front-end snippet called "client.php" that the client loads to get a report:

Client.php:

<head>
 <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script> 
 <link rel="stylesheet" type="text/css" href="css/mediacallreport.css">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Generic Title</title>
</head>



<body>
  <h2> Generic Reports </h2>
  <h3>Report Formatting: </h3>
  <div id="instruction">Select how you would like the data selected using the dropdowns below</div>

  <!--DROPDOWNS-->
 Vertical - 
<select name="station" id="station">
    <option value="all">All</option>
    <script id="source" language="javascript" type="text/javascript">
      $(function () 
      {
        $.ajax({                                      
          url: 'api.php',   //the script to call to get data          
          data: "",   //you can insert url argumnets here to pass to api.php
          dataType: 'json',   //data format      
          success: function(verticals) //on recieve of reply
          {
          for (var i in verticals)
          {
            var vertical = verticals[i];
            var verticalID = verticals[0];

        $('station').append("<option value=\""+verticalID+"\">"+verticalID+"</option>");
                }

          } 
        });
      });
    </script>
</select>

 <!--TABLE BEGINNING - TABLE HEADER ROW--> 
<table id="output">
<tr>
<th>ID</th>
<th>Station_Network</th>
<th>Vertical</th>
<th>Creative</th>
<th>Tolls</th>
<th>States</th>
<th>Date Range</th>
<th>Week</th>
<th>Ordered</th>
<th>Credits</th>
<th>Credits Totals</th>
<th>Not Used</th>
<th>Cleared</th>
<th>Total Uniques</th>
<th>Cleared Each Unique</th>
<th>Total Unique Connect</th>
<th>Cleared Each Unique Connect</th>
<th>Unique Connect 8am - 8pm</th>
<th>Cleared Unique 8am - 8pm</th>
<th>Calls over 10 Min</th>
<th>Calls over 10 Min %</th>
</tr>

<!--JAVASCRIPT TO GET INFORMATION FROM DB, ASSIGN VARIABLES AND PUT INTO TABLE ROWS-->
  <script id="source" language="javascript" type="text/javascript">
  $(function () 
  {
    //-----------------------------------------------------------------------
    // 2) SEND HTTP REQUEST WITH AJAX
    //-----------------------------------------------------------------------
    $.ajax({                                      
      url: 'api.php',   //the script to call to get data          
      data: "",   //you can insert url argumnets here to pass to api.php
                        //for example "id=5&parent=6"
      dataType: 'json',   //data format      
      success: function(rows) //on recieve of reply
      {
      for (var i in rows)
      {
        var row = rows[i];
        var id = row[0];              //get id
        var station_network = row[1];       //get name
        var vertical = row[2];  //get vertical
        var creative = row[3]; //get creative
        var tolls= row[4];  //get tolls
        var states= row[5];  //get states
        var date_range= row[6];  //get date_range
        var week= row[7];  //get week
        var ordered= row[8];  //get ordered
        var credits= row[9];  //get credits
        var credit_totals= row[10];  //get credit_totals
        var not_used= row[11];
        var cleared= row[12];
        var total_uniques= row[13];
        var cleared_each_unique= row[14];
        var total_unique_connect= row[15];
        var cleared_each_unique_connect= row[16];
        var unique_connect_8am_to_8pm= row[17];
        var cleared_each_8am_to_8pm= row[18];
        var calls_over_10= row[19];
        var calls_over_10_pct= row[20];
        //--------------------------------------------------------------------
        // DISPLAY THE CONTENT
        //--------------------------------------------------------------------
        //TABLES (ALTERNATING ROWS)
        if (id % 2 == 0){
        $('#output').append("<tr id=\"evenrow\"> <td>"+id+"</td><td>"+station_network+"</td><td>"+vertical+"</td><td>"+creative+"</td><td>"+tolls+"</td><td>"+states+"</td><td>"+date_range+"</td><td>"+week+"</td><td>"+ordered+"</td><td>"+credits+"</td><td>"+credit_totals+"</td><td>"+not_used+"</td><td>"+cleared+"</td><td>"+total_uniques+"</td><td>"+cleared_each_unique+"</td><td>"+total_unique_connect+"</td><td>"+cleared_each_unique_connect+"</td><td>"+unique_connect_8am_to_8pm+"</td><td>"+cleared_each_8am_to_8pm+"</td><td>"+calls_over_10+"</td><td>"+calls_over_10_pct+"</td></tr>");
        } else {
        $('#output').append("<tr id=\"oddrow\"> <td>"+id+"</td><td>"+station_network+"</td><td>"+vertical+"</td><td>"+creative+"</td><td>"+tolls+"</td><td>"+states+"</td><td>"+date_range+"</td><td>"+week+"</td><td>"+ordered+"</td><td>"+credits+"</td><td>"+credit_totals+"</td><td>"+not_used+"</td><td>"+cleared+"</td><td>"+total_uniques+"</td><td>"+cleared_each_unique+"</td><td>"+total_unique_connect+"</td><td>"+cleared_each_unique_connect+"</td><td>"+unique_connect_8am_to_8pm+"</td><td>"+cleared_each_8am_to_8pm+"</td><td>"+calls_over_10+"</td><td>"+calls_over_10_pct+"</td></tr>");
        }

        }

      } 
    });
  });
  </script>

</body>
</html>

That talks to my api.php api.php:

<?php 
  //--------------------------------------------------------------------------
  // Connect to DB
  //--------------------------------------------------------------------------
  include 'DB.php';
  $con = mysql_connect($host,$user,$pass) ;
  $dbs = mysql_select_db($databaseName, $con);
  //--------------------------------------------------------------------------
  // 2) Query database for data
  //--------------------------------------------------------------------------
  //Rows of data in media-analysis
  $result = mysql_query("SELECT * FROM $tableName");          //query
  $data = array();
  while ($row = mysql_fetch_row($result) )
  {
  $data[] = $row;
  }



  //Dropdown for Verticals
    $verticalSql = "SELECT VERTICAL FROM media_analysis GROUP BY VERTICAL";
    $verticalResult = mysql_query($verticalSql);
    $verticalData = array();
    while ($verticalRow = mysql_fetch_row($verticalResult)){
        $verticalData[] = $verticalRow;
    }

    $finalarray = array ('rowdata' => $data, 'verticaldata' => $verticalData);
  //--------------------------------------------------------------------------
  // 3) echo result as json 
  //--------------------------------------------------------------------------
  echo json_encode($finalarray);
?>

In Firebug I can see the data coming across and it looks something like this:

{"rowdata":[["1","canceled","canceled","canceled","canceled","canceled","03\/18\/2013-03-24\/2013","12","canceled","0","","","0.00","0","0.00","0","0.00","0","0.00","0","0.00"],["2","Station B","Vertical B","DEBIT","800-555-5555","CA","03\/18\/2013-03-24\/2013","12","$813.00","0","","","813.00","8","101.62","5","162.60","3","271.00","2","40.00"]],"verticaldata":[["canceled"],["Vertical B"]]}

Before I started including the 'dropdowns', I just had a simple json_encode ($data); and it would work fine and the table displayed exactly how I wanted it to. Since I've added another array to be passing, this is when things got crazy and I'm completely lost.

I know my coding is probably very poor, but I'm just trying to get a handle on this. All help appreciated.

Steve · Accepted Answer · 2013-04-01 21:13:49Z

4

The problem is that the code in your success handler expects an array to be returned:

success: function(verticals) //on recieve of reply
{
    for (var i in verticals)
    {
        var vertical = verticals[i];
        var verticalID = verticals[0];

        $('station').append("<option value=\""+verticalID+"\">"+verticalID+"</option>");
    }

}

However, as you showed in your example, verticals looks like this:

{"rowdata":[[...],[...]],"verticaldata":[["canceled"],["Vertical B"]]}

As you can see, verticals is an object, that contains two other objects (rowdata and verticaldata).

To make your current success handler work again, you first need to extract verticaldata from the AJAX response. Simply changing the code to this should work:

success: function(response) //on recieve of reply
{
    var verticals = response.verticaldata;
    for (var i in verticals)
    {
        var vertical = verticals[i];
        var verticalID = verticals[0];

        $('station').append("<option value=\""+verticalID+"\">"+verticalID+"</option>");
    }
}

answered Apr 1, 2013 at 21:13

Steve

8,6587 gold badges42 silver badges54 bronze badges

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

1 Comment

Hanny Over a year ago

This will do it! Thanks a ton. I'm still a noob, but I'm learning and your explanation was exactly what I was hoping for! Thanks a ton!

pocesar · Accepted Answer · 2013-04-01 21:29:05Z

2

since you are using PHP and jQuery, you can use my library for that. Out-of-the-box dealing with any data that comes from PHP to Javascript, and vice-versa ;)

See here http://phery-php-ajax.net

in your case, it would be like this

<?php
   include('Phery.php');

   Phery::instance()->set(array(
     'verticals' => function(data){
        $r = new PheryResponse;

        $verticalResult = mysql_query("SELECT VERTICAL FROM media_analysis GROUP BY VERTICAL");

        $r->jquery('.station'); // Select it for subsequent chaining in the loop
        $i = 0;
        while ($verticalRow = mysql_fetch_row($verticalResult)){
          $r->append('<option value="'.($i++).'">'.$verticalRow['VERTICAL'].'</option>'); // What is VERTICAL? Is that query the real one?
        }
        // every "append" call will happen to .station jquery selector automatically
        return $r;
     }
   ))->process();
?>

in your javascript code

$(function(){
   phery.remote('verticals');
});

That's it. Why (re)generate data, when you can do it once in the server (where data generation belongs)

Of course, you could just return the JSON and use phery:json, but that's not practical in your case

return PheryResponse::factory()->json($verticalData);

And the Javascript side

phery.remote('verticals', null, null, false).on('phery:json', function(data){
  for (var i in data){
    // do your append here
  }
});

edited Apr 1, 2013 at 21:29

answered Apr 1, 2013 at 21:15

pocesar

7,0707 gold badges58 silver badges91 bronze badges

2 Comments

Hanny Over a year ago

I'm sure this is a great solution - thank you for putting it out there. I will be checking out your library and putting that to use in the future!! Thanks!

pocesar Over a year ago

no problem, it was made with cutting down the necessary code from client in mind, when it's about manipulating HTML directly from PHP

Collectives™ on Stack Overflow

PHP arrays with Ajax - Proper way to get the data?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related