PHP multidimension array...

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.

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

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

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.

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)

Select allOpen 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

Select allOpen in new window

Thank you,
A

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.

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.

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.

Please also post the show create table for my_orders and order_city

The table structure I showed about is the my_orders table.

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

@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

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

Select allOpen 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

Select allOpen in new window

Thanks,
A

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.

@aej1973
Can you post an sql dump i.e. with the create table/insert commands etc. and enough data to test.

Arnold, I cannot thank you enough, this is exactly what I was looking for, Amazing!!!

Thank you,
A

Excellent!!!!