[nakedintruder]

I've been trying all night at this and I'm still buggered.. here's what's going on simplified a bit, I've changed the group id to letters to make it easier to see what I'm after too.

The relevant part of the table of my database is set out like;

item id |    group id  |
--------+--------------+
   1    |       A      |
   2    |       A      |
   3    |       C      |
   4    |       D      |
   5    |       B      |
--------+--------------+

item id = id of each item in the current table
group id = foreign key to the group that item is part of from another table.

I want to output this like so;

• GROUP ID A
  • item id 1
  • item id 2
• GROUP ID B
  • item id 5
• GROUP ID C
  • item id 3
• GROUP ID D
  • item id 4

Without absolutely hammering the database as best I can...I've managed to get the output I want, but it's hitting the database too much, at the moment it's

1. connecting to the DB server.
2. connecting to the group table and finding the number of rows.
3. looping through every group to query it's name.
4. within that loop, connecting to the item table and finding all items in that group (the number of rows that have the group id of the loop iteration value).
5. still within that loop, another loop which outputs every item inside that group

If I only had 1 group and 1 item, this'd connect once then hit the database 4 times The other problem also is, it's looping by the iteration value and not the group id, so if a group was to be deleted then this script would output an empty template on that particular pass.

I've been trying with multi-dimensional arrays but having no luck and half-ideas I'm having don't look like they're going to hit the DB any less. I was also writing over arrays so in my most recent attempt have tried eval();

for ($i=1; $i <= $rows; $i++) {
$sql= "SELECT column1, column2 FROM items WHERE group_id='$i'"; 
$result = mysql_query($sql); // run the query
$myrow = mysql_fetch_array($result); // create an array from the results

$group_id = $myrow["group_id"];

// make an array of items for every group
$items[eval("return $group_id;").$i] = array ('column1' => $myrow["column1"], 'column2' => $myrow["column2"]);
};

and cos I'm knackered haven't fully thought through whether this last ditch attempt is even going to work either, so I'm nowhere nearer.

Anyone had to do this before and can put me on the right track? I'm making a right mess as you can see! Cheers

[cam]

Haha, what the fuck is that - a brain dump?

It sounds like this could be massively simplified on the sql side, as nothing should require four database accesses for "1 group and 1 item", but you're making it too much of an effort for me to understand what you actually want

Post the relevant db schemas and tell us in concise english exactly what you want the sql to do and i'll do it

[nakedintruder]

lol, sorry matey...hang on and I'll write it again, it was bout 4:30 am though! :p

[nakedintruder]

This is the db structure (with irrelevant parts removed)


The page I want to build is a list of all items in the database, headed by the group each item belongs to, It's not going well haha!

We can see here that;
group 1 contains 1 item, item 1
group 2 contains 2 items, item 2 and item 3.

The output I'm after is;

• group 1
  • item 1
• group 2
  • item 2
  • item 3

[Luke Redpath]

OK, the first step is to read the results into an array.

Then convert this into a new multidimensional array where each key is the group and each value is an array of items.

So, for example:

PHP Code:

$sql = "SELECT item_name, group_name
        FROM items, groups
        WHERE items.group_id = groups.group_ID";
$query = mysql_query($sql);
$combinedResults = array();

while($result = mysql_fetch_array($query)) {
    $combinedResults[$result['group_name']][] = $result['item_name'];
}

echo '<ul'>;

// now loop through the combined results
foreach(array_keys($combinedResults) as $groupKey) {
    echo '<li>'.$groupKey
    echo '<ul>'
    foreach($combinedResults[$groupKey] as $item) {
        echo '<li>'.$item.'</li>';
    }
    echo '</ul></li>';
}

echo '</ul>'; 


I've not tested it so I might have made the odd mistake but thats the general idea.

[nakedintruder]

Thanks mate, I'll have a proper look at it now, I was just reading about performing MySQL joins before you posted, and your script here has them in!

Really appreciate this, I'll be back in a bit to let you know how it goes.

[nakedintruder]

haha, ace! it works you beauty! I owe you big time for this mate, I was well buggered as you could probably tell, brilliant!! thanks a lot!

Using a while loop saved on the db loads, I was using for loops and having to find out the number of rows first and all sorts, messy.

Do you mind breaking down what this part is doing so I understand it better?

PHP Code:

while($result = mysql_fetch_array($query)) {
    $combinedResults[$result['group_name']][] = $result['item_name'];
} 


EDIT: Doh, how do I add extra columns into this? I added;

PHP Code:

while($result = mysql_fetch_array($query)) {
    $combinedResults[$result['group_name']][] = $result['item_name'].' - '.$result['item_type'];
} 


fine, but how would I add more? Sorry to pretty much have you doing this for me haha

[nakedintruder]

I suppose I can concatenate more columns onto the end in the item array to build the code I want. Otherwise it'll get nuts I think.

[Luke Redpath]

Ok, I'll do my best.

PHP Code:

while($result = mysql_fetch_array($query)) { 


OK, obviously this loops through the resultset, returning an associative array for each row in the resultset.

PHP Code:

$combinedResults[$result['group_name']][] = $result['item_name'];
} 


This is where the real action happens. It creates a new key in the $combinedResults array from the group name, then treats the resulting key's value as an array itself, using the [] operator to add a new value to this array, the item name.

After this loop has run, if you were to do a print_r on the $combinedResults array, you'd have an array that looked a bit like this:

Array
(
    [groupone] => Array
        (
            [0] => item1
            [1] => item2
            [2] => item3
        )
	[grouptwo] => Array
        (
            [0] => item1
            [1] => item2
            [2] => item3
        )
	[groupthree] => Array
        (
            [0] => item1
            [1] => item2
            [2] => item3
        )
)

So as you can see from this, we have an array of groups, and each group is an array of items, which makes the loop to build your list work.

By extra columns, I assume you mean how do you store more than just the item name within each group array? That's quite easy too. Do something like this:

PHP Code:

while($result = mysql_fetch_array($query)) {
    $combinedResults[$result['group_name']][] = array(
        'name' => $result['item_name'],
        'type' => $result['item_type']
    );
} 


Now you can use these in the list building loop like this (as an example):

PHP Code:

echo '<ul'>;

// now loop through the combined results
foreach(array_keys($combinedResults) as $groupKey) {
    echo '<li>'.$groupKey
    echo '<ul>'
    foreach($combinedResults[$groupKey] as $item) {
        echo '<li>'.$item['name'].'(type: '.$item['type'].')</li>';
    }
    echo '</ul></li>';
}

echo '</ul>'; 


Your array would now look like this:

Array
(
    [groupone] => Array
        (
            [0] =>  Array
				(
					[name] => item1
					[type] => myitemtype
				)
			[1] =>  Array
				(
					[name] => item2
					[type] => myitemtype
				)
        )
	[grouptwo] => Array
        (
            [0] =>  Array
				(
					[name] => item1
					[type] => myitemtype
				)
			[1] =>  Array
				(
					[name] => item2
					[type] => myitemtype
				)
        )
	[groupthree] => Array
        (
            [0] =>  Array
				(
					[name] => item1
					[type] => myitemtype
				)
			[1] =>  Array
				(
					[name] => item2
					[type] => myitemtype
				)
        )
)

Hope that helps!

[nakedintruder]

That's exactly it, thanks again mate you've saved the day

[Luke Redpath]

In fact, if you want access to each column in the row automatically, you could just do this:

PHP Code:

$combinedResults[$result['group_name']][] = $result; 


This would lead to the group_name column being duplicated (as both the top level array key and as a value in the array it contains but this is useful if you want access to all of your columns in one go).

[Goose25]

I have been looking for a multidimensional array process for sooooo long now that works as nicely as this and is scaleable too...

thx!