Jump to content

get two results in same table row


rondog

Recommended Posts

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:

query1.jpg

 

 

This is what I would like it to do:

query2.jpg

 

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

Link to comment
https://forums.phpfreaks.com/topic/214503-get-two-results-in-same-table-row/
Share on other sites

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')

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

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?

the first rule to follow:

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

 

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()

 

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?

 

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'

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.