SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2008
    Location
    Bozeman, MT
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question PHP + MySQL: Possible to get array keys as a value?

    This is perhaps a really dumb question, but I've been unable to find an answer after quite a bit of searching.

    Is there a way to query MySQL (I'm using the ezSQL class) and have it return the PHP array with the keys set as a specific value?

    For example, if I had a table called "Customers" with the columns "CustomerID, Name, State, ZIP", how could I query MySQL so that it will return the array with the 'CustomerID' value set as the key:

    Code:
    Array(
        [CustomerID] => Array (
            [Name] => Value
            [State] => Value
            [ZIP] => Value
            )
        )
    Instead of this:

    Code:
    Array(
            [0] => Array(
            [CustomerID] => Value
            [Name] => Value
            [State] => Value
            [ZIP] => Value
            )
        )
    Any thoughts, suggestions, comments are most definitely appreciated - My genuine thanks in advance to all who reply.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You can build such an array from the result set

    PHP Code:
    $array = array();
    while (
    $row mysql_fetch_assoc($result)) {
      foreach (
    $row as $key => $value) {
        
    $array[$key][] = $value;
      }

    Last edited by Dan Grossman; Jun 30, 2009 at 17:40.

  3. #3
    SitePoint Member
    Join Date
    Jun 2008
    Location
    Bozeman, MT
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Thank you, Dan - I had considered going that route, but wasn't sure if there was a method for doing it right in the MySQL query.

    In any case, your solution works perfectly, and I appreciate your time helping me out.

  4. #4
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is how I understood you wanted to re-organize the array (this assumes the first value is always customer id in your mysql query):

    PHP Code:
    $array = array();
    while(
    $row mysql_fetch_assoc($result))
    {
        
    $array[] = $row;
    }

    $organizedArray = array();
    foreach (
    $array as $innerArray)
    {
        foreach (
    $innerArray as $key => $value)
        {
            if (
    $key === 'customer_id')
            {
                
    $customerId $value;
                
    $organizedArray[$customerId] = array();
            }
            else
            {
                
    $organizedArray[$customerId][$key] = $value;
            }
        }

    prints (every array key is customer id and customer info under it):
    Code:
    Array
    (
        [1] => Array
            (
                [name] => test
                [state] => teststate
                [zip] => 12314
            )
    
        [2] => Array
            (
                [name] => test2
                [state] => test2state
                [zip] => 4444
            )
    
        [3] => Array
            (
                [name] => test3
                [state] => test3state
                [zip] => 3333
            )
    
    )

  5. #5
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can do this with PDO
    First instantiate the $pdo object, read the manual for how to do that here:
    http://us3.php.net/manual/en/pdo.construct.php

    Then:

    $sql = 'select CustomerID, Name, State, Zip from customer where CustomerID = :id';
    $sth = $pdo->prepare($sql);
    $sth->bindParam( ':id', $id );
    $sth->execute();
    $sth->fetchAll(PDO::FETCH_GROUP);

    The trick here is the PDO::FETCH_GROUP

    Your array will have the value of the first column as array key and the rest of columns are value of that array.

    By the way, you don't have to select just one customer, this was just an example. You can just as easily select all customers or customers whose State = 'NY', in which case you get array where array keys are customerID and values are arrays with the rest of customer data.

    Basically, you will have array that looks exactly how you wanted it to look. Just dump your ezSql and switch to PDO.
    I've been using DB class from pear, then MDB2 from pear for years, then finally dumped them in favor of PDO and it's been great, I'll never use anyther DB class again.

  6. #6
    SitePoint Member
    Join Date
    Jun 2008
    Location
    Bozeman, MT
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    Some really excellent replies here - I really appreciate all of you taking the time to share your expertise!

    I'm going to play around with both of the 2 new suggestions a bit later today and will post back with which one seems to work out best.

    Huge thanks to TeNDoLLA and Sharedlog.com!

  7. #7
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As shareddog said if you are using some database class, my vote would also go to PDO.


Tags for this Thread

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
  •