SQL creates new row when I run an update command

Question

I have this script (below), which is meant to update a value for a specific row in a table. I am searching by ID, which is set as an auto-incrementing, primary key integer. When I run this script, the row is not updated- instead, a completely new row is created.

Here's the code:

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if (!$link) {
    die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db(DB_NAME, $link);

if (!$db_selected) {
die('Can\'t use ' . DB_NAME . ': ' . mysql_error());
}

$id = $_POST['id'];
$certLevel = $_POST['certlevel'];

$sql = "UPDATE jcontact SET certlevel='$certLevel' WHERE id=$'id'";

if (!mysql_query($sql)) {
    die('Error: ' . mysql_error());
}

mysql_close();

Well look at $'id'. This is a great example of why you should stop using mysql_* functions and update to mysqli or PDO and use prepared/paramatized statements. — Sean
– Sean, Commented Feb 3, 2016 at 5:08

AbraCadaver · Accepted Answer · 2016-02-03 15:44:02Z

1

First of all, I think its time to travel from MySQL into MySQLi or PDO.

Second, you need to use ' for strings and your mistake in your WHERE clause:

id=$'id'

You can easily use like this:

id=$id

or:

id='$id'

edited Feb 3, 2016 at 15:44

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

answered Feb 3, 2016 at 5:11

user4486345

Sign up to request clarification or add additional context in comments.

Comments

Mr. Engineer · Accepted Answer · 2016-02-03 05:09:02Z

1

Replace this :

$sql = "UPDATE jcontact SET certlevel='$certLevel' WHERE id=$'id'";

With this :

$sql = "UPDATE jcontact SET certlevel='$certLevel' WHERE id='$id'";

answered Feb 3, 2016 at 5:09

Mr. Engineer

3,5254 gold badges20 silver badges34 bronze badges

6 Comments

Bora Over a year ago

Thanks, but it didn't work. Any other possible changes?

Bora Over a year ago

Also, the ID is a number

Mr. Engineer Over a year ago

Write your query in phpmyadmin and see what happen.

Mr. Engineer Over a year ago

Then please provide your complete code. I mean from where do you call this script (e.g HTML).

Bora Over a year ago

<form action="connect.php" method="post" /> <p>Judge ID: <input type="number" name="id" /></p> <p>Certification Level: <input type="text" name="certlevel" /></p> <input type="submit" value="Submit" /> </form>

|

Collectives™ on Stack Overflow

SQL creates new row when I run an update command

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related