Convert a PHP array to an SQL statment?

Question

I'm trying to convert an array (key/value) to be an SQL statement.

I'm using MYSQLi like such:

if(!$result = $mysqli->query($sql)){throw new Exception("SQL Failed ".__file__." on line ".__line__.":\n".$sql);}

I have an array like such:

Array
(
[database] => Array
    (
        [cms_network] => Array
            (
                [network_id] => 61
                [network_name] =>
                [network_server_mac_address] => 00:1b:eb:21:38:f4
                [network_description] => network
                [network_thermostat_reporting_rate] => 5
                [network_server_reporting_rate] => 5
                [network_data_poll_rate] => 5
                [network_created_by] => 38
                [network_modified_by] => 1
                [network_network_id] => 8012
                [network_language] => en
                [network_hotel_id] => 68
                [network_channel] => 0
                [network_deleted] => 0
                [network_reported_network_id] => 8012
                [network_rooms] => 4
            )

    )

)

How can I convert [cms_network] to look like this:

$sql = "UPDATE cms_network set network_id='61', network_name='',      
network_server_mac_address = '00:1b:eb:21:38:f4', .... WHERE network_id='61'"

I'm more interested in knowing how to concatenate the key=>value pair of the array to be key='value' in my select statement.

Thanks for the help!

assuming that you set the inline array to the variable $cms_network, you can use foreach ($cms_network as $key=>$value) {} and build your query string. — dev7
– dev7, Commented Feb 5, 2014 at 0:54
I'd like to use implode() but I can't figure out how to blend the values in with the keys. When imploding the array, I only get the values: 61,,00:1b:eb:21:38:f4,network,5,5,5,38,1,8012,en,68,0,0,8012,4. I would need to implode the keys as well and map them together somehow. — Drace
– Drace, Commented Feb 5, 2014 at 0:55
implode() doesn't do associative array. You could use array_walk() but that would be an overkill. — dev7
– dev7, Commented Feb 5, 2014 at 0:59

autobottodoggo · Accepted Answer · 2014-02-06 15:07:04Z

2

If you use the VALUES syntax, you could do it in one fell swoop.

mysql_query("
UPDATE MyTable
( . implode(',', array_keys($array['database']['cms_network'])) . ")
VALUES ('" . implode("','", $array['database']['cms_network']) . "')
");

This, of course, assumes that the data is already escaped.

EDIT: Tidier version that's easier to read and maintain:

$fields = implode(',', array_keys($array['database']['cms_network']));
$values = implode("','", $array['database']['cms_network']);
mysql_query("UPDATE MyTable ($fields) VALUES ('$values')");

edited Feb 6, 2014 at 15:07

answered Feb 5, 2014 at 1:17

autobottodoggo

7071 gold badge9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Drace Over a year ago

Thanks, I wanted to simplify using implode ! all my data is already controlled since it comes from another Database on another server (which has it's data protected). Thanks again!

Community · Accepted Answer · 2023-11-17 20:12:06Z

2

I suggest you populate an array with formatted key/value pairs, then implode them at the end. This is an easy way to add the required , between each key/value:

$fields = array();
foreach($array['database']['cms_network'] as $key => $value) {
    // add formatted key/value pair to fields array
    // e.g. format: network_id = '26'
    $fields[] = $key . " = '" . $value . "'";
}
$fields = implode(', ', $fields);
// build your query
$query = "UPDATE cms_network SET " . $fields . " WHERE network_id = " . $array['database']['cms_network']['network_id'] . " LIMIT 1";
// process it...

This will (SQL wise) be inserting every value as a string, which is obviously incorrect with integer columns etc. It should still work anyway, but if not you'll need to put in a conditional statement for whether to wrap the value in quotes or not, like this:

foreach(...) {
    if(is_numeric($value))
        $fields[] = $key . ' = ' . $value;
    else
        $fields[] = $key . " = '$value'";
}

Although this should probably relate to your database column type rather than the PHP variable type. Up to you, they should work fine with quotes around integers.

edited Nov 17, 2023 at 20:12

CommunityBot

11 silver badge

answered Feb 5, 2014 at 1:07

scrowler

24.4k10 gold badges67 silver badges99 bronze badges

1 Comment

zerkms Over a year ago

It worth to mention that you need to escape the string values properly, in this case with mysql_real_escape_string, otherwise you cannot guarantee the query to be syntactically correct

OfirH · Accepted Answer · 2014-02-05 00:56:17Z

1

This should work.

$update_query = "UPDATE `cms_network` SET ";
$count = 0;
foreach($array['database']['cms_network'] as $key => $value)    {
    if ($count != 0)    {
        $update_query = $update_query.",".$key."=".$value;
    }   else    {
        $update_query = $update_query.$key."=".$value;
    }
    $count++;
}
$update_query = $update_query." WHERE ".cms_network."=".$array['database']['cms_network'];
mysql_query($update_query);

answered Feb 5, 2014 at 0:56

OfirH

6572 gold badges8 silver badges19 bronze badges

3 Comments

zerkms Over a year ago

It will generate something like network_server_mac_address=00:1b:eb:21:38:f4 which is not a valid statement

OfirH Over a year ago

He can fix it on his own with implode, explode or anyway he want to insert this data.

OfirH Over a year ago

It won't :) sorry got mistaken with another function.

Collectives™ on Stack Overflow

Convert a PHP array to an SQL statment?

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related