insert into error sql syntax

Question

My test code is:

<?php
$connessione = mysql_connect("***", "***", "***");
mysql_select_db("***", $connessione);

$risultato = mysql_query("SELECT * FROM servem_vote", $connessione);

if(mysql_query("INSERT INTO servem_vote (uid,lastvote) VALUES ($uid,now()) ON DUPLICATE KEY UPDATE lastvote=now();

")) {
    header('location:/home.php'); }
else {
    echo "Error: " . mysql_error(); }
mysql_close($con);
?>

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 'now()) ON DUPLICATE KEY UPDATE lastvote=now()' at line 1

DB:

http://prntscr.com/ef7544

Where am I doing wrong?

You need to stop using `mysql_*` functions. They have been deprecated for years and don't even exist in current PHP releases. Please study about [PHP Data Objects](http://php.net/manual/en/book.pdo.php), known as PDO for short, for a more modern approach. — sidyll, Mar 02 '17 at 17:54
My comment has a link to the manual, with all the instructions and examples. If you don't fancy reading the manual, there are lots of other texts around the internet. Just search for PHP PDO :) — sidyll, Mar 02 '17 at 18:02
Where do you set `$uid` to a valid value? If you do not set this variable, then it will be empty, then you values section will look like the following: `VALUES (,now())` , which would yield a syntax error. Pls forget MySQL functions, use PDO or mysqli instead. It is easy to find sample codes for both of them. — Shadow, Mar 02 '17 at 18:05
I'ts correct? $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4', 'username', 'password'); $stmt = $db->query('SELECT * FROM servem_vote'); $result = $db->exec("INSERT INTO servem_vote($uid, lastvote) VAULES('$uid', 'now()') ON DUPLICATE KEY UPDATE lastvote=now()"); — Focusjob, Mar 02 '17 at 18:18

score 0 · Accepted Answer · edited May 23 '17 at 10:29

0

You are missing $uid in the code you shared. You don't set that value anywhere but you attempt to use it as part of your INSERT query.

If it's coming from form data, grab it from $_REQUEST superglobal variable before attempting to use it:

$uid = $_REQUEST['uid']

If it's NOT an integer in the MySQL table, you need to wrap it in single quotes as part of your statement.

INSERT INTO servem_vote (uid,lastvote) VALUES ('$uid',now())
ON DUPLICATE KEY UPDATE lastvote=now();

I don't know what purpose this line serves:

$risultato = mysql_query("SELECT * FROM servem_vote", $connessione);

You don't seem to do anything with the result set from this query.

MOST IMPORTANTLY: As many others have commented you need to be sanitizing your data and you should be relying on PDO or mysqli* functions to safely interact with your database. See answers here

edited May 23 '17 at 10:29

Community

1
1

answered Mar 02 '17 at 18:08

Patrick Moore

13,251
5
38
63

I tried single quotes '$uid' - Error: Incorrect integer value: '' for column 'uid' at row 1 – Focusjob Mar 02 '17 at 18:24
So, `$uid` variable is empty when used in the SQL query. Where are you expecting to get a value for this variable from? – Patrick Moore Mar 02 '17 at 18:27
I solved it by adding: if($mybb->user['uid'] > 0) { $uid = $mybb->user['uid']; $usg = $mybb->user['usergroup']; $user = get_user($uid); $name = $user['username']; } If now I would also like to add another query UPDATE how can I do? UPDATE serverm SET voto = voto + 1 WHERE id = '$id' AND NOT EXISTS (SELECT * FROM servem_vote WHERE uid = '$uid' AND DATE_FORMAT(lastvote,'%m%d%Y') = DATE_FORMAT(NOW(),'%m%d%Y')) – Focusjob Mar 02 '17 at 18:44

insert into error sql syntax

1 Answers1