Solved

PHP multidimension array...

Posted on 2014-12-21
18
98 Views
Last Modified: 2014-12-25
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
0
Comment
Question by:aej1973
  • 7
  • 6
  • 4
  • +1
18 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40512283
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
 

Author Comment

by:aej1973
ID: 40512292
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
 
LVL 58

Expert Comment

by:Gary
ID: 40512293
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 77

Expert Comment

by:arnold
ID: 40512296
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
 

Author Comment

by:aej1973
ID: 40512303
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
 
LVL 77

Expert Comment

by:arnold
ID: 40512312
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40512318
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
 
LVL 77

Expert Comment

by:arnold
ID: 40512326
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
 
LVL 77

Expert Comment

by:arnold
ID: 40512329
Please also post the show create table for my_orders and order_city
0
 

Author Comment

by:aej1973
ID: 40512332
The table structure I showed about is the my_orders table.
0
 
LVL 77

Expert Comment

by:arnold
ID: 40512336
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
 
LVL 58

Expert Comment

by:Gary
ID: 40512362
@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
 

Author Comment

by:aej1973
ID: 40512379
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
 
LVL 77

Expert Comment

by:arnold
ID: 40512394
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
 
LVL 58

Expert Comment

by:Gary
ID: 40515553
@aej1973
Can you post an sql dump i.e. with the create table/insert commands etc. and enough data to test.
0
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 40515579
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
 

Author Comment

by:aej1973
ID: 40518122
Arnold, I cannot thank you enough, this is exactly what I was looking for, Amazing!!!

Thank you,
A
0
 

Author Closing Comment

by:aej1973
ID: 40518124
Excellent!!!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question