Update mysql row determined by a php string

Question

Hello everyone the problem I am having is that I am unable to update a mysql row with php that is determined by a string. The code that does not work is below

mysqli_query($con1,"UPDATE Complete SET  $GameName = '5'
WHERE Username='$UserInfo[0]'");

But this code works see how the row I want to set changes

mysqli_query($con1,"UPDATE Complete SET  rowname = '5'
WHERE Username='$UserInfo[0]'");

I have already tried to put single quotes around $Gamename to no avail. and cant place the row name in quotes for unknown reasons. UPDATE: I have tried to get an error log but the debugger is not recognizing any problems so to try and help I will post another example that does not work but will help to determine that $UserInfo[0] is not the problem.

mysqli_query($con1,"UPDATE Complete SET  $GameName = '5'
WHERE Username='beta'");

Also To rule out things like the variable $GameName from being the problem I tested the code below to no avail.

$Column="Addition";
mysqli_query($con1,"UPDATE Complete SET   $Column= '5'
WHERE Username='beta'");

But the code below this does work

mysqli_query($con1,"UPDATE Complete SET   Addition= '5'
WHERE Username='beta'");

Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. — Kermit, Jul 07 '14 at 19:14

score 1 · Answer 1 · answered Jul 05 '14 at 01:02

1

I think you need to do some learning on MySQLi. The way your using it is improper. You should be utilizing prepared statements. By the looks of how you have the first query, you should not have to specify a column name as a variable, but maybe that's where your problem is?

It should be something like below, but as I said before, you should not be specifying the column name using a variable.

if ($stmt = $con1->prepare("UPDATE Complete SET ".$GameName." = '5' WHERE Username=?")) {
    $stmt->bind_param('s', $UserInfo[0]);
    $stmt->execute();
    $stmt->close();
}

answered Jul 05 '14 at 01:02

SameOldNick

2,397
24
33

Why should I not specify a column value as a variable I have a table with one row per page then use a variable to select which row I want to modify and what do prepared statements have to offer. – TheBackDoor Jul 05 '14 at 01:04
You should be specifying the column value as a variable, not the column name as a variable. However, it is a general rule and some rules are meant to be broken. – SameOldNick Jul 05 '14 at 01:06

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

Probably your problem is with quoting. So you can try some of this ways of escaping:

mysqli_query($con1,"UPDATE Complete SET  `".$GameName."` = '5' WHERE Username='".$UserInfo[0]."'");

OR

mysqli_query($con1,"UPDATE Complete SET  `{$GameName}` = '5' WHERE Username='{$UserInfo[0]}'");

By the way, your code is very vulnerable to sql injection.

See HOW TO PREVENT SQL INJECTION IN PHP

edited May 23 '17 at 11:57

Community

1
1

answered Jul 05 '14 at 00:56

hlscalon

7,304
4
33
40

check http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php and http://www.php.net/manual/en/function.error-reporting.php – hlscalon Jul 05 '14 at 01:01

score 0 · Answer 3 · answered Jul 05 '14 at 01:08

0

I don't really think the problem is with $GameName... You can try this

$sql = "UPDATE Complete SET  ".$GameName." = '5'
WHERE Username='".$UserInfo[0]."'";

mysqli_query($con1, $sql);

I think the problem is with $UserInfo[0].

answered Jul 05 '14 at 01:08

Abhishek Batra

1,539
1
18
45

even like this it still does not work $GameName="rowname"; $Username="admin"; $con1=mysqli_connect("localhost","admin","admin","db"); $sql = "UPDATE Complete SET ".$GameName." = '5' WHERE Username='".$Username."'"; – TheBackDoor Jul 05 '14 at 01:10
It will be great if you can give us the Error you are getting. – Abhishek Batra Jul 05 '14 at 01:11

score 0 · Answer 4 · answered Jul 05 '14 at 02:00

Try to echo the result SQL and run the query to your actual database GUI (use navicat or something). I think your $GameName value could not be a valid column from your database. Since you are using dynamic column name, you should consider validating your columns like this:

$array_columns = array('column_1', 'column_2', 'name', 'other_col');
if (in_array($GameName, $array_columns)) {
    mysqli_query($con1,"UPDATE Complete SET $GameName = '5'
        WHERE Username='$UserInfo[0]'");
}

score -1 · Answer 5 · answered Jul 05 '14 at 21:04

Here is the code that solved my problem the problem was that i need to create my statement before trying to process it and one of my tables columns(The one i was trying to edit) would not edit so I had to remove it then recreate it.

$Query="UPDATE Complete SET  " .$UserInfo[9] . "='1' WHERE Username='" . $UserInfo[0] . "'";
mysqli_query($con1,"$Query");

Update mysql row determined by a php string

5 Answers5