重构以循环mySQL和PHP代码

Question

I currently have an associative array called fields that stores all my $_POST variables from my HTML input fields. The associative array variables are used when I try to bind my values to the mySQL database In the same manner, I also have the same names used as column headers for the mySQL database. So this is very tedious and long. Is there a way to perhaps loop through the columns and assign a value with another loop for the associative array? Please check the example below and thank you in advance for your help.

     try {
                $insertSql = "INSERT INTO tableExample";
                $sqlCols = " (
                    a,
                    b,
                    c,
                    d,
                    e,
                    f,
                    g,
                    h,
                    i,
                    j,
                    k,
                    l,
                    m,
                    n,
                    o,
                    p,
                    q,
                    r,
                    s,
                    t,
                    u,
                    v,
                    w,
                    x
                )";
                $result = $db->prepare($insertSql . $sqlCols . " VALUES (
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?)");
                $result->bindValue(1,$fields['a'],PDO::PARAM_STR);
                $result->bindValue(2,$fields['b'],PDO::PARAM_STR);
                $result->bindValue(3,$fields['c'],PDO::PARAM_STR);
                $result->bindValue(4,$fields['d'],PDO::PARAM_STR);
                $result->bindValue(5,$fields['e'],PDO::PARAM_STR);
                $result->bindValue(6,$fields['f'],PDO::PARAM_STR);
                $result->bindValue(7,$fields['g'],PDO::PARAM_STR);
                $result->bindValue(8,$fields['h'],PDO::PARAM_STR);
                $result->bindValue(9,$fields['i'],PDO::PARAM_STR);
                $result->bindValue(10,$fields['j'],PDO::PARAM_STR);
                $result->bindValue(11,$fields['k'],PDO::PARAM_STR);
                $result->bindValue(12,$fields['l'],PDO::PARAM_STR);
                $result->bindValue(13,$fields['m'],PDO::PARAM_STR);
                $result->bindValue(14,$fields['n'],PDO::PARAM_STR);
                $result->bindValue(15,$fields['o'],PDO::PARAM_STR);
                $result->bindValue(16,$fields['p'],PDO::PARAM_STR);
                $result->bindValue(17,$fields['q'],PDO::PARAM_STR);
                $result->bindValue(18,$fields['r'],PDO::PARAM_STR);
                $result->bindValue(19,$fields['s'],PDO::PARAM_STR);
                $result->bindValue(20,$fields['t'],PDO::PARAM_STR);
                $result->bindValue(21,$fields['u'],PDO::PARAM_STR);
                $result->bindValue(22,$fields['v'],PDO::PARAM_STR);
                $result->bindValue(23,$fields['w'],PDO::PARAM_STR);
                $result->bindValue(24,$fields['x'],PDO::PARAM_STR);
                $result->execute();
            } catch (Exception $e) {
                echo "Unable to store data";
                echo $e->getMessage();
                exit;
            }           

Answer 1

Quickly cooked up a possible solution. Before trying it, please bear in mind that it's untested and there may be several syntax errors here (no PDO on my end... Actually, no php either).

What this does is define two separate maps: one for "database columns to indexes" and other for "database columns to values".

I can't be sure that this solves your problem but it's a starting point.

<?php
try 
{
    //Define your columns and indexes...
    $fields=['col_a' => 1, 'col_b' => 2, 'col_c' => 3, 'col_d' => 4];

    //Let this be your post data. Notice how the index are the same as above.
    $data=['col_a' => "Value a", "col_b" => 33, "col_c" => "Value C", "col_d" => 12];

    //Prepare the statement....
    $sqlCols=null;
    $sqlValues=null;

    foreach($fields as $key => $value) 
    {
        $sqlCols.=$key.',';
        $sqlValues.='?,';
    }

    $result = $db->prepare("INSERT INTO tableExample (".substr($sqlCols, 0, -1).") VALUES (".substr($sqlValues, 0, -1).")";

    //Bind the values... Please, make sure the order of parameters is correct!.
    foreach($fields as $key => $index)
    {
        $result->bindValue($index, $data[$key], PDO::PARAM_STR);
    }

    $result->execute();
} 
catch (Exception $e) 
{
    echo "Unable to store data";
    echo $e->getMessage();
}
?>

Again, let me state that you're exposing your database structure through the names in your post variables. I doubt that's what you want to do.