get two results in same table row

[rondog]

when I do a "SELECT * FROM system_disks WHERE system_id = 'aNumber'" and their is more than one result with that system_id, I get more than one result obviously.

 

I essentially need two results in one row. I've took a screen shot of what it is doing and what I need it to do.

 

The blurred row is a different system_id

 

This is what the while ($row = mysql_fetch_array($query)) is currently doing:

 

 

This is what I would like it to do:

 

Sorry if this is a dumb question, I've been coding the past 48 hours and my brain is fried 

[chintansshah]

You should first save your result in one variable and then club then array with same COLUMN.

Use below function

function club_array($contents,$parent_id)
{
	if(count($contents)>0)
	{	
		$list = array();
		$children = array();
		foreach ($contents as $v ) 
		{
			$pt = $v[$parent_id];
			@$list = $children[$pt] ? $children[$pt] : array();
			array_push( $list, $v );
			$children[$pt] = $list;
		}
		$contents = $children;

	}
	return $contents;
}
club_array($result_arr,'module_id')

[rondog]

I've tried using your method and I am getting an "Uninitialized string offset"

[mikosiko]

another option.. (replace the field name for the correct ones)

 

SELECT GROUP_CONCAT(CAST(diskqty AS CHAR)) AS diskqty,
       GROUP_CONCAT(diskmake) AS diskmake,
       GROUP_CONCAT(diskmodel) AS diskmodel,
       GROUP_CONCAT(CAST(disksize AS CHAR)) AS disksize,
       GROUP_CONCAT(CAST(disksrno AS CHAR)) AS disksrno
FROM system_disks 
WHERE system_id = 'aNumber'
GROUP BY system_id

 

Notes:

- the usage of CAST() is assuming that those fields are numerics... otherwise just remove it

- if you don't want to repeat identical values like p.e: diskmodel change that line to:

           

GROUP_CONCAT(DISTINCT diskmodel) AS diskmodel,

- and last... be careful with the group_concat_max_len system variable which has a default of  1024

[rondog]

ok this is more like what I was thinking. I modified the query to match mine like you said, but the returned result is false so the query is failing.

$sql 	= "
SELECT GROUP_CONCAT(system_disk_qty AS CHAR) AS system_disk_qty,
       GROUP_CONCAT(system_disk_size AS CHAR) AS system_disk_size
FROM system_disks 
WHERE system_id = 2
GROUP BY system_id";

$query 	= mysql_query($sql);

if ($query)
{
echo "succeed";
}
else
{
echo "failed";
}

Any ideas?

[mikosiko]

the first rule to follow:

- TEST the query before to use it in PHP.... I did and is working 

 

you error was that you removed the CAST() function but you did it wrongly .... this sentence

SELECT GROUP_CONCAT(system_disk_qty AS CHAR) AS system_disk_qty,
       GROUP_CONCAT(system_disk_size AS CHAR) AS system_disk_size
FROM system_disks 
WHERE system_id = 2
GROUP BY system_id;

 

should be

SELECT GROUP_CONCAT(system_disk_qty) AS system_disk_qty,
       GROUP_CONCAT(system_disk_size) AS system_disk_size
FROM system_disks 
WHERE system_id = 2
GROUP BY system_id;

 

and are you sure that the field system_disk_qty IS NOT A NUMBER?  ... if it IS a number you have to use CAST()

 

[rondog]

Ah ok I gotcha..It is succeeding now. The field is set as varchar so no it isnt a number exactly. I guess its more of a "2" than a 2 if that makes sense.

 

Well you are a life saver. That works beatifully. I had never used group_concat til now. That is very useful.

 

The return value is a string obviously so if I want to display '4 x 1 TB, 2 x 800 GB', am I better off just using explode on the values?

 

[rondog]

Ok looking at this query is going to look scary. It is generated through a number of loops and what not. The issue I am running into is their wont always be a controller in the system, so if their happens to not be, the entire query fails if their is nothing in the system_controllers table that has a system_id of  cam_systems.id.

<?php
$q = "
SELECT
cam_systems.id,
cam_systems.cam_systems_warranty,
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_disks.system_disks_serial_no) AS system_disks_serial_no
FROM 
cam_systems,
system_controllers,
system_cpus,
system_disks,
system_macip,
system_memory,
system_power
WHERE
cam_systems.location_id = '1'
AND system_controllers.system_id = cam_systems.id
AND system_cpus.system_id = cam_systems.id 
AND system_disks.system_id = cam_systems.id 
AND system_macip.system_id = cam_systems.id 
AND system_memory.system_id = cam_systems.id 
AND system_power.system_id = cam_systems.id
GROUP BY 
cam_systems.id 
LIMIT 0,10";
?>

 

I dont even know where to start right now as to what I should do. Its almost like I need to do an if in that statement in order to add that 'AND system_controllers.system_id = cam_systems.id'

[rondog]

also the reason I am selecting from all those different tables is because the user can turn on and off what fields they want to display. Right now I just have the disk information checked, but I could potentially have say some memory fields checked