Unknown column 'E5V7D24M10Y2015' in 'where clause'

Question

I have the code below to update (more 'add to') a row in a database. I have read a few very similar posts, but still can't see where I'm going wrong...

I am getting the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''EventID' = '2' WHERE GameID = 'E2V1D24M10Y2015' AND PlayerID = '55'' at line 1

In this case, 'E5V7D24M10Y2015' is the value of $GameID. I am looking for a column called GameID where the value is E5V7D24M10Y2015, not a column of that name. Please tell me why my $sql is looking for a column named after the value it is looking for.

Each time the function runs the count($Runners) will be different and the values in each variable will be different. That is why I have the SQL in a loop.

        if ($formtype == "gameresults"){

    $Runners = $_POST['runners'];
    $event = $_POST['event'];
    $eid = $_POST['eid'];
    $vid = $_POST['vid'];
    $GameID = $_POST['GameID'];
    $date = $_POST['date'];
    $season = $_POST['season'];
    $region = $_POST['region'];
    $notes = $_POST['notes'];
    $kev = "kev@email.com";
    $email = $_POST['manager'];
    $notes = wordwrap($notes,70);
    $subject = ("Results for " . $event);
    $tix = "";
    $cashs = "";

    for ($x = 1; $x < ($Runners + 1); $x++){
    $ID = $_POST['ID' . $x];
    $Name   = $_POST['Name' .$x];
    $Place  = $_POST['Place'.$x];
    $Points = $_POST['Points'.$x];
    $Cash   = $_POST['Cash'.$x];
    $Ticket = $_POST['Ticket'.$x];
    $vn     = $_POST['vn'];
    $buyin  = $_POST['buyin'];

    $data = array($eid,$vid,$region,$buyin,$GameID,$date,$season,$ID,$Name,$Place,$Points,$Ticket,$Cash,$Runners);
    $fields = array('EventID','VenueID','Region','Buyin','GameID','Date','Season','PlayerID','Name','Position','Points','Ticket?','Cash','Runners');

for ($x = 0; $x < (count($data) - 1); $x++){
$sql = "UPDATE results SET '$fields[$x]' = '$data[$x]' WHERE GameID = '$GameID' AND PlayerID = '$ID'";
$res = mysqli_query($dbcon, $sql) or die("Update failed. <br>" . mysqli_error($dbcon));
}

Try to replace `WHERE GameID = $GameID ` to `WHERE GameID = '$GameID'` — KuKeC, Oct 24 '15 at 07:55
This gives the following error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? = ' ' WHERE GameID = 'E2V1D24M10Y2015' AND PlayerID = 55' at line 1 — Tunna182, Oct 24 '15 at 08:00
We'll have much more success figuring it out if we can see the actual SQL, including the values, that are being sent. Do `var_dump($sql)` before you actually do the `mysql_query` and post the results. It may even be that you have a quote mark in your data that's causing the issue -- your query is vulnerable to [SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Also, you should rewrite this code so that you do the update in a single statement, which would be much more efficient and have no danger of a half-completed update. — Matt Gibson, Oct 24 '15 at 08:17
Well, not really. You don't want the column names in single quotes; that's what's causing your current problem. You'd be much better off writing this as a single SQL statement, without the loop, and using the [built-in mysqli parameter binding](http://stackoverflow.com/a/728239/300836) to ensure that things are automatically quoted (this will sort out your issue where you need strings to be quoted in single quotes but not numbers, for example.) — Matt Gibson, Oct 24 '15 at 10:43

score 1 · Accepted Answer · answered Oct 24 '15 at 08:25

You could modify your code to be more clear. I did example but I did not map all the fields so you need to adjust it.

$eid = 4;
$vid = 5;
$region = 'aa';
$buyin = 'asd';
$gameID = 5;
$date = 5;
$playerID = 10;

$fields = array(
                'EventID' => $eid,
                'VenueID' => $vid,
                'Region'  => $region,
                'Buyin'   => $buyin,
                'GameID'  => $gameID,
                'PlayerID'=> $playerID,
                'Date'    => $date,
               );

$numItems = count($fields);

$query = "UPDATE `results` SET  ";
$i = 0;
foreach($fields as $name => $value) {
    ++$i;
    if($name == 'GameID' || $name == 'PlayerID') {
       continue;
    }
    $query .= sprintf(" `%s` = '%s'%s ", $name, $value, ($i === $numItems ? "": ","));
}

$query .= sprintf(" WHERE `GameID` = '%d' AND `PlayerID` = '%d'",     $fields['GameID'], $fields['PlayerID']);

echo $query;

Fields can be maped via $key => $value and then used in foreach loop. Also using sprintf() makes is more clear to read. However, the best option would be using prepared statements.

Also you don't need to make multiple UPDATE queries, just SET more parameters in one query.

score 0 · Answer 2 · answered Oct 24 '15 at 07:45

$data = array($eid,$vid,$region,$buyin,$GameID,$date,$season,$ID,$Name,$Place,$Points,$Ticket,$Cash,$Runners);
    $fields = array('EventID','VenueID','Region','Buyin','GameID','Date','Season','PlayerID','Name','Position','Points','Ticket?','Cash','Runners');

for ($x = 0; $x < (count($data) - 1); $x++){
$sql = "UPDATE results SET " . $fields[$x] . " = '$data[$x]' WHERE results.GameID = $GameID AND results.PlayerID = $ID";
$res = mysqli_query($dbcon, $sql) or die("Update failed. <br>" . mysqli_error($dbcon));
}

Try this by specifying alias.

Unknown column 'E5V7D24M10Y2015' in 'where clause'

2 Answers2