SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need to merge arrays

    Hi,

    I have a couple of MySQL tables setup like so:

    Code:
    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:

    PHP Code:
    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:

    PHP Code:
    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. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,245
    Mentioned
    157 Post(s)
    Tagged
    1 Thread(s)
    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. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cpradio View Post
    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
    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. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,245
    Mentioned
    157 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    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'.
    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).


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •