php - 关联数组中的多个 SQL 查询

Question

我有一个关于发送多个查询并将每个结果存储在关联数组中的非常简单的问题。

基本上，我在关联数组中分配了每个查询。

每个查询的每个结果都进入我的多维数组$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);

到目前为止一切正常，但是我想知道我是否使用了最好的方法来实现这一点？

这是处理从我上面的代码生成的 json 数据的 jquery 脚本...它以一种形式用不同的值填充多个列表:

$.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);

});

Answer 1

您可以使用一个 查询并使用UNION

select .... UNION select ...