Thursday, 30 August 2018

Multi-SQL queries in an associative array with PHP / SQL

I've got a very simple question about sending multi-queries with php/sql and add each result in an associative array.

Basically, I've assigned each query in an associative array.
Each result for each queries go into my multi-dimensional array $el['nameofthequery']['resultsofthequery']
//connection information
 $host = "localhost";
 $user = "root";
 $password = "";
 $database = "sghi";
 $el = array();
 $query = array();

 //make connection
 $server = mysql_connect($host, $user, $password) or die('Could not connect to mysql server.');
 $connection = mysql_select_db($database, $server);

 //query the database
 $query['mylist1'] = mysql_query("SELECT CompanyName AS label,ContactName AS value FROM suppliers") or die('Could not select database.');
 $query['mylist2'] = mysql_query("SELECT ContactTitle AS label,City AS value FROM suppliers") or die('Could not select database.');

 //build array of results

  // Check if there is any results
  if(mysql_num_rows($query['mylist1']) == 0) {
   echo "No results found";
   exit; // so exit
  }
  else {
   while( $row = mysql_fetch_assoc( $query['mylist1']) ){
    $el['mylist1'][] = $row;
   }
  }

  // Check if there is any results
  if(mysql_num_rows($query['mylist2']) == 0) {
   echo "No results found";
   exit; // so exit
  }
  else {
   while( $row = mysql_fetch_assoc( $query['mylist2']) ){
    $el['mylist2'][] = $row;
   }
  }

 //echo JSON to page
 $response = json_encode($el);
 echo $response;
 mysql_close($server);

It's working fine so far, but I would like to know if I'm using the best method in order to achieve that?
Here is the jquery script that handle the json data generated from my code above...it populate multiple list with different values in a form:
$.getJSON('test.php', function(result) {

    var optionsValues = '';
$.each(result['mylist1'], function(item) {
  optionsValues += '<option value="' + this.value + '">' + this.label + '</option>';
});
var options = $('#DestAirportList');
options.append(optionsValues);

    var optionsValues = '';
$.each(result['mylist2'], function(item) {
  optionsValues += '<option value="' + this.value + '">' + this.label + '</option>';
});
var options = $('#DestAirportRoomList');
options.append(optionsValues);

});

Thanks for your answer!

Besides the fact that you aren't using mysqli...
May I ask why you are doing this? It's often easier to know if it's a good/bad method in context.

0 comments:

Post a Comment