Solved

PHP multidimension array...

Posted on 2014-12-21
18
93 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
 
LVL 76

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 76

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 82

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 76

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 76

Expert Comment

by:arnold
ID: 40512329
Please also post the show create table for my_orders and order_city
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

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

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 76

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 76

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now