Error: SQLSTATE[42000] When I try to update a value in a table

Question

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''1235'='1235' WHERE username='wafflezzz'' at line 1' in /home/wafflez3/public_html/Project SA Theme/ipn/set.php:14 Stack trace: #0 /home/wafflez3/public_html/Project SA Theme/ipn/set.php(14): PDOStatement->execute() #1 {main} thrown in /home/wafflez3/public_html/Project SA Theme/ipn/set.php on line 14

I get that error when I use this code to change a null value to a value.

    <?php session_start(); require "../pdo_connect.php"; $usrname = $_SESSION["username"]; ?>
<title>Loading...</title>
<?php
$checker = $conn->prepare("SELECT * FROM transactions WHERE payer_user=:username AND success='1'");
$checker->bindParam(":username", $usrname);
$checker->execute();

while ($row = $checker->fetch(PDO::FETCH_BOTH)) {

   $paidscript = $row["item_name"];
   $sql = $conn->prepare("UPDATE us SET :script=:script WHERE username=:userr");
   $sql->bindParam(":userr", $usrname);
   $sql->bindParam(":script", $paidscript);
   $sql->execute();
    echo "You can now view the script!";




}

You sure it's not meant to be `$sql = $conn->prepare("UPDATE us SET script=:script WHERE username=:userr");` ? — Matt, Feb 21 '16 at 00:24
this line $sql = $conn->prepare("UPDATE us SET :script=:script WHERE username=:userr"); remove ':' from script column name — Jack jdeoel, Feb 21 '16 at 00:25
its :script because I want to get the scriptname and my system works if u set the scriptname to the scriptname then u have the script. Ik its stupid but thats just how I did it. Also the scriptname gets a column — Ch33ky, Feb 21 '16 at 00:27
Simple: You can't do this `SET :script` it's called binding a column. — Funk Forty Niner, Feb 21 '16 at 00:32
Why don't you just have a field called `scriptname` and have the script name in it? — Matt, Feb 21 '16 at 00:32

Matt · Answer 1 · 2016-02-21T00:31:16.803

1

$sql = $conn->prepare("UPDATE us SET :script=:script WHERE username=:userr");

Should probably be

$sql = $conn->prepare("UPDATE us SET script=:script WHERE username=:userr");

You're simply not meant to bind field names to parameters. If you do, it will give you an error like this.

To have a dynamic field name, you would have to do something like:

$paidscript = $row["item_name"];
$sql = $conn->prepare("UPDATE us SET {$paidscript}=:script WHERE username=:userr");

Although you shouldn't really be entering a field dynamically if it's from a user inputted value.

edited Feb 21 '16 at 00:31

answered Feb 21 '16 at 00:26

Matt

2,851
1
13
27

How could I make :script=:script work, what else would I have to add – Ch33ky Feb 21 '16 at 00:28
Read http://stackoverflow.com/questions/16885091/dynamically-change-column-name-in-pdo-statement . You cant bind parameters to field names. – Matt Feb 21 '16 at 00:29

score 0 · Answer 2 · answered Feb 21 '16 at 00:32

0

This

$sql = $conn->prepare("UPDATE us SET {$paidscript}=:script WHERE username=:userr");

Instead of this:

   $sql = $conn->prepare("UPDATE us SET :script=:script WHERE username=:userr");

answered Feb 21 '16 at 00:32

Ch33ky

31
7

Error: SQLSTATE[42000] When I try to update a value in a table

2 Answers2