Turn database result into array
Okay, I've written PHP classes that extend the Zend Framework DB table, row, and rowset classes. I've been developing this anyway because I'm speaking at PHP Tek-X in a couple of weeks about hierarchical data models.
I don't want to post all my code to Stack Overflow because they implicitly get licensed under Creative Commons if I do that. update: I committed my code to the Zend Framework extras incubator and my presentation is Models for Hierarchical Data with SQL and PHP at slideshare.
I'll describe the solution in pseudocode. I'm using zoological taxonomy as test data, downloaded from ITIS.gov. The table is longnames:
CREATE TABLE `longnames` (
`tsn` int(11) NOT NULL,
`completename` varchar(164) NOT NULL,
PRIMARY KEY (`tsn`),
KEY `tsn` (`tsn`,`completename`)
)
I've created a closure table for the paths in the hierarchy of taxonomy:
CREATE TABLE `closure` (
`a` int(11) NOT NULL DEFAULT '0', -- ancestor
`d` int(11) NOT NULL DEFAULT '0', -- descendant
`l` tinyint(3) unsigned NOT NULL, -- levels between a and d
PRIMARY KEY (`a`,`d`),
CONSTRAINT `closure_ibfk_1` FOREIGN KEY (`a`) REFERENCES `longnames` (`tsn`),
CONSTRAINT `closure_ibfk_2` FOREIGN KEY (`d`) REFERENCES `longnames` (`tsn`)
)
Given the primary key of one node, you can get all its descendants this way:
SELECT d.*, p.a AS `_parent`
FROM longnames AS a
JOIN closure AS c ON (c.a = a.tsn)
JOIN longnames AS d ON (c.d = d.tsn)
LEFT OUTER JOIN closure AS p ON (p.d = d.tsn AND p.l = 1)
WHERE a.tsn = ? AND c.l <= ?
ORDER BY c.l;
The join to closure AS p is to include each node's parent id.
The query makes pretty good use of indexes:
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
| 1 | SIMPLE | a | const | PRIMARY,tsn | PRIMARY | 4 | const | 1 | Using index; Using filesort |
| 1 | SIMPLE | c | ref | PRIMARY,d | PRIMARY | 4 | const | 5346 | Using where |
| 1 | SIMPLE | d | eq_ref | PRIMARY,tsn | PRIMARY | 4 | itis.c.d | 1 | |
| 1 | SIMPLE | p | ref | d | d | 4 | itis.c.d | 3 | |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
And given that I have 490,032 rows in longnames and 4,299,883 rows in closure, it runs in pretty good time:
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000257 |
| Opening tables | 0.000028 |
| System lock | 0.000009 |
| Table lock | 0.000013 |
| init | 0.000048 |
| optimizing | 0.000032 |
| statistics | 0.000142 |
| preparing | 0.000048 |
| executing | 0.000008 |
| Sorting result | 0.034102 |
| Sending data | 0.001300 |
| end | 0.000018 |
| query end | 0.000005 |
| freeing items | 0.012191 |
| logging slow query | 0.000008 |
| cleaning up | 0.000007 |
+--------------------+----------+
Now I post-process the result of the SQL query above, sorting the rows into subsets according to the hierarchy (pseudocode):
while ($rowData = fetch()) {
$row = new RowObject($rowData);
$nodes[$row["tsn"]] = $row;
if (array_key_exists($row["_parent"], $nodes)) {
$nodes[$row["_parent"]]->addChildRow($row);
} else {
$top = $row;
}
}
return $top;
I also define classes for Rows and Rowsets. A Rowset is basically an array of rows. A Row contains an associative array of row data, and also contains a Rowset for its children. The children Rowset for a leaf node is empty.
Rows and Rowsets also define methods called toArrayDeep() which dump their data content recursively as a plain array.
Then I can use the whole system together like this:
// Get an instance of the taxonomy table data gateway
$tax = new Taxonomy();
// query tree starting at Rodentia (id 180130), to a depth of 2
$tree = $tax->fetchTree(180130, 2);
// dump out the array
var_export($tree->toArrayDeep());
The output is as follows:
array (
'tsn' => '180130',
'completename' => 'Rodentia',
'_parent' => '179925',
'_children' =>
array (
0 =>
array (
'tsn' => '584569',
'completename' => 'Hystricognatha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '552299',
'completename' => 'Hystricognathi',
'_parent' => '584569',
),
),
),
1 =>
array (
'tsn' => '180134',
'completename' => 'Sciuromorpha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '180210',
'completename' => 'Castoridae',
'_parent' => '180134',
),
1 =>
array (
'tsn' => '180135',
'completename' => 'Sciuridae',
'_parent' => '180134',
),
2 =>
array (
'tsn' => '180131',
'completename' => 'Aplodontiidae',
'_parent' => '180134',
),
),
),
2 =>
array (
'tsn' => '573166',
'completename' => 'Anomaluromorpha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '573168',
'completename' => 'Anomaluridae',
'_parent' => '573166',
),
1 =>
array (
'tsn' => '573169',
'completename' => 'Pedetidae',
'_parent' => '573166',
),
),
),
3 =>
array (
'tsn' => '180273',
'completename' => 'Myomorpha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '180399',
'completename' => 'Dipodidae',
'_parent' => '180273',
),
1 =>
array (
'tsn' => '180360',
'completename' => 'Muridae',
'_parent' => '180273',
),
2 =>
array (
'tsn' => '180231',
'completename' => 'Heteromyidae',
'_parent' => '180273',
),
3 =>
array (
'tsn' => '180213',
'completename' => 'Geomyidae',
'_parent' => '180273',
),
4 =>
array (
'tsn' => '584940',
'completename' => 'Myoxidae',
'_parent' => '180273',
),
),
),
4 =>
array (
'tsn' => '573167',
'completename' => 'Sciuravida',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '573170',
'completename' => 'Ctenodactylidae',
'_parent' => '573167',
),
),
),
),
)
Re your comment about calculating depth -- or really length of each path.
Assuming you've just inserted a new node to your table that holds the actual nodes (longnames in the example above), the id of the new node is returned by LAST_INSERT_ID() in MySQL or else you can get it somehow.
INSERT INTO Closure (a, d, l)
SELECT a, LAST_INSERT_ID(), l+1 FROM Closure
WHERE d = 5 -- the intended parent of your new node
UNION ALL SELECT LAST_INSERT_ID(), LAST_INSERT_ID(), 0;
Converting large number of database results into array of objects in javascript
Your best bet is actually to do this all in SQL, since you are using PostgreSQL for your database (as mentioned in comments). I don't know the exact names of your tables and columns, so you may need to tweak this, but this will get you what you want:
SELECT json_agg(t)
FROM (
SELECT
u.id,
u.name,
ro.roles
FROM "user" u
LEFT JOIN (
SELECT
ur.user_id,
json_agg(
json_build_object(
'id', r.id,
'title', r.title
)
) AS roles
FROM user_role ur
LEFT JOIN "role" r ON r.id = ur.role_id
GROUP BY ur.user_id
) ro ON ro.user_id = u.id
) t;
SQL Fiddle: http://www.sqlfiddle.com/#!17/5f6ca/11
Explanation
json_build_object will create an object using the name / value pairs specified, so:
json_build_object(
'id', r.id,
'title', r.title
)
Combines the role id and title into a JSON object like this:
{id: 1, title: "Title 1"}
json_agg aggregates multiple rows into a JSON array, so it converts the role objects above into a single column that is an array of role objects per user (thanks to the GROUP BY u.id part of the inner subquery). The inner subquery gives us a result set like this (one row per user)
| user_id | roles |
|---------|------------------------------------------------------|
| 1 | [{id: 1, title: "Role 1"}, {id: 2, title: "Role 2"}] |
Then the subquery is joined to the user table, and all of that is wrapped in another subquery so json_agg can be used on the entire result and return a single json object that is an array of users with roles.
Convert SQL results into PHP array
You can do:
$rows = [];
while($row = mysqli_fetch_array($result))
{
$rows[] = $row;
}
Converting a database result of 3 columns to associative array
You can achieve this by using two loops. One to prepare the values for the format you want, and one to actually put the values in that format.
First, prepare the data
$array = array(
array('ApptTime' => "16:00", 'ApptType' => "pickup", 'Count' => 1),
array('ApptTime' => "16:30", 'ApptType' => "dropoff", 'Count' => 1),
array('ApptTime' => "16:30", 'ApptType' => "pickup", 'Count' => 6),
array('ApptTime' => "16:45", 'ApptType' => "pickup", 'Count' => 2),
array('ApptTime' => "16:45", 'ApptType' => "dropoff", 'Count' => 1)
);
$new_array = [];
foreach($array as $arr) {
$time = $arr['ApptTime'];
$type = $arr['ApptType'];
$count = $arr['Count'];
//check if `$time` exists as key of `$new_array`, if not, create it with an empty array value
if(!array_key_exists($time, $new_array)) {
$new_array[$time] = [];
}
//add string e.g "1 pickup" as array value to array `$new_array` with key `$time`
$new_array[$time][] = "{$count} {$type}";
}
First loop return
Array
(
[16:00] => Array
(
[0] => 1 pickup
)
[16:30] => Array
(
[0] => 1 dropoff
[1] => 6 pickup
)
[16:45] => Array
(
[0] => 2 pickup
[1] => 1 dropoff
)
)
And this is where I would personally stop, and format the values when actually needed to be displayed. However, the following loop can be used to modify the $new_array to the format you wanted.
foreach($new_array as &$n_arr) {
$n_arr = implode(', ', $n_arr);
}
Second loop return
Array
(
[16:00] => 1 pickup
[16:30] => 1 dropoff, 6 pickup
[16:45] => 2 pickup, 1 dropoff
)
converting database result into array in codeigniter
In model
$res = $this->db->get();
return $res->result_array();
then apply
foreach($answer as $a)
{
var_dump( $a );
}
use $a to get values
Convert sql query result into JSON array in python
If your version of sqlite has the JSON1 extension enabled, it's easy to do in pure SQL:
SELECT json_object('Teacher', teacher,
'Students', json_group_array(student)) AS result
FROM ex
GROUP BY teacher;
DB Fiddle example
How to convert the array of objects into an array?
Try with result_array() function instead of result()
Related Topics
Bounce Email Handling with PHP
Expected Response Code 220 But Got Code "", with Message "" in Laravel
Array_Multisort and Dynamic Variable Options
How to Detect an Incoming Request (With PHP Script) from a Cname Subdomain
Determining What Classes Are Defined in a PHP Class File
Handling Expired Token in Laravel
How to Rename Sub-Array Keys in PHP
Checking If Array Is Multidimensional or Not
How to Install the Ext-Curl Extension with PHP 7
How to Update/Edit a JSON File Using PHP
Converting Object to JSON and JSON to Object in PHP, (Library Like Gson for Java)
Warning: File_Get_Contents(): Https:// Wrapper Is Disabled in the Server Configuration by All