How to get multiple column values from Mysql into JQuery function Using Ajax

Question

I have a Jquery Function which generates reports like bar chart, line chart etc.

I used ajax in Jquery, to make a call to php function which returns a Json object. The problem is, I have to return the complete data from all the columns in a table. With ajax, i have successfully returned all the values in a single column to my Jquery function . But, i am unable to return multiple columns. Please Help me.

//Ajax function calling
$.ajax({                                      
    url: 'Data.php',                  //the script to call to get data          
    data: "",                        //you can insert url argumnets here to pas
                                   //for example "id=5&parent=6"
  dataType: 'json',    
  async: false,//data format      
  success: function(data)          //on recieve of reply
  {
 var returnedArray = [];
returnedArray = data;

  } 
 });
  ///// Data.php code
<?php 


   $host = "localhost";
   $user = "root";
   $pass = "";

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


$i=0;
$storeArray= Array();

$result = mysql_query("SELECT User_Id FROM user");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
    {
    $storeArray[$i] =  $row['User_Id'];  

    $i++;
    }   




    echo json_encode($newstoreArray);

     ?>

In the above Data.php, i have successfully returned values of a single column "User_Id" values. I want to return more Columns.

Mahmoud Gamal · Accepted Answer · 2013-01-24 08:37:56Z

First, you should include more columns in your query:

$result = mysql_query("SELECT User_Id, col2, col3, ... FROM user");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
    array_push($storeArray, array('User_Id' => $row['User_Id'],
                                 'col2'    => $row['col2'],
                                 ... //tell the rest of your columns
                                 ));
    $i++;
}

Second, in the success function you should handle these new columns' values like:

success: function(data)          //on recieve of reply
{
   var returnedArray = [];
   returnedArray = data;

   var item = '';
   $.each(data, function(){
       // here you can loop through all columns values like this['User_Id'], this['col2']
       item = ... 
   });
}

Note that: Try to avoid the mysql_query and use prepared statments or PDO instead.

Vlad Preda · Accepted Answer · 2013-01-24 08:33:01Z

0

Just replace the while with this and you will have the full results from mysql.

$query = "SELECT User_ID, col2, col3 FROM user"; // select multiple columns
$result = mysqli_query($conn, $query); // replaced with procedural mysqli
while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) 
{
    $storeArray[] =  $row;  
}

PS: Stop using mysql extension, it is deprecated (check the big red box in the link). Replace it with mysqli or PDO

edited Jan 24, 2013 at 8:33

answered Jan 24, 2013 at 8:26

Vlad Preda

9,9808 gold badges40 silver badges63 bronze badges

Collectives™ on Stack Overflow

How to get multiple column values from Mysql into JQuery function Using Ajax

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related