CODEWITHSUNDEEP
phpmysqlarrayspdo
Saidur Rahman
Saidur Rahman

Reputation: 422

PHP Build Statement from Array

I will keep it short and quick. Point me at the right direction or tell me how to do it.

I have an array (shown below) - I want to be able to replace the value of ? with the element from 2nd array. So the first ? gets replaced with 1st value from array 2 and so on.

I am not rolling up my own PDO and codebase is very old and is not using a PDO at the moment. Just need to know how I can take this below array and prepare and bind to make it to into a quick sql statement:

Update tblA set name = 'SRTest12', agency = 'AUS', feature_tag = 'SRTest1', sart_date = '2000-01-01 00:00:00', end_date = '2050-12-31 00:00:00', ongoing = '0', hide = '1' where id = 165

Var Dump Output

>     array(2) {
>       [0]=>
>       string(126) "UPDATE tblA SET name = ?,agency = ?,feature_Tag = ?,start_Date = ?,end_Date = ?,hide_text = ?,ongoing =
> ? WHERE id = ?"
>       [1]=>
>       array(8) {
>         [0]=>
>         string(8) "SRtest12"
>         [1]=>
>         string(5) "AUS"
>         [2]=>
>         string(7) "SRTest1"
>         [3]=>
>         string(19) "2000-01-01 00:00:00"
>         [4]=>
>         string(19) "2050-12-31 00:00:00"
>         [5]=>
>         string(1) "0"
>         [6]=>
>         string(1) "1"
>         [7]=>
>         string(3) "165"
>       }
>     }

Print_r Output

Array
(
    [0] => UPDATE tblA SET name = ?,agency = ?,feature_Tag = ?,start_Date = ?,end_Date = ?,hide_text = ?,ongoing = ? WHERE id = ?
    [1] => Array
        (
            [0] => SRtest12
            [1] => AUS
            [2] => SRTest1
            [3] => 2000-01-01 00:00:00
            [4] => 2050-12-31 00:00:00
            [5] => 0
            [6] => 1
            [7] => 165
        )

)

Upvotes: 0

Views: 71

Answers (1)

deceze
deceze

Reputation: 522085

The most straight forward method is probably preg_replace_callback with a counter:

$query = 'UPDATE...';
$data = ['foo', 'bar', ...];
$counter = 0;

$result = preg_replace_callback('/\?/', function () use (&$counter, $data) {
    return $data[$counter++];
}, $query);

You'll want to add code to escape/quote the values for SQL syntax inside the callback too. For completeness I'd probably also add a check whether $data[$counter] exists and throw an exception if not.

Upvotes: 1

Related Questions