Help with GROUP_CONCAT

[rondog]

I am running this query:

$q2 = "
SELECT 
cam_systems.id,
GROUP_CONCAT(system_disks.system_disks_qty) AS system_disks_qty,
GROUP_CONCAT(system_disks.system_disks_make) AS system_disks_make,
GROUP_CONCAT(system_disks.system_disks_model_no) AS system_disks_model_no,
GROUP_CONCAT(system_disks.system_disks_size) AS system_disks_size,
GROUP_CONCAT(system_memory.system_memory_qty) AS system_memory_qty,
GROUP_CONCAT(system_memory.system_memory_serial_no) AS system_memory_serial_no,
GROUP_CONCAT(system_memory.system_memory_manf) AS system_memory_manf,
GROUP_CONCAT(system_memory.system_memory_part_no) AS system_memory_part_no,
GROUP_CONCAT(system_memory.system_memory_size) AS system_memory_size
FROM
cam_systems,
system_disks,
system_memory 
WHERE 
cam_systems.location_id = '1' 
AND system_disks.system_id = cam_systems.id 
AND system_memory.system_id = cam_systems.id 
GROUP BY 
cam_systems.id LIMIT 0,10";

 

It almost works, but the problem I am running into is sometimes say a system has 2 different disks, therefore the group concat will group a field like system_disks_qty and it will show as :

 

2,4 as in it has 6 hard drives 2 of one type and 4 of the other. That's all good, the problem is say in system_memory, I only have one type, say 8 sticks of one type. The quantity shows up as 8,8. Its repeating the same value since it had to loop twice for the disks.

 

Below is what it is outputting. the first row is correct. The second and third are not. The disk info displays correctly, but the memory does not. It is showing the same result twice.

 

Disk Qty.

Disk Make

Disk Size

Memory Qty.

Memory Manf.

Memory Size/td]

5

seagate

500 GB

8

Kingston

1 GB/td]

3,3

seagate,western digital

1 TB,500 GB

16,16

Kingston,Kingston

2 GB, 2 GB/td]

2,4

WD,Seagate

1 TB, 800 GB

8,8

Kingston,Kingston

1 GB,1 GB/td]

 

Anyone have a suggestion?

[rondog]

I thought about using DISTINCT, but what if I had 3 hard drives of one type and 3 hard drives another type. Since their is the same amount wouldn't it only choose 3?

[mikosiko]

I don't want to sound harsh or anything similar,  but the original question that you posted here:

http://www.phpfreaks.com/forums/index.php/topic,311121.0.html

 

was clearly incomplete ...you asked for a solution considering one table and later you showed 7 tables... likewise in this post you are showing the same lack of information and showing just 3 tables instead of  7, therefore the solution that you got originally was based in your original question... somebody here has the signature

" The quality of the answer is directly proportional to the quality of the question" and that apply perfectly to this case.

 

With the right information I wouldn't have suggested the usage of GROUP_CONCAT even when you can use the DISTINCT, in the way that I suggested in my Notes in the original post, for specific columns (you have to decide in which ones make sense).

 

My suggestion now for you is to re-think your problem and how you are displaying the information... a screen like the one that you showed originally will look very cluttered with information of 7 tables.... you should think in a different way to show that information and maybe in that case get first all the relevant records/fields in an array and post-process it could make sense.

 

[rondog]

I've asked many questions here and I found the more complicated the code looks the fewer responses I get so yes, the original post was trimmed quite a bit.

 

Now, the reason for going after 7 tables... Above the list of systems is a customize fields button. This brings down about 40 check boxes. Whichever the user happens to check determines what information is displayed. I originally had it selected from all the tables by default, but managed to make it so it only selects from the tables that are relevant. In most cases a user isn't going to have more than a quarter of the check boxes selected since they will be looking for something specific. If they have all of them selected, then they will just have a really wide table. Not much I can do about that.

 

So with that said, before even getting to thinking about storing it in an array I need to get my SELECT statement working.

[fenway]

That's because you're joining a many-to-many relationship -- by GROUP BY has a DISTINCT option, too.

[rondog]

That's because you're joining a many-to-many relationship -- by GROUP BY has a DISTINCT option, too.

 

I'm a newb to mysql so forgive my naiveness

 

Can you elaborate on your response?

[fenway]

If you want the GROUP_CONCAT() expression to remove duplicates, use the DISTINCT modifier -- the refman covers the syntax.

[rondog]

If you want the GROUP_CONCAT() expression to remove duplicates, use the DISTINCT modifier -- the refman covers the syntax.

 

However if I do that and I have say 3 of one disk and 3 of another in the same system, it will only show 3. Not 3,3. My issue here is if their is a situation like this, anything with one item will be replicated twice.

[mikosiko]

again.... read the Notes in my answer to you first post... and in this thread my first answer to you have this paragraph too :

 

"....GROUP_CONCAT even when you can use the DISTINCT, in the way that I suggested in my Notes in the original post, for specific columns (you have to decide in which ones make sense)."

[rondog]

So I have to maybe get the SUM first.. damn I dont know this stuff is way over my head

[rondog]

does doing that seem about right?