Cut array to fit needs

Question

I want to write the array values into my database like:

"UPDATE tbl SET $fieldname = $fieldvalue WHERE id=$recid"

and I get this array from $_POST['changed']:

[changed] => Array
        (
            [0] => Array
                (
                    [recid] => 1
                    [vorname] => Walter
                    [nachname] => Bauer
                )

            [1] => Array
                (
                    [recid] => 2
                    [vorname] => Michael
                )

            [2] => Array
                (
                    [recid] => 3
                    [vorname] => Peter
                    [nachname] => Highler
                    [email] => p.highler@aol.com
                )

            [3] => Array
                (
                    [recid] => 4
                    [vorname] => Maria
                    [nachname] => Maier
                )

            [4] => Array
                (
                    [recid] => 5
                    [vorname] => Werner
                    [nachname] => Rock
                )

        )

When I do it with this foreach loop, the recid get's also as field name - but this should not happen.

foreach ($_POST['changed'] as $SubArray)
{
    foreach ($SubArray as $key => $value)
    {
        $sql .= "UPDATE kunden SET ".$key."='".$value."' WHERE id=**?????????**";
    }       
}

Your SQL query is vulnarable to SQL-injection. See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Joren, Sep 02 '13 at 17:05
@Joren i know that but for now, that i get it and that i understand and learn from my problem i used this string — Mike, Sep 02 '13 at 17:08
Also you are grouping the result into one string with `$sql .=` where if you're using the old and deprecated `mysql_*` library it will not work as it only executes 1 query at a time per query. — Prix, Sep 02 '13 at 17:09
@ i have `$sql .=` to write it into a txt-file first to check if the array and foreach loop is working correct. — Mike, Sep 02 '13 at 17:13
Also you are generating 3 queries if all the 3 fields exist where you could make a single query to update all the fields with a simple check to append only the ones present. — Prix, Sep 02 '13 at 17:21

score 2 · Answer 1 · edited Sep 02 '13 at 17:25

foreach ($_POST['changed'] as $SubArray)
{
    $id = $subArray['recid'];
    $values = array();

    foreach ($SubArray as $key => $value)
    {
        if($key != 'recid')
        {
            $values[] = $key . '=' . $value;                
        }
        $sqlValues = implode(', ', $values);
    }

    $sql = "UPDATE kunden SET " . $sqlValues . " WHERE id='" . $id . "'";
    //execute query here
}

MD SHAHIDUL ISLAM · Answer 2 · 2013-09-02T17:22:20.947

0

Just use:

if ($key === 'recid') continue;
$sql .= "UPDATE kunden SET ".$key."='".$value."' WHERE id='".$SubArray['recid']."'";

instead of

$sql .= "UPDATE kunden SET ".$key."='".$value."' WHERE id=**?????????**";

edited Sep 02 '13 at 17:22

answered Sep 02 '13 at 17:14

MD SHAHIDUL ISLAM

14,325
6
82
89

Alexander Yancharuk · Accepted Answer · 2013-09-03T11:45:48.950

-1

Try this:

foreach ($_POST['changed'] as $SubArray) {
    foreach ($SubArray as $key => $value) {
        if ($key === 'recid') continue;

        $sql = "UPDATE kunden SET $key = $value WHERE id = '$SubArray[recid]'";
    }    
}

This code is not for production. You must escape each $_POST variable or use PDO placeholders.

Update (PDO placeholders example):

$dsn = 'mysql:dbname=kunden;host=127.0.0.1';
$enc = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'");
$dbh = new PDO($dsn, $user, $pass, $enc);

foreach ($_POST['changed'] as $SubArray) {
    foreach ($SubArray as $key => $value) {
        if ($key === 'recid') continue;

        $sql  = "UPDATE kunden SET $key = :value WHERE id = :recid";
        $stmt = $dbh->prepare($sql);

        try {
            $stmt->execute(array(':value' => $value, ':recid' => $SubArray['recid']));
        } catch (PDOException $e) {
            // error processing
        }
    }
}

edited Sep 03 '13 at 11:45

answered Sep 02 '13 at 17:12

Alexander Yancharuk

13,817
5
55
55

thank you for your solution its the answer for my question. you also write to user PDO placeholders. but using placeholders for table columns is not possible. for my example i don't know the column names. is it also possible to make a dynamic and secure PDO statement/placeholder solution? – Mike Sep 02 '13 at 17:22
sorry i found the mistake - i had this: `$sql = "UPDATE kunden SET $key = ':value' WHERE id = :recid";` after removing `'` and `'` before and after ':value' it worked for me now. does this also mean, that it does not matter if `:value` is a **char** or an **integer**???? – Mike Sep 02 '13 at 18:15
I tried it and i add a new column "age" and set it to "int(11)". i printed the $sql out into a txt-file and I only get `UPDATE kunden SET age = :value WHERE id = :recid` - values for `:value` and `:recid` are missing and the fields don't get updated in the database. – Mike Sep 02 '13 at 18:37
$sql is not a real executed query. You can enable query logging in you mysql server for checking what exactly was executed or check it in mysql client typing `show full processlist`. Or check here: http://stackoverflow.com/questions/650238/how-to-show-the-last-queries-executed-on-mysql – Alexander Yancharuk Sep 02 '13 at 18:51
this is not possible because i get from phpmyadmin `#1227 - Access denied; you need the SUPER privilege for this operation` and i don't have root permissions on my server. is there another solution - maybe to print the executed query also into a txt-file? – Mike Sep 02 '13 at 19:01
What is the purpose of all these $e->getCode();, $e->getFile();, $e->getLine(); and $e->getMessage(); stuff? – Your Common Sense Sep 02 '13 at 20:42
@Mike Unfortunately PDO haven't interface for accessing to last executed query. You can do it only through tools like mysql client or logging or mysqladmin tool. – Alexander Yancharuk Sep 03 '13 at 04:37
@YourCommonSense It's just Exception processing. In dev environment you can echo customized result, in produdction environment you can include user friendly error page and email error to admin. – Alexander Yancharuk Sep 03 '13 at 04:38
So I have to edit this long code for the every query when change from dev to production? What if I will need to debug it after release? Do I have to change it back again? – Your Common Sense Sep 03 '13 at 04:43
@YourCommonSense You just need once adopt that example for your project. If you want to debug it after release make agile Exception processing algorithm that depends on your environment. – Alexander Yancharuk Sep 03 '13 at 09:40
Okay, suppose I adopted it, wrapping every query into try catch with all this $e->getCode();, $e->getFile();, $e->getLine(); and $e->getMessage(); stuff. What's next? Would it work with agile Exception processing algorithm? – Your Common Sense Sep 03 '13 at 09:43
If you don't know how to process your error - **why use `try..catch` at all?** What benefit you get from catching a PDOException? – Your Common Sense Sep 04 '13 at 05:46
@YourCommonSense If you don't know how to adopt same Exception processing to production and development environment ask proper question not in comments. Same about benefit from catching PDOException. If you don't know something just ask question. – Alexander Yancharuk Sep 04 '13 at 06:35
Unfortunately, I know the answer already. And such an adaptation has nothing to do with wrapping every query into try..catch. – Your Common Sense Sep 04 '13 at 06:38

Cut array to fit needs

3 Answers3