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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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'

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.