SQL update statement parameter list or individual.

[CB150Special]

I'm not sure whether to create one SQL update routine and pass a parameter list to it possibly using something like a SESSION variable, or, to create one SQL routine for each procedure with its own set of variables.

The source of the data is a form using submit.

 

Alternately, what are the key words I need to search for on this.

 

[Jacques1]

It's impossible to give you any meaningful answer or keyword based on this extremely vague description. Also, what's an "SQL routine"? A stored procedure? A PHP function running SQL queries? Something else?

 

Show us some concrete examples of what you're talking about, then we can help you.

Edited July 12, 2017 by Jacques1

[CB150Special]

This is more a general question. I'm leaning to be passing a string to the php SQL script. Example below.

<?php
include 'sql_con.php';
if ($conn->connect_error) {
   echo ('Connection failed: ' . $conn->connect_error);
 }else {
    $sql =  'UPDATE db SET Name = '.$_POST['Name'].' WHERE ID = '.$_SESSION['NameID'].' ';
    $result = $conn->query($sql);
}   
$conn->close();
?> 

This would mean a php file for every db update required for each input form I have.

 

Using a variable for  "SET Name'.$_POST['Name']" means I can use the same php file for many update routines.  Eg

"SET column1 = value1, column2 = value2, ..."

[Jacques1]

There seem to be several misunderstandings.

 

First, you definitely do not create a new connection or even PHP script for every single query. Why would you do that? You establish one connection and then run as many queries as you need in the main script or within functions.

<?php

$connection = new mysqli(...);

// one query
$connection->query('...');

// another query
$connection->query('...');

...

Secondly, you must not insert user input directly into query strings. This immediately leads to an SQL injection vulnerability and can compromise your database or even the entire server. Always use prepared statements with parameters when you need to pass dynamic values to a query.

 

Then you should seriously consider switching from mysqli to PDO which is far more programmer-friendly. If you insist on using mysqli, then you need to actually learn it (secure queries, proper error handling etc.).

 

If you fix all this, there's really no reason for special code to handle UPDATE queries. In fact, I strongly recommend against that. You may save a few lines with a generalized update routine, but there's a huge risk of introducing new vulnerabilities and bugs through the dynamic queries. Just use plain old prepared statements with static query templates.