How do i get a database table to JSON in php

Question

i used this code

$dbhandle = mysql_connect("localhost","root","password")
or die("Unable to connect to MySQL");

$arr;

//select a database to work with
$selected = mysql_select_db("info",$dbhandle)
or die("Could not select examples");

$result = mysql_query("SELECT * FROM students");

while($row = mysql_fetch_array($result))
{
    $arr[]=$row;
}

Is it right way to store all data of a table into a 2D array. And how i use this array in Ajax as json to show data in a HTML file.?

the other thing you could do is, you can create associative array for all students (by some primary key), to efficiently access students by id. like `$students[$row['stud_id']]=$row;`... and later access individual student records like, `$students[101]` — Ravi Dhoriya ツ, Jul 22 '14 at 13:25
I want to use this array in Ajax as json to print it in a HTML page. how i can? — Aniket Singh, Jul 22 '14 at 13:27
Please rephrase you question to include your real question or it will get flagged. — ztripez, Jul 22 '14 at 13:31

score 1 · Accepted Answer · edited May 23 '17 at 11:58

As you mentioned in comments, that you want to do ajax and json.

You can do code like this (use mysql_fetch_assoc() instead of array):

//set content type: text/json
header("Content-Type: text/json");

$dbhandle = mysql_connect("localhost","root","password") or die("Unable to connect to MySQL");

//select a database to work with
$selected = mysql_select_db("info",$dbhandle) or die("Could not select examples");

$result = mysql_query("SELECT * FROM students");

if(mysql_num_rows($result)>0){
    $rows=array();
    while($row = mysql_fetch_assoc($result))
    {
        $rows[]=$row;
    }
    echo json_encode(array("success"=>$rows));
}else{
    echo json_encode(array("error"=>"No records found."));
}
exit;

and for ajax (assuming, jQuery.get() AJAX)

$.get("script.php", function(data){
    if(data.success){
        //process each row
        data.success.forEach(function(row){
            console.log(row); //access student row here, simply like row.student_name, row.city etc.
        });
    }else{
        alert(data.error);
    }
});

Scurity Note:

mysql_* functions are deprecated and will be removed from support, so you should avoid using it.
use mysqli_* or PDO for database queries.

Thanks Yes it working when i using data as a 2d array like this for(var i=0;i — Aniket Singh, Jul 23 '14 at 05:13

score 0 · Answer 2 · answered Jul 22 '14 at 13:31

Use mysql_fetch_assoc instead of mysql_fetch_array...

while ($row = mysql_fetch_assoc($result)) {
    $arr[]=$row; 
}

Check mysql_query documentation for more information. However, its recommended that you use PDO or MySQLi drivers as this is deprecated as of PHP 5.5.0.

How do i get a database table to JSON in php

2 Answers2