Can i use isset() to control execution of mysql query

Question

I have created an editable database to help me automate weekly member updates. There are 9 values that each member updates each week, these are controlled by $_POST submit to secondary php.

From that php, the post values are set as php var, then used to UPDATE sql db.

mysql_select_db("web_footy1") or die(mysql_error());




// The SQL statement is built
$strSQL = "UPDATE Round_6 SET "; 

$strSQL = $strSQL . "Game1= '$Game1', ";

$strSQL = $strSQL . "Game2= '$Game2', "; 

$strSQL = $strSQL . "Game3= '$Game3', "; 

$strSQL = $strSQL . "Game4= '$Game4', "; 

$strSQL = $strSQL . "Game5= '$Game5', "; 

$strSQL = $strSQL . "Game6= '$Game6', "; 

$strSQL = $strSQL . "Game7= '$Game7', "; 

$strSQL = $strSQL . "Game8= '$Game8', "; 

$strSQL = $strSQL . "Game9= '$Game9' "; 

$strSQL = $strSQL . "WHERE  Member = '$Member' "; 

// The SQL statement is executed 
mysql_query($strSQL) or die(mysql_error()) ;

Yes, i am aware this is subject to SQL injection, it is a private site so security can wait atm

The problem is that all values are update at same time, and to update one, you need to re enter all otherwise they are replaced with empty value.

So my question in twofold. A) What is the cleanest way to be able to control each variable separately, B) Can i, and if so, how to use isset($GameX) to control which queries are executed.

Eg

IF (isset($Game1)) {UPDATE Round6 SET Game1='$Game1' WHERE Member='$Member'} ;

Please keep in mind, 3 weeks ago i knew nothing about coding, and have crash coursed in html, php and sql in that time... Cheers

Answer 1

I really really really can't recommend enough that you FIX your SQL injection.

Having said that, you can programatically add conditions to your UPDATE clause. An example might be the following snippet:

<?php

$Game3 = "things";
$Game5 = "stuff";
$Game6 = "awesome";
$Member = 'ben';

$update_parts = array();
for ($game_counter = 1; $game_counter < 10; $game_counter++) {
    $variable_name = "Game" . $game_counter;
    if ( isset($$variable_name) ) { // This is like isset($Game1)
        $update_parts[] = "Game" . $game_counter . " = '" . $$variable_name . "'";
    }
}

if ( sizeof($update_parts) > 0 ) {
    $strSQL = "UPDATE Round_6 SET ";
    $strSQL .= implode(", ", $update_parts);
    $strSQL .= " WHERE Member = '$Member'";
    echo $strSQL;
}

I've put in a couple of variables up the top there. This yields the following SQL:

UPDATE Round_6 SET Game3 = 'things', 
  Game5 = 'stuff', Game6 = 'awesome' WHERE Member = 'ben'

EDIT: If you want to use PDO, you need the Query and the Parameters separated. In my example below, I'm putting the Query parameters in

$conn = new PDO("mysql:host=localhost;dbname=database;","username","password"); // Your Connection String

$update_parts = array();
$query_params = array();
for ($game_counter = 1; $game_counter < 10; $game_counter++) {
    $variable_name = "Game" . $game_counter;
    if ( isset($$variable_name) ) { // This is like isset($Game1)
        $update_parts[] = "Game" . $game_counter . " = ?";
        $query_params[] = $$variable_name;
    }
}

if ( sizeof($update_parts) > 0 ) {
    $strSQL = "UPDATE Round_6 SET ";
    $strSQL .= implode(", ", $update_parts);
    $strSQL .= " WHERE Member = ?";
    $query_params[] = $Member;

    // Here is where you'd run the update
    $stmt = $conn->prepare($strSQL);
    $stmt->execute($query_params); // Notice I'm passing in the parameters separately
}