将数据从MySQL拉入json数组

I'm trying to pull data from my database using json in php. I have a few elements I need to specific then to post them on a page.

我正在尝试使用php中的json从我的数据库中提取数据。我有一些我需要特定的元素然后将它们发布在页面上。

I want to "fetch" the data from mysql and return it to a json_encode. How can I do this using the SELECT method. Some had used PDO methods and other have used mysql_assoc, which confuses me.

我想从mysql“获取”数据并将其返回到json_encode。如何使用SELECT方法执行此操作。有些人使用过PDO方法,有些人使用过mysql_assoc,这让我很困惑。

For instance,

I have rows of: 'id' , 'title' , 'start', 'backgroundColor'...etc. along with a default value for all of them. ($array[] = "someValue = default")

我有一行:'id','title','start','backgroundColor'......等等。以及所有这些的默认值。 ($ array [] =“someValue = default”)

I want it to export like so:

我希望它像这样导出:

array(
      'id' => 1,
      'title' => "someTitle",
      'start' => "2012-04-16",
      'backgroundColor' => "blue",
      'someValue' = > "default",
      ...
      ),   ....
 ));

If anyone could help me with this with the best detail, I'd be awesome!

如果有人能用最好的细节帮助我,我会很棒!

4 个解决方案

#1

If you wanted to do this with PDO then here is an example:

如果你想用PDO做这个,那么这里是一个例子:

<?php 
$dbh = new PDO("mysql:host=localhost;dbname=DBNAME", $username, $password);

$sql = "SELECT `id`, `title`, `time`, `start`, `backgroundColor` 
        FROM my_table";

$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
//To output as-is json data result
//header('Content-type: application/json');
//echo json_encode($result);

//Or if you need to edit/manipulate the result before output
$return = [];
foreach ($result as $row) {
    $return[] = [ 
        'id' => $row['id'],
        'title' => $row['title'],
        'start' => $row['start'].' '.$row['time'],
        'backgroundColor' => $row['backgroundColor']
    ];
}
$dbh = null;

header('Content-type: application/json');
echo json_encode($return);
?>

#2

You don't "fetch to a json array".

你没有“获取到json数组”。

You fetch your database results into a PHP array, then convert that php array, AFTER THE FETCHING IS COMPLETED, to a json string.

您将数据库结果提取到PHP数组中,然后将这个php数组(在完成FETCHING之后)转换为json字符串。

e.g.

$data = array();
while ($row = mysql_fetch_assoc($results)) {
   $data[] = $row;
}
echo json_encode($data);

#3

You can get the result from mysql,then format it to json

您可以从mysql获取结果,然后将其格式化为json

    $array = array();
    while($row = mysqli_fetch_array($result))
    {
        array_push($array,$row);
    }
    $json_array = json_encode($array);

#4

Please check for SELECT methods here

请在这里检查SELECT方法

In general it would look like this

一般来说,它看起来像这样

$data = array(); // result variable

$i=0

$query = "SELECT id,title,start,backgroundColor FROM my_table"; // query with SELECT

$result = mysql_query($query);


while($row = mysql_fetch_assoc($result)){ // iterate over results
      $data['item'][$i]['id'] = $row['id']; // rest similarly 
      ...
      ...

      $i++; 
}

header('Content-type: application/json'); // display result JSON format
echo json_encode(array(
     'success' => true,
     'data' => $data // this is your data variable
));

#1