Multidimensional Arrays

[Rmazin]

I am trying to return some query results in a multidemensional array.

The first dimension is the name of the usergroups on my site
The second is the names of the members of that group
The third dimension is some extra information on each user

I have 3 seperate arrays that all return correctly compiled from 3 queries. Indivudally printed to the page they print the proper data.

$group = array();
$names = array();
$data = array(); 

Here is where I am having problems grouping them:

$rpt = array($group => array($names) => array($data)); 

I am getting an error:

Parse error: syntax error, unexpected T_DOUBLE_ARROW, expecting ')'

Thanks in advance for any help.

[AnthonySterling]

Try:-

<?php
$rpt = array($group,$names,$data);
?>

[Rmazin]

Try:-

<?php
$rpt = array($group,$names,$data);
?>

I tried that, it just returns the word Array.

[buldozerce]

keys can only be integers or strings. Keys can not be arrays.

[AnthonySterling]

Have you tried...

<?php
print_r($rpt);
?>

[Rmazin]

Have you tried...

<?php
print_r($rpt);
?>

This kind of works. It prints the array data but not in a "text" format.

[AnthonySterling]

How exactly do you want the data formatted? Your OP only requested the data in a single array, let me know how you want it and i'll take another look.

[Rmazin]

How exactly do you want the data formatted? Your OP only requested the data in a single array, let me know how you want it and i'll take another look.

I basically need to email a report to our moderators. so the data would display as follows:

Group 1
Randy M
data
data data
data data data
Bob C.
data
data data

Group 2
James J.
data
data data
data data data
data data data data
Bill Q.
data
data data
data data data

etc.

[Cups]

I have 3 seperate arrays that all return correctly compiled from 3 queries.

Perhaps we should look at how you are doing that, it looks like an sql question though.

[AnthonySterling]

Shouldn't you be obtaining all this data in one SQL query? Either way, we'll need to sample data too.

[Rmazin]

Perhaps we should look at how you are doing that, it looks like an sql question though.

I am doing it like this:

$mods= "select * from users where type='moderator''";
$res = mysql_query($mods);

while($row_recip= mysql_fetch_assoc($res)){

$grp= "select * from groups where mod ='".$row_mods['id']."' and active ='y'";
$res1 = mysql_query($grp);

$group = array();

while($row_grp = mysql_fetch_assoc($res1)){

$group[] = $row_group['group_name'];

$mems = "select * from usres where group_id ='".$row_grp['id']."' and activated ='yes'";
$res2 = mysql_query($mems);

$names = array();

while($row_mems = mysql_fetch_assoc($res2)){

$names[] = $row_mems['fname']." ".$row_mems['lname'];


$dt= "select * from data where usr_id = '".$row_mems['id']."'";
$res3 = mysql_query($dt));

$data = array();

while($row_dt = mysql_fetch_assoc($res3)){

$data[] = $row_items['mod_name'].": ".$row_items['series'].": ".$row_items['item_title'];
}
}
}
}

$group = implode('<br />', $group);
$names = implode('<br />', $names);
$data = implode('<br />', $data); 

That lists the information correctly on screen. However, I removed the implode() functions to try and get them into a multidimensional array.

$rpt = array(array($group) => array($names) => array($data)); 

[Cups]

So you have 3 tables, groups, users (usres?), data.

You have got quite a few options about how you structure your database, but lets stick with what you have got.

If we can use an sql "join" on those 3 tables somehow then you would only have to do one trip to the database rather than those nasty nested loops containing queries.

Even if we got it down to 2 trips to the database it would likely make things simpler.

Because you are using "select *" it is difficult to give you absolute answers.

Can you post the field names? ( or show your db schema )

What I am guessing, like SBuk, is that yours' is primarily an sql question rather than a PHP question at this point. When you come to display the output - then it will likely be a simple PHP loop that outputs what you want.

You make two simple, but major mistakes which all of us have done.
1 "select * " should be avoided when you can
2 nested loops containing sql queries can grind your server to a halt if you aren't careful

This ends up with you trying to bend PHP to do some processing which your database can do far more effectively.

Post those field names if you can, or some aliases if you prefer.

[AnthonySterling]

Oh my, that's some serious looping you're doing there.

I think were going to definitely have to look at how you obtain your data before we continue; unfortunately my SQL skills are pretty much non-existent.

Hopefully one of the other members could address this for you, good luck!

[Rmazin]

So you have 3 tables, groups, users (usres?), data.

You have got quite a few options about how you structure your database, but lets stick with what you have got.

If we can use an sql "join" on those 3 tables somehow then you would only have to do one trip to the database rather than those nasty nested loops containing queries.

Even if we got it down to 2 trips to the database it would likely make things simpler.

Because you are using "select *" it is difficult to give you absolute answers.

Can you post the field names? ( or show your db schema )

What I am guessing, like SBuk, is that yours' is primarily an sql question rather than a PHP question at this point. When you come to display the output - then it will likely be a simple PHP loop that outputs what you want.

You make two simple, but major mistakes which all of us have done.
1 "select * " should be avoided when you can
2 nested loops containing sql queries can grind your server to a halt if you aren't careful

This ends up with you trying to bend PHP to do some processing which your database can do far more effectively.

Post those field names if you can, or some aliases if you prefer.

Field names for the tables by structure of the output I need is as follows:

Table: users
Purpose: get the moderators email address and get the id so that we can match groups which fall under that moderator.
Fields:
id
fname
lname
email

Table: groups
Purpose: stores group information.
Fields:
id
group_name
group_moderator_id

Table: users
Purpose: matches registered users to groups
Fields:
id
fname
lname
group_id
moderator_id

Table: data
Purpose: stores user data
Fields:
usr_id
data_text
date_submitted


Thanks.

[Cups]

Hi,

I want to help but there are some things not making sense to me.

Why do you seem to have 2 tables called users?

Are the tables all in the same database?

Do you really have 4 tables, or do you have 3?

Your sample sql contains things like this:

$grp= "select * from groups where mod ='".$row_mods['id']."' and active ='y'";

so why aren't you showing columns like mod, active, activated?

Can you get a dump of the schema, if you are using PhpMyAdmin you can press export, choose Structure only, and you will see something similar to this:

-- 
-- Table structure for table `people`
-- 

CREATE TABLE `people` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `tel` varchar(20) NOT NULL default '',
  `job_ref` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=84 ;

Field names for the tables by structure of the output I need is as follows:

But sadly that isn't the information we need to help you join up your tables for your queries.

You can chop out any lines containing columns you have not mentioned in your example and don't post anything which may compromise you.

[Rmazin]

Hi,

I want to help but there are some things not making sense to me.

Why do you seem to have 2 tables called users?

Are the tables all in the same database?

Do you really have 4 tables, or do you have 3?

Your sample sql contains things like this:

$grp= "select * from groups where mod ='".$row_mods['id']."' and active ='y'";

so why aren't you showing columns like mod, active, activated?

Can you get a dump of the schema, if you are using PhpMyAdmin you can press export, choose Structure only, and you will see something similar to this:

-- 
-- Table structure for table `people`
-- 

CREATE TABLE `people` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `tel` varchar(20) NOT NULL default '',
  `job_ref` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=84 ;

But sadly that isn't the information we need to help you join up your tables for your queries.

You can chop out any lines containing columns you have not mentioned in your example and don't post anything which may compromise you.

Okay, sorry for not being clear.

1. It queries the users table twice overall. The scenario is as follows:

First, it queries the users table to see who the moderators are and to get their emails so that they can be sent the report.

Second, it queries the groups table to see which groups those moderators are in charge of.

Third, queries the users table again to see which non-moderator users are members of that group. It then queries the data table for any data on those users.

2. All tables are in the same DB.

3. I have 3 tables users, groups and data, however it queries users a second time to find info on group members who are not the moderators.

4. the column "mod" is actually moderator_id under the group table in my last post. As far as skipping the activated and active columns, I just overlooked them, sorry about that. active is in the groups table and activated is in the users table.

CREATE TABLE `users`(
`id` int(11) NOT NULL auto_increment,
`fname` varchar(20) NOT NULL default '',
`lname` varchar(20) NOT NULL default '',
`email` varchar(100)NOT NULL default ''
`permissions` varchar(20) NOT NULL default '',
`group` varchar(20) NOT NULL default '',
`activated` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=84 ;


CREATE TABLE `groups`(
`id` int(11) NOT NULL auto_increment,
`group_name` varchar(100) NOT NULL default '',
`group_moderator_id` varchar(20) NOT NULL default '',
`active` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=84 ;

CREATE TABLE `data`(
`id` int(11) NOT NULL auto_increment,
`usr_id` varchar(100) NOT NULL default '',
`data_text` LARGETEXT NOT NULL default '',
`date_submitted` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=84 ;

[Cups]

OK, thanks for explaining.

The first thing to do is to try and get a list of groups, and their moderators.

$qry = "
SELECT
g.name as group_name,
concat( u.fname, ' ', u.lname) as moderator
FROM 
groups as g
INNER JOIN users as u
ON g.group_moderator_id = u.id
WHERE
g.active = 'y' 
";

Try that first ... there might be an sql guru along in a moment anyway ...

Send that query in, and var_dump the results to have a look at the subsequent array - or paste it into PhpMyAdmin (PMA).

That should give you a result set with each each moderators name preceded by the group that moderator works on. ( given that in your example each mod can only be in one group - or can each mod work on more than one group?)

[Cups]

I missed a comma on line 2 of that statement BTW, its corrected now, check again.

[Cups]

I suppose the next thing to work out is how to get the data which belongs to users brought out depending on if the group was in the first query.

Is it likely that a user who is not activated can have possibly created any data?

Especially as you seem to be collecting what has changed for a time period, say today?

I can work out how to do this query, which would be nested but will only be called as many times as you have groups.

(sorry for this stream of consciousness type of rant, its the only way I can work when I am doing this in my head)

We might then get the 2 queries joined together and polished up.

If you add this new line in the first query;

$qry = "
SELECT 
  g.id as group_id
, g.name as group_name
, concat( u.fname, ' ', u.lname) as moderator

... as before

then you'd (hopefully) have a result set like this:

1 Group1 Randy M
1 Group1 Bob C
2 Group2 James J
2 Group2 Bill Q

So foreach of those results you could get the data by doing this.

Where $rows is your result set, and you are retrieving the results as assoc arrays.

$sql="
SELECT d.data_text
FROM data as d
INNER JOIN users as u
ON d.usr_id = u.id
WHERE u.group = $row['group_id']
";

You can test whether that works by sticking that query in PMA and substituting $row['group_id'] with the number of a populated group, like, 1.

Now of course we need to look at why you seem to be storing dates in a VARCHAR field.

What kind of dates are you storing?

I hope some of this is working, been called for dinner, gotta go.

Maybe someone else can improve on my stab.