Need to merge arrays

PHP
1

Hi,

I have a couple of MySQL tables setup like so:

Table Name: location

Field key     Value
------------| --------------------------
0                Ongar, Essex
1                Richmond, London
2                Brentwood, Essex
3                Romford, Essex

Table Name: date

Field key     Value
------------| --------------------------
0                04/10/2012
1                10/11/2012
2                04/06/2012
3                05/09/2012

I’m currently looping through both tables like so:


foreach($_SESSION['cv_builder']['array_field_list'] as $table_name)
{
				$subset_array = $this->dbh->getAll("select field_key,value from ".TABLE_PREFIX."cv_builder_".$table_name." where sessionid='".mysql_real_escape_string($_SESSION['cv_builder']['sessionid'])."'", DB_FETCHMODE_ASSOC);
				
}

Now what i’m looking to achieve is to merge the arrays by the field_key field. So, the array I would like to end up with should look like so:

array(
[0] => 
    array(
        [location] => 'Ongar, Essex',
        [date] => '04/10/2012'
    )
[1] => 
    array(
        [location] => 'Richmond, London',
        [date] => '10/11/2012'
    )
[2] => 
    array(
        [location] => 'Brentwood, Essex',
        [date] => '04/06/2012'
    )
[3] => 
    array(
        [location] => 'Romford, Essex',
        [date] => '05/09/2012'
    )
)

Any idea how to get to this output?

Thanks.

2

Use a SQL Join…

SELECT location.field_key, location.value AS location, date.value AS ‘date’ FROM location LEFT JOIN date ON location.field_key = date.field_key

3

Best practice IMO is to handle anything and everything possible on the MySQL side before trying to fix something in a PHP loop. I’m not 100% sure, but you may run into a situation where someone starts complaining about using the alias of ‘date’.

4

I did that to match his array expectations, but I do agree, that using the alias of date could be problematic, and it would be best to use a different alias name (where possible).

5