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 ツ
– Ravi Dhoriya ツ, Commented Jul 22, 2014 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
– Aniket Singh, Commented Jul 22, 2014 at 13:27
Please rephrase you question to include your real question or it will get flagged. — ztripez
– ztripez, Commented Jul 22, 2014 at 13:31

Community · Accepted Answer · 2017-05-23 11:58:05Z

1

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.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Jul 22, 2014 at 13:32

Ravi Dhoriya ツ

4,4148 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aniket Singh Over a year ago

Thanks Yes it working when i using data as a 2d array like this for(var i=0;i<data.length;i++) { $("p").append("ID"+data[i].s_id+"Name"+data[i].name); }

Akshay Raje · Accepted Answer · 2014-07-22 13:31:47Z

0

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.

answered Jul 22, 2014 at 13:31

Akshay Raje

89210 silver badges20 bronze badges

Collectives™ on Stack Overflow

How do i get a database table to JSON in php

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related