PHP multidimension array...

Hello, I have a php script as follows;

function statusCount($id){
$query = "SELECT status, COUNT(*) FROM my_orders WHERE order_date>=DATE_SUB(CURDATE(),INTERVAL 90 DAY) AND cityid = (SELECT cityid FROM order_city WHERE city_code:id) GROUP BY STATUS";
          $result=array();
          $sql = $db->prepare($query);
          $sql->bindParam(':id', $id);              
          $sql->execute();         
          $result= $sql->fetchAll(PDO::FETCH_ASSOC);
          return $result;
}
$myID = 01;
$output = statusCount($myID)

Open in new window


The output of this script is :
Completed 23
Processed 15
Submitted 25
Shipped     40

Open in new window


I need to change to be able to get an output in an multidimentional array to be able to get the out put of more than one city id. The output I am looking for is as follows;

City   completed  processed  submitted   shipped
01         10                  14               34                 40
02          12                  67              89                 89
03           11                  12             00                 00 

Open in new window


Can someone let me know how this can be done? Thank you.

A
aej1973Asked:
Who is Participating?
 
arnoldConnect With a Mentor Commented:
So you want to dynamically get the information based on the city_code you provide.
function statusCount($id){
$query = "SELECT a.cityid as City,
sum (if ( a.status='Shipped',1,0)) as Shipped, 
sum (if ( a.status='Completed',1,0)) as Completed, 
sum (if ( a.status='Submitted',1,0)) as Submitted, 
sum (if ( a.status='Processed',1,0)) as Processed
FROM my_orders a  join order_city b on a.cityid=b.cityid WHERE order_date>=DATE_SUB(CURDATE(),INTERVAL 90 DAY) AND b.city_code:id) GROUP BY a.cityid ";
          $result=array();
          $sql = $db->prepare($query);
          $sql->bindParam(':id', $id);              
          $sql->execute();         
          $result= $sql->fetchAll(PDO::FETCH_ASSOC);
          return $result;
}
$myID = 01;
$output = statusCount($myID)

Open in new window


See if that gives you the output you want without the need to aggregate/assemble the data within PHP.

The result is a table in the format since your dynamic query is providing the City_code
City,Shipped, Completed,Submitted,Processed
you only will get one row as a response
1x4 array.  If you want to get all your data, remove the test on city code, and make the query dynamic based on the length of time for which you want the report. i.e. for the last 90 days which is your current query. the results will table with the above breakdown for all the citys for which orders were received, and their current status.
0
 
GaryCommented:
The SQL and your example output don't match so I don't see how status matches with your example output above

It would probably be easier to group by city and do a GROUP_CONCAT on each individual field per city then you can explode those individual columns into an array and loop them.
You would need to add an ISNULL on the fields to pass in a blank value where the value is null if that could be case for any field.
0
 
aej1973Author Commented:
Hello Gary, not sure why you say that the SQL statement and output does not match. This is what I have and it gives the output I have shown. Please let me know why you think it does not match, thank you.

A
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
GaryCommented:
SELECT status, COUNT(*) ... GROUP BY STATUS
There is no status in your example output so I don't know what status relates to?

Might be better if you can give a dump or example dump of your table data.

edit
Reread through the question and see what you mean. Will have a little think
0
 
arnoldCommented:
It seems you are looking to do requests in sequence for status, count to populate instead of constructing the sql query to return the data in a format you wish.

You could array of arrays hash of arrays or arrays of hashes.
You would need to pass the reference to array or hash to the function that will add elements accordingly.

Another option is to create a view or a temporary table that will populate the data from which your output process will retrieve the data.
0
 
aej1973Author Commented:
Sorry, here is the updated query and the statement;
function statusCount($id){
$query = "SELECT status, COUNT(*) FROM my_orders WHERE order_date>=DATE_SUB(CURDATE(),INTERVAL 90 DAY) AND cityid = (SELECT cityid FROM order_city WHERE city_code:id) GROUP BY status";
          $result=array();
          $sql = $db->prepare($query);
          $sql->bindParam(':id', $id);              
          $sql->execute();         
          $result= $sql->fetchAll(PDO::FETCH_ASSOC);
          return $result;
}
$myID = 01;
$output = statusCount($myID)

Open in new window


Here is how the table is structured;
cust_id	staus	cityid
1001	Completed	1
1002	Processed	2
1003	Submitted	1
1004	Shipped	1
1005	Completed	3
1006	Completed	6
1007	Completed	5
1008	Processed	3
1009	Completed	7
1010	Processed	1
1011	Processed	1
1012	Processed	8
1013	Submitted	9
1014	Shipped	4
1015	Shipped	1
1016	Shipped	1
1017	Shipped	2

Open in new window


Thank you,
A
0
 
arnoldCommented:
Array of hashes
An alternative is to use a pivot table.

This is discussed as a response to a query to alter the display in columns where data is stored in rows.

http://forums.mysql.com/read.php?52,104797,104821#msg-104821

Your select query is altered to not specify status, but the status is counted.
0
 
Dave BaldwinFixer of ProblemsCommented:
Your 'desired  results' require a completely different query.  For starters, 'cityid' is not part of your result set.  And "count(*)" won't give you counts by city.
0
 
arnoldCommented:
The asked is usiing dynamic query with nested selects to reach the Id being passed to this function to extract the city, status using city_code
Altering the query as suggested to group by cityid will simplify.

Try using explain with your sql query on the mysql server.

Then try the explain on a query returning the data in a table to you PHP rendering /reporting process.
0
 
arnoldCommented:
Please also post the show create table for my_orders and order_city
0
 
aej1973Author Commented:
The table structure I showed about is the my_orders table.
0
 
arnoldCommented:
The query within php would look something like in the example in the mysql url posted earlier
Select order_city.city_code,
Conditional sum as completed,,
Conditional sum as processed,
Conditional sum as submitted,
Conditional sum as shipped,
From my_orders join order_city on my_orders.ordercity_reference=order_city.cityid
Where datediff(my _order.order_date,curdate()) <=90
Group by order_city.city_code

The response to the query would be a table in the format you want
cityid,completed,processed,submitted,shipped
0
 
GaryCommented:
@arnold
There is no such thing as "Conditional sum" in MySQL

@aej1973
Post a dump of the relevant tables,

I think the group concat is probably the best advice and do the stuff in PHP
0
 
aej1973Author Commented:
Gary, this is how the two tables look;

my_orders table:

cust_id	staus	cityid	order_date
1001	Completed	1	11/18/2014 18:29
1002	Processed	2	12/8/2014 18:01
1003	Submitted	1	12/15/2014 18:00
1004	Shipped	1	10/8/2014 17:52
1005	Completed	3	11/28/2014 17:40
1006	Completed	6	12/8/2014 17:11
1007	Completed	5	12/1/2014 17:03
1008	Processed	3	12/12/2014 16:41
1009	Completed	7	12/15/2014 18:00
1010	Processed	1	10/8/2014 16:16
1011	Processed	1	9/11/2014 16:01
1012	Processed	8	12/8/2014 15:57
1013	Submitted	9	12/1/2014 17:03
1014	Shipped	4	12/12/2014 16:41
1015	Shipped	1	12/15/2014 18:00
1016	Shipped	1	12/15/2014 18:00
1017	Shipped	2	10/8/2014 17:52

Open in new window


order_city	table

cityid	city_code
1	AT
2	BO
3	CU
4	CR
5	CV
6	CT
7	DW
8	DE
9	DM
10	FL
11	FT
12	HS
13	JX
14	OF
15	LV
17	MV
18	MF

Open in new window


Thanks,
A
0
 
arnoldCommented:
Gary, I know there is no conditional sum as a mysql directive in a query,   I posted a link to a discussion dealing with the exact same conversion of data in rows into a table of data displayed in columns which I referenced.

The absence of the table structure is what prevents a more direct example.

The example deals with a join statement between two tables the sum ( if (status='complete',1,0) ) as complete, change the status condition and modify the column name accordingly.
0
 
GaryCommented:
@aej1973
Can you post an sql dump i.e. with the create table/insert commands etc. and enough data to test.
0
 
aej1973Author Commented:
Arnold, I cannot thank you enough, this is exactly what I was looking for, Amazing!!!

Thank you,
A
0
 
aej1973Author Commented:
Excellent!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.