What is the error in MySQL query.?

Question

I am new to PHP & MySQL and I am getting an error and I don't understand what is the error in my query? Basically I am allowing user to fill this form and submit. After the form is submitted it should go in MySQL database, on myphp the code of Insertion is working all right but on my website its throwing me the error.

This is the error I am getting:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com), AF_Name = VALUES('Name of A Person'), V_City = VALU' at line 7

And after submitting the form it moved to another PHP page. I want it to stay on a page where form is present. This code & form are on different PHP pages.

This is my code:

<?php

    $con = mysql_connect(host, User_Name, Password, DB_Name);

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

mysql_select_db("user", $con);

$sql="INSERT INTO request (RF_Name, RL_Name, R_Email, AF_Name, V_City, V_Country, Time, Date, Message)
    VALUES
    ('$_POST[RFname]', '$_POST[RLname]', '$_POST[sender]', '$_POST[AFname]', '$_POST[Vcity]', '$_POST[Vcountry]', '$_POST[time]', '$_POST[date]', '$_POST[body]')
ON DUPLICATE KEY UPDATE
     RF_Name = VALUES($_POST[RFname]),
     RL_Name = VALUES($_POST[RLname]),
     R_Email = VALUES($_POST[sender]),
     AF_Name = VALUES($_POST[AFname]),
     V_City = VALUES($_POST[Vcity]),
     V_Country = VALUES($_POST[Vcountry]),
     Time = VALUES($_POST[time]),
     Date = VALUES($_POST[date]),
     Message = VALUES($_POST[body])";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?>

You're using the `values()` function wrong. it should be `fieldname=values(fieldname), or `fieldname=$value_for_field` — Marc B, Jun 19 '14 at 16:18
Your present code is open to [**SQL injection**](http://stackoverflow.com/q/60174/). Use [**`mysqli_*` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO**](http://php.net/pdo) with [**prepared statements**](http://php.net/pdo.prepared-statements). — Funk Forty Niner, Jun 19 '14 at 16:32

Nawed Khan · Accepted Answer · 2014-06-19T16:22:27.727

0

Enclose your values in quotes when updating:

  RF_Name = VALUES('$_POST[RFname])',
     RL_Name = VALUES('$_POST[RLname]'),
     R_Email = VALUES('$_POST[sender]'),
     AF_Name = VALUES('$_POST[AFname]'),
     V_City = VALUES('$_POST[Vcity]'),
     V_Country = VALUES('$_POST[Vcountry]'),
     Time = VALUES('$_POST[time]'),
     Date = VALUES('$_POST[date]'),
     Message = VALUES('$_POST[body]')

edited Jun 19 '14 at 16:22

answered Jun 19 '14 at 16:15

Nawed Khan

4,393
1
10
22

values are from the on duplicate update – exussum Jun 19 '14 at 16:19
Thanks exussm, I have update my answer accordingly. – Nawed Khan Jun 19 '14 at 16:22
And The form goes to another form.. can u help me in that too..?? – MaXx Jun 19 '14 at 16:29

exussum · Answer 2 · 2014-06-19T16:24:18.493

0

it should be

R_Email = VALUES(R_Email)

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

You want the value of the proposed R_email column

As others have noted you need to protect yourself against SQL injection and look at upgrading mysql_* functions to either Mysqli or PDO

edited Jun 19 '14 at 16:24

answered Jun 19 '14 at 16:16

exussum

18,275
8
32
65

Sidenote: I doubt that the email address is an `int`, therefore the value must be quoted. – Funk Forty Niner Jun 19 '14 at 16:25
@Fred-ii- - its referencing the column Check the syntax for ON DUPLICATE – exussum Jun 19 '14 at 16:26
I have, and the examples in there show numbers (`int`s) and not quotable values such as `[email protected]` which will throw an error. – Funk Forty Niner Jun 19 '14 at 16:27
I am taking INPUT from a user.. So I can't hardcode the E-Mail address or the "@gmail.com or @live.com" part... – MaXx Jun 19 '14 at 16:27
Fred you quote the column name. The variable part comes from the insert statement originally. The function gets the value you were trying to insert – exussum Jun 19 '14 at 17:23
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); is the example which refs the vluwes from a and b – exussum Jun 19 '14 at 17:26

score 0 · Answer 3 · answered Jun 19 '14 at 16:17

0

Your problem is that you're not sanitizing your query parameters. Try using PDO or mysqli_* with parameterized queries. This will ensure that your parameters are correctly encoded in the query and won't mess up the syntax.

answered Jun 19 '14 at 16:17

Dennis Degryse

104
4

What is the error in MySQL query.?

3 Answers3