PHP - get data from db to JSON

Question

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

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

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.

Ex:

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

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.

• Marc B: 1. write select queries. 2. run queries. 3. retrieve results with whatever your DB library's equivalent of ->fetch() is. 4. build array with the results. 5. run array through json_encode().

Answer 1

Just so you can get started,

<?php
 $outArr = array();
 $arr1 = array(0 => array('ID' => '01', 'FIRSTNAME' => 'Google'));
 $arr2 = array(0 => array('ID' => '01', 'FIRSTNAME' => 'JOHN', 'SURNAME' => 'DOE'),
 1 => array('ID' => '01', 'FIRSTNAME' => 'NewJOHN', 'SURNAME' => 'NewDOE'));
 foreach ($arr1 as $key => $compArr) {
 $companyId = $compArr['ID'];
 $outArr[$compArr['FIRSTNAME']] = '';
 foreach ($arr2 as $arr2key => $employeeArr) {
 if ($employeeArr['ID'] == $companyId) {
 $outArr[$compArr['FIRSTNAME']][] .= $employeeArr['FIRSTNAME'];
 }
 }
 }
 echo json_encode($outArr);
 exit;
?>

Answer 2

This may help you,

$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"]}

Note: DDL would be as follows,

/*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

Node: Try to Use latest mysql function instead deprecated.

• SDC: please don't suggest using the old mysql_xxx() functions; they are deprecated and obsolete. Use mysqli_xxX() or PDO instead.