PHP & MYSQL: Convert rows and columns to json objects

I’m trying to get all of the rows and columns of a MYSQL table to be objects in an array so I can retrieve them via javascript.

Here’s what I’ve got so far:

<?PHP
include '../sql.php';
$c = mysql_connect('localhost', $user, $pw) or die('Unable to connect to MySQL');
$s = mysql_select_db('mydb') or die('Could not select database');
$q = mysql_query('SELECT * FROM mytable') or die(mysql_error());
$a = array();

$cl = mysql_num_fields($q); 
for($i = 1; $i < $cl; $i++){
    while($r = mysql_fetch_assoc($q)){
        foreach($r as $k => $v){
            $a[] = //something;
        }
    }
}

echo json_encode($a);
mysql_close($c);
?>

The object at the end should look something like this:

[
    {id: 1, name: 'bob', status: 'sleeping'},
    {id: 2, name: 'james', status: 'partying'},
    {id: 3, name: 'eric', status: 'reading'}
]

Answer

What about doing it in two (I mean three!) lines?

$pdo = new PDO("mysql:host=localhost;dbname=myDB;charset=utf8",'myUserName','myPassword');
$stmt = $pdo->query('SELECT * FROM mytable');
echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

PS. Using SELECT * is considered bad practice.

Advertisement

Answer