error while updating a database

Question

    $record_id1=$_REQUEST['record_id'];

    echo $record_id1;
    echo $dor1;
    if(isset($_REQUEST['updatebtn']))
    {                    
        $status=$_REQUEST['up'];
        $sql="UPDATE record SET dor='$dor1' WHERE record_id=$record_id1";
        mysql_query($sql) or die(mysql_error());
    }

I am trying to update my record table which will take record_id1 in its WHERE clause and will simply update dor=$dor here dor is properly set in the page .The problem is when i try to execute this page i get the following error :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax...

i have spent hours and was not able to point out the bug when i use ... WHERE record_id=7(any numeric value) the table gets updated but when i am updating it with $record_id1 i am getting the above error.

Data Type dor =String record_id=int

or try adding quotes around `$record_id1` in the `WHERE` clause. — Sablefoste, Jul 08 '14 at 19:10
but dor's type is text so `'$dor1'` must be used...and @VotetoClose can you please show me one example to do this job — rick, Jul 08 '14 at 19:11
[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://j.mp/PoWehJ). **You are also wide open to [SQL injections](http://stackoverflow.com/q/60174)** — John Conde, Jul 08 '14 at 19:11
Also, post the **whole** error message. You left off the usual part. — John Conde, Jul 08 '14 at 19:12
may be you have to add trim. $record_id1=trim($_REQUEST['record_id']); — fortune, Jul 08 '14 at 19:31

score 1 · Answer 1 · answered Jul 08 '14 at 19:22

Is your table column name record_id1 with a 1 on the end or just record_id. According to your question you can do a custom query using and numberical value assigning it to record_id1 (with a 1 on the end).

If your column name has a 1 then you just need to update your query to match the correct name.

score 0 · Answer 2 · answered Jul 08 '14 at 19:14

You should be escaping your variables:

$sql = sprintf(
    "UPDATE record SET dor='%s' WHERE record_id=%s",
    mysql_real_escape_string($dor),
    mysql_real_escape_string($record_id1)
);

and echo your SQL to make sure what you're running is what you're expecting:

echo $sql;

Numeric values can have quotes around them as well, so this will work too:

$sql = sprintf(
    "UPDATE record SET dor='%s' WHERE record_id='%s'",
    mysql_real_escape_string($dor),
    mysql_real_escape_string($record_id1)
);

Now, all the mysql_* functions are deprecated so you should be using the mysqli_* equivalents.

score 0 · Answer 3 · answered Jul 08 '14 at 19:21

0

$sql="UPDATE record SET dor='$dor1' WHERE record_id=$record_id1";

The problem is when i try to execute this page i get the following error :

-

when i use ... WHERE record_id1=7(any numeric value) the table gets updated

is that 1 after the record_id (column name) correct? is your column called record_id or record_id1? If its really record_id1 you ofc. need to use:

 ... WHERE record_id1=$record_id1 ...

answered Jul 08 '14 at 19:21

dognose

20,360
9
61
107

my column name is record_id – rick Jul 08 '14 at 19:23
and i am storing the id which i am getting from some other page in record_id1 – rick Jul 08 '14 at 19:24
@rick then why "... WHERE record_id1=7" – fortune Jul 08 '14 at 19:25
@rick then please fix your description about the working manual query - that's confusing. – dognose Jul 08 '14 at 19:27

score 0 · Answer 4 · answered Jul 08 '14 at 19:40

0

Sometimes you have an unwanted space character in your variable $record_id1.

Please use trim(), if it so.

$record_id1=trim($_REQUEST['record_id']);

Query:

$sql="UPDATE `record` SET `dor`='$dor1' WHERE `record_id`='$record_id1'";

answered Jul 08 '14 at 19:40

fortune

3,361
1
20
30

score -1 · Answer 5 · answered Jul 08 '14 at 19:11

-1

Try putting qoutes around your WHERE clause, like this:

$sql="UPDATE record SET dor='$dor1' WHERE record_id='$record_id1'";

And just one more thing. Please use MySQLi or PDO and not MySQL. MySQL is deprecated. And using MySQLi or PDO allows you to do Prepared Statements

answered Jul 08 '14 at 19:11

user3719477

110
9

You dont know the content of `$dor1` or `$record_id1` - How can you decide whether quotes are needed or not? Trial-and-error-advice is not an answer. – dognose Jul 08 '14 at 19:14
@dognose Then see user583576's answer – user3719477 Jul 08 '14 at 19:18
Other worse answers wouldnt make yours better :) – dognose Jul 08 '14 at 19:22

score -1 · Answer 6 · answered Jul 08 '14 at 19:11

-1

$sql="UPDATE record SET dor = '$dor1' WHERE record_id = '$record_id1'";

answered Jul 08 '14 at 19:11

user583576

621
6
16

error while updating a database

6 Answers6