CODEWITHSUNDEEP
phpjquerymysqlajax
user3708494
user3708494

Reputation: 15

retrieve multiple data from various queries with ajax php mysql

I'm new in Ajax and JSON notation, so I'm trying to get data from differents tables of a Database, data like country names, state names, departament name, job position etc. and I've seen examples how through JSON can get data but just from a single table, can you give me a little help how can I do it with more than one table and keep it in an array. 

<?php 


  $host = "localhost";
  $user = "usuer";
  $pass = "password";

  $databaseName = "jsonExample";
  $tableName = "variables";


  $con = mysql_connect($host,$user,$pass);
  $dbs = mysql_select_db($databaseName, $con);


  $result = mysql_query("SELECT * FROM $tableName");            //query
  //$array = mysql_fetch_row($result);                          //fetch result
    if(mysql_num_rows($result) <= 0){

    }else{
        while($obj = mysql_fetch_row($result)){
        $array[] = $obj;        
        }
    }

  echo json_encode($array);

?>

Html file:

<html>
  <head>
    <script language="javascript" type="text/javascript" src="jquery.js"></script>
  </head>
  <body>-->


  <h2> Client example </h2>
  <h3>Output: </h3>
  <div id="output">this element will be accessed by jquery and this text will be replaced</div>

  <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 for example "id=5&parent=6"
      dataType: 'json',                //data format      
      success: function(data)          //on recieve of reply
      {

        var id = data[0];              //get id
        var vname = data[1];           //get name



        $('#output').html("<b>id: </b>"+id+"<b> name: </b>"+vname);     //Set output element html
        //recommend reading up on jquery selectors they are awesome http://api.jquery.com/category/selectors/
      } 
    });

  }); 
  </script>

  </body>
</html>

Upvotes: 1

Views: 4221

Answers (1)

Barry
Barry

Reputation: 3723

If you want to have the results from multiple queries in one array you can add each result to a key. F.i. if you querying table table1 to tablen ...

// define the array that will contain all result sets
$array = [];

// create an array for the result set coming from table 1
$array['table1']= [];
$result = mysql_query("SELECT * FROM table1");
    if(mysql_num_rows($result) <= 0){
}else{
    while($obj = mysql_fetch_row($result)){
    $array['table1'][] = $obj;        
    }
}

// create an array for the result set coming from table 2 
$array['table2']= [];
$result = mysql_query("SELECT * FROM table2");
    if(mysql_num_rows($result) <= 0){
}else{
    while($obj = mysql_fetch_row($result)){
    $array['table2'][] = $obj;        
    }
}
::
::
// create an array for the result set coming from table n
$array['tablen']= [];
$result = mysql_query("SELECT * FROM tablen");
    if(mysql_num_rows($result) <= 0){
}else{
    while($obj = mysql_fetch_row($result)){
    $array['tablen'][] = $obj;        
    }
}

// return the results formatted as json
return json_encode($array);

In javascript you can access the results for table1 with data->table1.

Tip

Use mysqli instead of mysql. It is the improved version of mysql. Check the answers for this question for some background.

Upvotes: 1

Related Questions