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.

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(). — Marc B
– Marc B, Commented Feb 1, 2013 at 14:45

Jaspal Singh · Accepted Answer · 2013-02-01 14:57:06Z

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;
?>

answered Feb 1, 2013 at 14:57

Jaspal Singh

1,2401 gold badge12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HILARUDEEN S ALLAUDEEN · Accepted Answer · 2013-02-01 15:34:03Z

1

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.

edited Feb 1, 2013 at 15:34

answered Feb 1, 2013 at 15:19

HILARUDEEN S ALLAUDEEN

1,7621 gold badge20 silver badges34 bronze badges

1 Comment

SDC Over a year ago

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

Collectives™ on Stack Overflow

PHP - get data from db to JSON

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related