SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    347
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Turning an array into (part of) a MySQL query string

    I frequently need to turn a PHP array such as:
    PHP Code:
    $cols = array ( [access] => [card] => [child] => [cot] => [dinner] => [ground] => [allyear] => [pets] => [wifi] => )
     
    // derived from 'print_r', I'm aware this is not how one would construct the array 
    into a string (part of a query string for MySQL):
    PHP Code:
    $cols_new "access = 'N', card = 'N', child = 'Y', cot = 'Y', dinner = 'N', ground = 'Y', allyear = 'Y', pets = 'N', wifi = 'Y'" 
    I've done it with a FOREACH loop, but I'm just curious to know if there's a better way (e.g. an array function I've missed).
    Implode just gives me a string of the values. "N, N, Y, Y, N, Y, Y, N, Y". I've wondered about 'Array_walk' but even if it were to work there will be at least as many lines of code as with the FOREACH loop:
    PHP Code:
        $cols_new '';
        foreach (
    $cols as $key => $value) {
            
    $cols_new .= $key " = '" $value "', ";
        }
        
    $cols_new substr($cols_new0, -2);  // remove trailing comma and space 
    I am using this procedure quite freqently, so if there's a better/shorter way it could be handy when I come to anonymise it.
    In short what I'm looking for is:
    PHP Code:
    $cols_new someExistingPHPFunction($cols); 
    Failing which I'll have to write my own based on what I've done so far.
    Last edited by ramasaig; Sep 12, 2011 at 05:07. Reason: clarify origin of the arrays
    Tim Dawson
    Isle of Mull, Scotland

  2. #2
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,329
    Mentioned
    191 Post(s)
    Tagged
    4 Thread(s)
    I think IMPLODE() is exactly what you need. The resultant string can become the VALUES() part of a SQL query.
    If I recall correctly, you can also apply IMPLODE() to an array of just keys and get the SET() part of your SQL statement also.
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Git is for EVERYONE
    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    implode the array_keys for the field names, and implode the array_values for the...well, values.

  4. #4
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    347
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your replies

    The only syntax I know for UPDATE is "UPDATE tablename SET column1 = 'value1', column2 = 'value2',... WHERE...". This means I need a string "column1 = 'value1', column2 = 'value2',..." which I'm currently getting via my FOREACH.
    Implode just gives me a comma separated string of values, which isn't what I need here.

    I think your suggestion would work for INSERT, with the syntax "INSERT INTO tablename (column1, column2,...) VALUES ('value1', value2',...) WHERE..." (grouping all the column names and then all the values).
    Tim Dawson
    Isle of Mull, Scotland

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Sorry, yes. For an UPDATE query this wont work. A most frustrating thing.
    Foreach is pretty much the best way to do it. You could probably get there using some complex series of array functions and implodes, but...

  6. #6
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Prepared statements makes it a bit easier since you don't have to worry about quotes and escaping.
    PHP Code:
      function update($tableName,$keyName,$data)
      {
        
    // Build set list
        
    $set '';
        foreach(
    array_keys($data) as $name)
        {
          if (
    $name != $keyName)
          {
            if (
    $set$set .= ', ';
            
    $set .= $name ' = :' $name;
          }
        }
        
    $sql "UPDATE {$tableName}\nSET {$set}\nWHERE {$keyName} = :{$keyName};";

        
    $stmt $this->pdo->prepare($sql);
        
    $stmt->execute($data); 
    But with a few mods you could stick the values directly into the sql statement.

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure how that eliminates the need for a foreach loop when it ... uses... a foreach loop...itself?

  8. #8
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    I'm not sure how that eliminates the need for a foreach loop when it ... uses... a foreach loop...itself?
    The example posted does not. I edited the post accordingly. The post provides a more or less general solution for doing a sql update of one record.

    However, depending on how the app is organized, you could write one sql statement for each table
    $sql = 'UPDATE xxx SET col1 = :value1, col2 = :value2 WHERE ...';
    After which you could just pass your array of values to the execute statement. No foreach required.


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
  •