MySQL queries don't work when query string is concatenated/ substituted from variables in PHP

Question

I have a string saved in a variable. That string is made from a couple other variables. If I echo it to a test page it shows up correctly, but if I try to update a mysql table with that variable it doesn't work.

It update the table, however when I use the actual string and no the variable.

Works:

$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$conn->query("UPDATE db SET columnx = 1 WHERE columny = 'y'");

Does not work:

$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$y = 'y';
$columnx = 'columnx';
$conn->query("UPDATE db SET $columnx = 1 WHERE columny = '$y'");

I've been at this for 2 days and still can't figure out why these strings are not being loaded to the table when I use a variable.

Any help is, as always, greatly appreciated!

`$sql = "UPDATE db SET $columnx = 1 WHERE columny = '$y'";` then echo $sql to check its correct. But anyway, use PDO, and use prepared statements! — delboy1978uk, Jan 23 '20 at 16:57
If a query does not work as expected, then you should check for [mysqli errors](http://php.net/manual/en/mysqli.error.php) to see if there are any database errors. — aynber, Jan 23 '20 at 16:57
you ought to use prepared statements anyway - that would negate this issue — Professor Abronsius, Jan 23 '20 at 16:58
@delboy1978uk I'd love to use a prepared statement, but I can't substitute the column name in a prepared statement. I tried that. I also put the whole query in a variable and echoed it to make sure it was correct - it was. — Eric Brockman, Jan 23 '20 at 17:00
I understand that. You only bind values, you can still build the query with the columns normally except for the values. give it a try! — delboy1978uk, Jan 23 '20 at 17:02
You can still use prepared statements for the values, and you should. — Jay Blanchard, Jan 23 '20 at 17:02
Yes agreed, I did that for the values, but that doesn't solve the problem I asking about. — Eric Brockman, Jan 23 '20 at 17:03
it isnt possible everything has updated when you ran the first query, and now there is nothing left to update with the second? — delboy1978uk, Jan 23 '20 at 17:03
Abstracting the sample code is great and all. But this question isn't answerable without further details (such as a concrete error message). — mario, Jan 23 '20 at 17:04
There's no error showing up, query is executing, but the update is not happening. — Eric Brockman, Jan 23 '20 at 17:08
Mysql errors do not show up like PHP errors do. You need to explicitly check for [mysqli errors](http://php.net/manual/en/mysqli.error.php). — aynber, Jan 23 '20 at 17:09
I added `mysqli_error()` to a function and called the function on a test page, but nothing comes up. — Eric Brockman, Jan 23 '20 at 17:24

MySQL queries don't work when query string is concatenated/ substituted from variables in PHP

0 Answers0