CODEWITHSUNDEEP
phpmysqlarraysjson
Joe
Joe

Reputation: 124

In PHP, how can I return multiple column values from MySQL in JSON format?

I'm trying to return a list of all of the courses a user is enrolled in (course1, course2, etc.) Currently, I have the following code:

$mysqli = new mysqli("localhost","username","password","sampleTest");
if (mysqli_connect_errno()) {
    printf("Can't connect to SQL Server. Error Code %s\n", mysqli_connect_error($mysqli));
    exit;
}
// Set the default namespace to utf8
$mysqli->query("SET NAMES 'utf8'");
$json   = array();
if($result = $mysqli->query("select course1 from users where username ='test'")) {
    while ($row=$result->fetch_assoc()) {
        $json[]=array(
            'courses'=>$row['course1'],

        );
    }
}
$result->close();

header("Content-Type: text/json");
echo json_encode(array( 'courses'  =>   $json )); 

$mysqli->close();

I can get the first course to show but not the others. I've tried select * from users where username ='test' but I'm stuck on the passing along the array.

Upvotes: 0

Views: 3047

Answers (4)

olezhek
olezhek

Reputation: 58

Try to perform this:

$mysqli = new mysqli("localhost","username","password","sampleTest");
if (mysqli_connect_errno()) {
    printf("Can't connect to SQL Server. Error Code %s\n", mysqli_connect_error($mysqli));
    exit;
}
// Set the default namespace to utf8
$mysqli->query("SET NAMES 'utf8'");
$json   = array();
if($result = $mysqli->query("select * from users where username ='test'")) {
    while ($row=$result->fetch_assoc()) {
        $json[]=array(
            'courses'=> array(
                  'cource1' => $row['course1'],
                  'cource2' => $row['course2'],
                  'cource3' => $row['course3'],
            ),
        );
    }
}
$result->close();
header("Content-Type: text/json");
echo json_encode(array( 'courses'  =>   $json )); 
$mysqli->close();

Assuming that you have the users table with fields named 'course1','course2','course3' etc and 'username'. You can perform * or just list required fields (comma-separated) after SELECT keyword in the sql query. For example, you have one user with name test:

username course1 course2 course3
  test     Yes     Yes     No

You'll receive just one row from the table. You'll get

//var_dump($json);

array(1) {
  [0]=>
  array(1) {
    ["cources"]=>
    array(3) {
      ["cource1"]=>
      string(3) "Yes"
      ["cource2"]=>
      string(3) "Yes"
      ["cource3"]=>
      string(2) "No"
    }
  }
}

after performing the code above. Then you just need to json_encode() this array. Hope that it'll help.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157839

It seems that your database has wrong design.

These courses should be stored in a separate table, not in the users table.
And then it can be easily retrieved.

To answer more certainly, more data of your database structure is required.

Upvotes: 2

texai
texai

Reputation: 3736

If you want to list only courses, you don't need to all these lines.

while ($row=$result->fetch_assoc()) {
    $json[]=array(
        'courses'=>$row['course1'],

    );
}

this will be enough

while ($row=$result->fetch_assoc()) {
    $json[]= $row['course1'] ;
}

Upvotes: 0

j_freyre
j_freyre

Reputation: 4738

What if you're doing something like that:

<?php
...
$json = array();
$json['courses'] = array();
if($result = $mysqli->query("select course1 from users where username ='test'")) {
    while ($row=$result->fetch_assoc()) {
        $json['courses'][] = $row['course1'];
    }
}
...
?>

Upvotes: 0

Related Questions