Should I use different names for UPDATE and/or INSERT variables

[Pedroski55]

I made a poll webpage, for taking a poll in the classroom, students should go around and ask everyone the question or questions.

Just a little technical point: I have an INSERT and UPDATE variable:

$mysql = 'INSERT INTO poll_1_data(surveyornr, weeknr, gender, choice) VALUES (?, ?, ?, ?)';
$mystmt = $pdo->prepare($mysql);
$mystmt->execute([$surveyornr, $weeknr, $q1, $q2]);

Then, a bit further down I have:

$mysql = 'UPDATE poll_count20BECW SET ' . $weeknr . ' = ' . $weeknr . ' + 1 WHERE studentnr = ' . $surveyornr;
//echo 'SQL is ' . $mysql . '<br>';
$mystmt = $pdo->query($mysql);

I think the $mystmt is “burned” after use in the first INSERT sql, so I just re-declare $mysql as UPDATE

Is this bad practice?

Should I call the second one $mysql2 and $mystmt2?

(I also have a third statement in this PHP file to UPDATE the table poll_totals.)

[skawid]

There’s nothing tehnically wrong with reusing variable names, but it can get confusing if you do it in the same scope - in the same function, for example.

Using $mysql2 and $mystmnt2 doesn’t solve the problem, as you’ve no real idea what the variables are from their names at that point.

If I was writing this I’d split out the SQL calls into functions, then anyone reading your main code doesn’t have to worry about the details of building and executing the query.

[Strider64]

You can call them anything you want, but it’s easier to read and debug if you use the same name conventions that everyone is using. You should also be using prepared statements and another way of doing this is have an array read in from the HTML form and using the array keys and values that MATCH the database table. An example:

/*
 * Update data in the procedural way
 */
function updateData(array $data, $pdo, $table): bool
{
    /* Initialize an array */
    $attribute_pairs = [];

    /* Create the prepared statement string */
    foreach ($data as $key => $value)
    {
        if($key === 'id') { continue; } // Don't include the id:
        $attribute_pairs[] = "$key=:$key"; // Assign it to an array:
    }

    /*
     * The sql implodes the prepared statement array in the proper format
     * and updates the correct record by id.
     */
    $sql  = 'UPDATE ' . $table . ' SET ';
    $sql .= implode(", ", $attribute_pairs) . ' WHERE id =:id';

    /* Normally in two lines, but you can daisy-chain pdo method calls */
    $pdo->prepare($sql)->execute($data);

    return true;
}

[Pedroski55]

Thanks both of you!

I have no experience writing functions for PHP. I will read up on it!

I have a large collection of my own Python functions, I pack them in modules and call them as needed.