PHP-从数据库获取数据到JSON [英] PHP - get data from db to JSON

问题描述

我有两个表，一个表由ID和FNAME(例如，Google 01)组成，另一个表是FIRSTNAME，SURNAME(例如01 JOHN DOE).

I have two tables, one consisting of ID and FNAME (ex, 01 Google), the other ID, FIRSTNAME, SURNAME (ex 01 JOHN DOE).

最后一个数据库中的ID将用户与组织(例如在这里ID为01的Google的f.ex Google)联系起来.

The ID in the last database connects the user with an organization (f.ex Google that has ID 01 here).

我正在尝试通过json_encode将它们转换为JSON，但我无法为我所爱，所以我想出了如何将得到的两个结果联系起来的方法.

I'm trying to get these into a JSON via json_encode, but I can't for the love of me figure out how I can connect the two results I get.

应该按组织对用户进行排序.

The users are supposed to be sorted by organization.

例如:

{"Google":["John Doe","Paul"],"Microsoft":["Bill Gates"]}

我真的没有任何代码示例，因为我无法获得任何可以远程工作的代码.我试图将其放入while循环中的数组中，但是格式完全错误-排序也是如此.

I really don't have any code-example, since I can't get anything to remotely working. I'm trying to put it into arrays in the while-loop, but the format goes all wrong - so goes the sorting.

推荐答案

这可能对您有帮助

$res = mysql_query('SELECT * FROM employee em LEFT JOIN organization org ON em.id = org.empid');
$op = array();
while($row = mysql_fetch_assoc($res))
{
  print_r($row);
  if(!isset($op[$row[org]])){
    $op[$row[org]]  = array();
  }
  array_push( $op[$row[org]], $row['fname']." ".$row['lname']);
}

echo json_encode($op);

以及基于我本地数据库中条目的相应结果，

and respective result based on entries in my local db,

Array
(
    [id] => 1
    [fname] => john
    [lname] => doe
    [sno] => 1
    [empid] => 1
    [org] => Google
)
Array
(
    [id] => 2
    [fname] => will
    [lname] => smith
    [sno] => 2
    [empid] => 2
    [org] => Microsoft
)
Array
(
    [id] => 3
    [fname] => abdul
    [lname] => raseed
    [sno] => 3
    [empid] => 3
    [org] => Google
)
{"Google":["john doe","abdul raseed"],"Microsoft":["will smith"]}

注意:DDL如下，

/*DDL Information*/
-------------------

CREATE TABLE `employee` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `fname` VARCHAR(256) DEFAULT NULL,
  `lname` VARCHAR(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1


CREATE TABLE `organization` (
  `sno` INT(11) NOT NULL AUTO_INCREMENT,
  `empid` VARCHAR(10) NOT NULL,
  `org` VARCHAR(256) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

节点:尝试使用最新的mysql函数代替.

Node: Try to Use latest mysql function instead deprecated.

这篇关于PHP-从数据库获取数据到JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！