Creating unique multidimensional arrays 1

[tewari68]

Hi,
I have a table as follows

+------------------+------------+-----------+-----------+-----------+-------+
| entityName       | optionName | entity_id | folder_id | option_id | value |
+------------------+------------+-----------+-----------+-----------+-------+
| default_domain   | list       |         8 |         1 |         1 | 1     |
| default_mailbox  | view       |         9 |         1 |         4 | 1     |
| domain.net       | view       |       386 |         1 |         4 | 1     |
| mailbox@domain.net | list       |       419 |         1 |         1 | 0     |
| mailbox@domain.net | list       |       419 |         2 |         1 | 0     |
+------------------+------------+-----------+-----------+-----------+-------+

I have to create an array which contains for each folder_id all the optionName and value, values and additionally if the same optionName has been defined for default_mailbox, or default_domain or domain.net or mailbox@domain.net then the values for the default_mailbox should be overwritten by the default_domain, default_domain by domain.net, domain.net by mailbox@domain.net.
Referring to the table above the array should have values for folder_id 1, optionName(mailbox@domain.net) = list, value = 0, optionName(domain.net) = view, value = 1.

for folder_id 2, optionName(mailbox@domain.net) = list, value=0. and so on.

I cannot think how to create such an array and then retrieve the desired values.

Appreciate any help in this regards.
Thanks,
tewari

 

[jpadie]

might this work for you?

<?

$arr = array();
$result = mysql_query("Select * from $table order by folder_id ASC, entity_id DESC") or die (mysql_error());
while ($row = mysql_fetch_assoc($result)):
	switch ($row['entityName']):
		case "domain.net":
			if($row['optionName'] === $arr[$row['folder_id']]['mailbox@domain.net']['optionName']):
				$value = $arr[$row['folder_id']]['mailbox@domain.net']['value'];
			else:
				$value = $row['value'];
			endif;
		break;
		case "default_mailbox":
			if($row['optionName'] === $arr[$row['folder_id']]['domain.net']['optionName']):
				$value = $arr[$row['folder_id']]['domain.net']['value'];
			else:
				$value = $row['value'];
			endif;
		break;
		case "default_domain":
			if($row['optionName'] === $arr[$row['folder_id']]['default_mailbox']['optionName']):
				$value = $arr[$row['folder_id']]['default_mailbox']['value'];
			else:
				$value = $row['value'];
			endif;		
		break;
		default:
		$value = $row['value'];
	endswitch;
	//assign the values to a new variable
	$arr[$row['folder_id']][$row['entityName']]=array("optionName"=>$row['optionName'], "value"=>$value);
endwhile;
echo "<pre>";
print_r($arr);
echo "</pre>";
?>

there is probably a way to do this within sql too. the key to this working is the orderby clause. if the entityid can change or you have not posted the universe of all entityid's then the above code might not work as expected.

overall if you need to do this much manipulation to a recordset to get it into a usable array it's often a sign that your table schema or business-logic is not well designed.

 

[Tarwn]

Ihave to admit your explanation is a little confusing, let me rephrase and if I am wrong (I have only had one cup of cofee) you can correct me:

You need an array of data values that contain one entry for each folder_id, option_id and value and contains the highest entityname/id based on the list of overrides you provided.

if thats the case then your resulting array should contain the following records:

| mailbox@domain.net | list       |       419 |         1 |         1 | 0  
| domain.net       | view       |       386 |         1 |         4 | 1     |
| mailbox@domain.net| list       |       419 |         2 |         1 | 0     |

Bsaed on the entityId's I would think that default_mailbox would override default_domain, but you have listed the other way around.

So, yes, you should be able to do this in your SQL statement. Basically you will want to do a select with an inner select, something like:

SELECT folder_id, option_id, optionName, value, MAX(Case entity_id WHEN 9 Then 0 Else entity_id) AS entity_id
FROM YourTable
GROUP BY folder_id, option_id, optionName, value

If you absolutely must have the entityName as well, you can either build another Case When structure in your select, doa sub-select, etc. This returns all of the data exactly how you need it from your query and should then be very easy to manage for creating an array if you still need it.

Warning: That Query above will probably not work out of the box, I'm tired, I'm decaiffeinated, and I have touched more MSSQL than MySQL recently (assuming your using MySQL).

Please let us know if I misread your conditions.

-T

 

[tewari68]

Hi jpadie,
Tried you solution and it worked with a little bit of modification.


Thanks

 

[jpadie]

glad to help.

can you share your solution? it may help people with similar problems in the future.