How to Avoid Code Repetition with PHP SQL Prepared Statements

How to avoid code repetition with PHP SQL prepared statements?

That's a very good question and I have several answers for it.

Raw PHP

First of all, you can use several tricks to reduce the verbosity, like omitting the fields clause in the query (and adding default values in the values clause for the missing fields) and using positional placeholders:

$sql = 'INSERT INTO tasks VALUES(null, ?, ?, ?)';
$this->pdo->prepare($sql)->execute([$taskName, $startDate, $completedDate]);

I call them tricks because they aren't always applicable.

Note that you must provide a value for all the columns in the table. It could be simply a null value or, to make it 100% equivalent for the omitted field, you could have it as DEFAULT(field_name) so it will insert a default value defined in the table definition.

A helper function

The next level would be creation of a helper function for inserts. When doing this, one must be acutely aware of the SQL Injection through field names.

Hence, such a helper function must have a helper function of its own:

function escape_mysql_identifier($field){
    return "`".str_replace("`", "``", $field)."`";
}

Having such a function we can create a helper function that accepts a table name and a data array contains field name => value pairs:

function prepared_insert($conn, $table, $data) {
    $keys = array_keys($data);
    $keys = array_map('escape_mysql_identifier', $keys);
    $fields = implode(",", $keys);
    $table = escape_mysql_identifier($table);
    $placeholders = str_repeat('?,', count($keys) - 1) . '?';
    $sql = "INSERT INTO $table ($fields) VALUES ($placeholders)";
    $conn->prepare($sql)->execute(array_values($data));
} 

I am intentionally not using named placeholders here because it makes the code shorter, there could be characters disallowed in placeholder names while being perfectly valid for the column names, a space or a dash for example; and also because we generally don't care how it works inside.

Now your insert code will become

prepared_insert($this->pdo, 'tasks',[
    'task_name' => $taskName,
    'start_date' => $startDate,
    'completed_date' => $completedDate,
]);

with so much repetitions removed

A baby ORM

However I don't like the above solution either, there are some quirks in it.

To fulfill the need for the automation, I would rather create a simple ORM. Don't be scared by the term it is not as monstrous as some picture it. I have a complete working example posted recently so you can use it for your case as well, especially given you are already using OOP.

Just throw in an insert() method

public function insert()
{
    $fields = '`'.implode("`,`", $this->_fields).'`';
    $placeholders = str_repeat('?,', count($this->_fields) - 1) . '?';

    $data = [];
    foreach($this->_fields as $key)
    {
        $data[]  = $this->{$key};
    }
    $sql = "INSERT INTO `{$this->_table}` ($fields) VALUES ($placeholders)";
    $this->_db->prepare($sql)->execute($data);
}

After that you will have to prepare your class,

class Task extends BaseAcctiveRecord
{
    protected $_table = "tasks";
    protected $_fields = ['task_name', 'start_date', 'completed_date'];
}

and then - all the magic happens here! - you won't have to write the insert code at all! Instead just create a new instance of your class, assign values to its properties and then just call the insert() method:

include 'pdo.php';
$task = new Task($pdo);
$task->task_name = $taskName;
$task->start_date = $startDate;
$task->completed_date = $completedDate;
$user->insert();

Avoiding code repetition for MySQL queries (multiple while)

There are two ways, variable variables or arrays. I'd suggest arrays as they are less prone to throwing errors everywhere.

<?php
include_once "connect.php";
$questions = array();
$questions[1] = mysqli_real_escape_string($con, $_POST['question_01']);
// $question_02 - $question_09 go here...
$questions[10] = mysqli_real_escape_string($con, $_POST['question_10']);

    $i = 0;
    $array_sum=[];
     while ($i < 10){
      $i++;
       $sql =  "SELECT * FROM parteners WHERE question_no = $i AND answer_variant = '".$questions[$i]."'";
       $result = mysqli_query($con, $sql);

       $final_array_1 = array();

      while ($row = mysqli_fetch_array($result, MYSQLI_NUM))
      {

        $final_array_1 = $row;
            $array_sum = array_map(function () {
                return array_sum(func_get_args());
            }, $array_sum, $final_array_1);
      }
}
print_r($final_array_1);

EDIT: The reason I used an array instead of just straight up using the POST variable in the while loop is so there is room before you run anything for validation (ensuring your question array contains 10 posted values etc)

Reuse prepared statement with optional condition

That's a very good question from a thoughtful programmer.

I'd say there is no equally elegant and simple solution. But I can offer a couple of approaches and you can decide which one is best for you.

1. You can use named placeholders and keep emulation mode turned on. In this case you will have to bind every variable only once. However, overall writing will remain the same as for named placeholders you have to repeat each name three times wither way.

2. As it was suggested in the now deleted answer, you could use conditional query building, something like

   $sql = "SELECT columns FROM tables WHERE manyConditions";
   $params = [$a,$b,$c];
   if (!empty($d)) {
       $sql .= " AND specificColumn = ?";
       $params[] = $d;
   }
   $stmt = $pdo->prepare($sql);
   $stmt->execute($params)
   

How to avoid repeating similar sections of MySQL query code in PHP?

This is not best practice to write code like this regardless whether the app is low volume or not. You are declaring same pdo object over and over again everytime the function is called which is not efficient. You should look to abstract the code to look at OOP concepts. For example create the PDO object once and then return an instance of it when connecting to DB.

funcUpdatePHPVariableInMySQL

You are better of using something like a switch statement such as:

switch (n) {
    case update:
update DB;
        break;
    case insert:
insert into DB;       
 break;
    case delete:
delete from DB;
        break;
    ...
    default:
 xyz;
}

above looks too much hardcoding to fit the application needs. That's just my two cents.

How to bind params to multiple values without parameter repetition

Typically you wouldn't try and double up like this, you'd prepare your statement once and run it twice with different values. I'm not a PHP coder so I borrowed from GMB's post:

$stmt20 = $conexao->prepare("
        INSERT INTO esc_usuarios_pgtoparcelas (parcela_codigo_plano, parcela_mes, parcela_valorBonificacao, parcela_situacao)
        VALUES
        (LAST_INSERT_ID(), :m, :zero, :parcela_situacao_prog)
        ");

$stmt20->execute(array(
    :m => $m1, 
    :zero => $zero, 
    :parcela_situacao_prog => $parcela_situacao_prog 
));

$stmt20->execute(array(
    :m => $m2, 
    :zero => $zero, 
    :parcela_situacao_prog => $parcela_situacao_prog 
));

If you had 1000 values to insert, you'd prepare the statement once and execute it 1000 times (in a loop)..

$stmt20 = $conexao->prepare("
        INSERT INTO esc_usuarios_pgtoparcelas (parcela_codigo_plano, parcela_mes, parcela_valorBonificacao, parcela_situacao)
        VALUES
        (LAST_INSERT_ID(), :m, :zero, :parcela_situacao_prog)
        ");

for ($x = 0; $x <= 1000; $x++) {
  $stmt20->execute(array(
    :m => $array_of_m[$x], 
    :zero => $zero, 
    :parcela_situacao_prog => $parcela_situacao_prog 
  ));
}

Digging around in the php help docs for mysqli, I guess the code for your situation might be more like:

$stmt20 = $conexao->prepare("
        INSERT INTO esc_usuarios_pgtoparcelas (parcela_codigo_plano, parcela_mes, parcela_valorBonificacao, parcela_situacao)
        VALUES
        (LAST_INSERT_ID(), ?, ?, ?)
        ");

$stmt20->bind_param('sdssds', $m, $zero, $parcela_situacao_prog);

for ($x = 0; $x <= 1000; $x++) {
    $m = $array_of_m[$x];
    $stmt20->execute();
}

Chiefly I'm trying to get across this notion that you set things up once, then jsut change the values and repeatedly call execute..

As notd, i'm not a php coder.. I referenced this: PHP/mysql: mysqli prepared statement insert same value multiple times within for loop

how to avoid duplicate records insertion in php mysqli?

Below code will help you inserting unique data:

$result = mysqli_query($conn, "SELECT nama, member, peserta, tour FROM gathering where nama = '$nama' and member = '$member' and peserta='$peserta' and tour='$tour'")
$total = mysqli_num_rows($result);

if($total==0)
{
    mysqli_query($conn,"insert into gathering (nama, member, peserta, tour) 
values ('$nama', '$member', '$peserta', '$tour')");
} 

loop prepare statements, what needs to be repeated?

You don't have to prepare the query multiple times. Just bind the parameters and execute it multiple times.

From the PHP Manual:

For a query that you need to issue multiple times, you will realize
better performance if you prepare a PDOStatement object using
PDO::prepare() and issue the statement with multiple calls to
PDOStatement::execute().

Hope this helps.

Prevent duplicate data PHP Mysql

You can add a UNIQUE constraint to the email field. This will be done by running the following query:

ALTER TABLE `event` ADD UNIQUE (email);

After that, when you want to insert a new row with an email that already exists in your table, you'll get an error like this: #1062 - Duplicate entry ...

You can catch that exception and react on it in your PHP code like this:

<?php
// perform INSERT query...
if (mysqli_errno($link) == 1062) {
    print 'An error occured';
}

---

Related Topics