Trouble with MYSQL update column for one selection

Question

I'm having trouble getting this to update when needed. This is an optout script intended to updated the selected email row with the value of 1 in the removed column. I can't seem to get it to update and I'm thinking its an issue with my sql. Any help in understanding this is much appreciated. As a note: I'm making it to Sorry there seems to be an issue with.........

Here is the script.

<?php

if (isset($_GET['e'])) {

    include_once "../storescripts/connect_to_mysql.php";

    $email = $_GET['e'];

    $sql_delete = mysql_query("UPDATE test WHERE email='$email' SET removed = '1'");

    if (!$sql_delete) {
        echo "Sorry there seems to be and issue when trying to remove your listing. Please email Admin directly using this email address: chris@.com";
    } else {
        echo "Sorry to see you go! You will not receive our newsletter ever again unless you relist. To gain access to our newsletter again simply let us know by email at chris@.com";
    }
}
?>

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://stackoverflow.com/a/14110189/1723893). — NullPoiиteя, Aug 02 '13 at 03:12

vee · Accepted Answer · 2013-08-02T02:44:38.537

1

Try:

$sql_delete = mysql_query("UPDATE test SET removed = '1' WHERE email='$email'");

The problem is your syntax, have a look at the mysql update syntax, where the where clause should go and where set should go http://dev.mysql.com/doc/refman/5.0/en/update.html.

You would have seen this problem had you used proper error handling, like follows:

$sql_delete = mysql_query("UPDATE test SET removed = '1' WHERE email='$email'") or die(mysql_error());

Have a look at mysql_real_escape_string http://www.php.net/manual/en/function.mysql-real-escape-string.php, to prevent SQL injection. Example:

$email = mysql_real_escape_string($email);
$sql_delete = mysql_query("UPDATE test SET removed = '1' WHERE email='$email'") or die(mysql_error());

Also note that mysql_ extension are deprecated, you want to start using mysqli or PDO.

edited Aug 02 '13 at 02:44

answered Aug 02 '13 at 02:39

vee

38,255
7
74
78

You got it brotha. Thanks for helping out. So the order of these must be exact, is this correct? – Chris Aug 02 '13 at 02:40
1

@Chris yes there is a order to it – Prix Aug 02 '13 at 02:41
@Chris what have to tried to debug code ? have you tried general debugging process ? and what exactly error are getting ? – NullPoiиteя Aug 02 '13 at 02:42
Y U NO SQL injection warning .. ? – NullPoiиteя Aug 02 '13 at 02:44
@NullPoiиteя, that was going to be next. Thanks for reminding though. – vee Aug 02 '13 at 02:45
@vinodadhikary thanks for the heads up..You know I just used that this morning then forgot to use that here. Still learning..thanks for the help. What are your thoughts on sql injection prevention here? – Chris Aug 02 '13 at 02:47
@Chris, just added a statement in my answer. Sorry forgot to add the word Update. Please read on `mysql_real_escape_string` – vee Aug 02 '13 at 02:50

score 1 · Answer 2 · answered Aug 02 '13 at 02:40

1

Use SET before your WHERE clause.

UPDATE test 
SET removed = '1'
WHERE email = '$email'

answered Aug 02 '13 at 02:40

hungerstar

21,206
6
50
59

score 1 · Answer 3 · edited May 23 '17 at 11:50

1

The update syntax is

UPDATE
    table
SET
    column = value
WHERE
    condition = met

On another note, I see you're using a very unsafe method of dynamic entries ($_GET) and Mysql_* function are deprecated in new version of php >= 5.5. I'd highly recommend researching PDO for the use of bind variables otherwise you can easily get "hacked" if

$_GET['e'] = "fake' OR '1'='1" // known as sql injection

Good Read

edited May 23 '17 at 11:50

Community

1
1

answered Aug 02 '13 at 02:44

Izodn

152
8

is the above escape string valuable in this situation? – Chris Aug 02 '13 at 02:47
1

@Chris he is showing you that he can easily hack your website because you have no protection against SQL Injection, see my answer, and that you should migrate to MySQLi or PDO as mysql_ is deprectated and may in future version be removed. – Prix Aug 02 '13 at 02:49
@Prix no I mean the mysql_real_escape_string not the "fake' or ect. – Chris Aug 02 '13 at 02:56
@Chris there is a huge difference, using it, it will escape any dangerous characters while without it won't see an example on my answer. – Prix Aug 02 '13 at 03:01

score 0 · Answer 4 · answered Aug 02 '13 at 02:45

0

You are right, your UPDATE syntax is incorrect. This is the correct form:

UPDATE test
SET removed = '1'
WHERE email = '$email'

answered Aug 02 '13 at 02:45

federico-t

12,014
19
67
111

score 0 · Answer 5 · answered Aug 02 '13 at 02:52

Your query should be

mysql_query("UPDATE test SET removed = '1' WHERE email='$email'");

But please notice that this extension is deprecated. Use MySQLi or PDO_MySQ instead.

the solution in both extensions are as follows.

MySQLi:

$mysqli = new mysqli(GDB_HOST, GDB_USERNAME, GDB_PASSWORD, GDB_NAME);
$cmd = $mysqli->prepare("UPDATE test SET removed = '1' WHERE email= ? ");
$cmd->bind_param('s', $email);
$cmd->execute();

PDO

$dbh = Database::connect();
$query = "UPDATE test SET removed = '1' WHERE email= ? ";
$sth = $dbh->prepare($query);
$sth->execute(array($email));

One of the big importances of using one of these 2 extensions is the fact that you avoid any attempt of SQL injection

Trouble with MYSQL update column for one selection

5 Answers5