MySQL Query to return Multidimensional Array

PHP
1

I’m horrible with MySQL JOIN statements. I’m used to working with CakePHP that returns the data the way I currently need to do in WordPress.

I have two tables of form data:

Subs
| ID | form_id | email |
| 1 | 1 | nada@email.com

Data

| ID | sub_id | field | value
| 1 | 1 | name | Tim |
| 2 | 1 | website | www.test.com |
| 3 | 1 | created | 0 |

So, my form_id is 1. I only want to return certain fields from Data where field=‘created’ AND value=0.

Now, of course there will be multiple form submissions. I’m trying to write the query to return data like so:


Array (
	[1] => Array(
		name => Tim
	),
	[2] => Array(
		name => John
	)
)

Honestly, I’m not even 100% sure you can just do this via straight MySQL query - it seems CakePHP may actually run through a function that re-sorts the data based upon the model association.

In english, I want to say to MySQL:

Select * FROM `Data` where `Data`.`field`='created' AND `Data`.`value`=0 JOIN `Subs` ON `Subs`.`id`=`Data`.`sub_id` GROUP BY `Subs`.`id`...

but show all Data values. Using GROUP BY only returns one row for each ID.

This is the current query I’m running:

SELECT
				`Cforms_Data`.`sub_id`,
				`Cforms_Data`.`field_name`,
				`Cforms_Data`.`field_val`,
				`Cforms_Subs`.`email`
			FROM
				`cformsdata` AS `Cforms_Data`
			LEFT JOIN
				`cformssubmissions` AS `Cforms_Subs`
			ON (`Cforms_Data`.`sub_id` = `Cforms_Subs`.`id`)
			WHERE
				`Cforms_Data`.`sub_id` =
				ANY(
					SELECT `Cforms_Data`.`sub_id`
					FROM `cformsdata` AS `Cforms_Data`
					WHERE `Cforms_Data`.`field_name`="created"
					AND `Cforms_Data`.`field_val`=0
				)
				AND
					`Cforms_Subs`.`form_id` =5
				AND (
					`Cforms_Data`.`field_name` = "Cache" OR
					`Cforms_Data`.`field_name` = "Comments" OR
					`Cforms_Data`.`field_name` = "Contact Forms" OR
					`Cforms_Data`.`field_name` = "Generics" OR
					`Cforms_Data`.`field_name` = "Sitemaps" OR
					`Cforms_Data`.`field_name` = "Security" OR
					`Cforms_Data`.`field_name` = "SEO" OR
					`Cforms_Data`.`field_name` = "Spam" OR
					`Cforms_Data`.`field_name` = "Stats" OR
					`Cforms_Data`.`field_name` = "Wordpress Theme"
					)
			;

The data is returned with a numeric array starting at 0; I have to run through a PHP function to sort the data how I want. Would love to eliminate that step for optimization.

Thanks in advance.

2

You can’t return a multidimensional array with MySQL. You will need to manually format the results in php. Group by generally isn’t the best answer for two reasons. One being there is a limit before truncation will occur. The other is it adds unnecessary overhead to the query.

3

Here is an example of how it could be done with sample data.


<?php

$rows = array(
	array(
		'sub_id'=>1
		,'field_name'=>'name'
		,'field_val'=>'Tim'
		,'email'=>'nada@email.com'
	)
	,array(
		'sub_id'=>1
		,'field_name'=>'website'
		,'field_val'=>'www.test.com'
		,'email'=>'nada@email.com'
	)
	,array(
		'sub_id'=>1
		,'field_name'=>'created'
		,'field_val'=>'0'
		,'email'=>'nada@email.com'
	)
	,array(
		'sub_id'=>2
		,'field_name'=>'name'
		,'field_val'=>'John'
		,'email'=>'john@email.com'
	)
	,array(
		'sub_id'=>2
		,'field_name'=>'website'
		,'field_val'=>'www.test2.com'
		,'email'=>'john@email.com'
	)
	,array(
		'sub_id'=>2
		,'field_name'=>'created'
		,'field_val'=>'0'
		,'email'=>'john@email.com'
	)
);

$data = array();

foreach($rows as $row) {
	
	// create unique form element
	if(!isset($data[$row['sub_id']])) {
		$data[$row['sub_id']] = array(
			'email'=>$row['email']
			,'sub_id'=>$row['sub_id']
			,'fields'=>array()
		);
	}
	
	$data[$row['sub_id']]['fields'][$row['field_name']] = $row['field_val'];
}

echo '<pre>',print_r($data),'</pre>';

?>
4

Someone should create full write-up discussing this topic on the php basics thread. Its such a common a question it would be nice to just point someone to how its done. Not only that but give them a chance to apply the concept to their problem rather than being spoon fed an answer.

5

oddz, that’s what I had been doing but I was hoping I could reverse the procedure, so to speak. Studying CakePHP’s method I didn’t see there was a detailed SQL query to handle it. So, I figured it was being done via PHP but wanted to make sure.

Thanks again