insert into table error

Question

here is my code

<?php
require '../connect/conn.php';
$quest = $_POST['domanda'];
$a1 = $_POST['risposta1'];
$a2 = $_POST['risposta2'];
$a3 = $_POST['risposta3'];
$a4 = $_POST['risposta4'];
$ins = "INSERT INTO melaraider SET domanda = '$quest',riposta1 = '$a1',riposta2 = '$a2',riposta3 = '$a3',riposta4 = '$a4'";
$result = mysqli_query($con, $ins);


if(!$result){
    die("query error $ins:" . mysql_error());
}
mysql_close(); 

echo "all done!";
?>

everytime I execute that code I get a query error:

query error INSERT INTO melaraider SET domanda = 'quanto fa 2 +2?',riposta1 = '4',riposta2 = '6',riposta3 = '9',riposta4 = '2':

I really don't understand what is my mistake... can someone please help me out? Its a local test so I cant show a live version.

Is that the full error? I'm also a little confused if you're using `mysql_` or `mysqli_` since both are in your code, but you're not showing your database connection. — Samsquanch, Jul 30 '14 at 22:03
Have you tried opening a mysql client (gui or command line) and typing that insert query? — user2910265, Jul 30 '14 at 22:07

score 0 · Accepted Answer · answered Jul 30 '14 at 22:05

Try this code:

<?php
require '../connect/conn.php';
$quest = $_POST['domanda'];
$a1 = $_POST['risposta1'];
$a2 = $_POST['risposta2'];
$a3 = $_POST['risposta3'];
$a4 = $_POST['risposta4'];
$ins = "INSERT INTO melaraider (domanda, riposta1, riposta2, riposta3, riposta4) VALUES('" . $quest . "','" . $a1 . "','" . $a2 . "','" . $a3 . "','" . $a4 . "')";
$result = mysqli_query($con, $ins);


if(!$result){
    die("query error $ins:" . mysql_error());
}
mysql_close(); 

echo "all done!";
?>

yup, this works,like a charm. The only thing, like others said already, i've changed mysql_error() and mysql_close() in mysqli. — Michele Petraroli, Jul 31 '14 at 04:21

score 0 · Answer 2 · answered Jul 30 '14 at 22:07

I believe your syntax is a little off. If using all the fields just specify just the data:

$ins = "INSERT INTO melaraider VALUES ('$quest','$a1',$a2','$a3','$a4')";

or specify the fields then data

$ins = "INSERT INTO melaraider (domanda,riposta1,riposta2,riposta3,riposta4) 
VALUES ('$quest','$a1',$a2','$a3','$a4')";

http://www.w3schools.com/php/php_mysql_insert.asp

EDIT: Not quite fast enough!

score 0 · Answer 3 · answered Jul 30 '14 at 22:08

You are using a combination of INSERT + UPDATE code, you can see here the full insert options. In your case you should use

$ins = "INSERT INTO melaraider('domanda', 'riposta1', 'riposta2', 'riposta3', 'riposta4') VALUES('$quest','$a1','$a2','$a3','$a4');";

Cheers!

score 0 · Answer 4 · edited May 23 '17 at 12:23

You are using the wrong syntax for an INSERT query.

Here are the docs: http://dev.mysql.com/doc/refman/5.6/en/insert.html

Your query should look like: INSERT INTO melaraider (domanda, riposta1, riposta2, riposta3, riposta4) VALUES ('$quest', '$a1', '$a2', '$a3', '$a4');

However, before you go any further with this code, you need to look into properly sanitizing your inputs. You should never directly put POST data into a query. See: What's the best method for sanitizing user input with PHP?

score 0 · Answer 5 · edited May 23 '17 at 12:04

You should use Mysqli and not Mysql, you mixed them togheter.
Your insert query syntax was also wrong.

<?php
require '../connect/conn.php';
$quest = $_POST['domanda'];
$a1 = $_POST['risposta1'];
$a2 = $_POST['risposta2'];
$a3 = $_POST['risposta3'];
$a4 = $_POST['risposta4'];
$ins = "INSERT INTO melaraider (domanda, riposta1, riposta2, riposta3, riposta4) 
VALUES ('$quest', '$a1', $a2', '$a3', '$a4')";
$result = mysqli_query($con, $ins);


if(!$result){
    echo "query error $ins:" . mysqli_error($con); //Changed from mysql_error(). Changed from die() to echo, because you always should do mysqli_close()
} else {
    echo "all done!";
}
mysqli_close($con); //changed from mysql_close()

?>

Like @patsweet said, you should think about sanitize the data before executing the query.

Chris Otaalo · Answer 6 · 2016-01-01T09:55:24.100

Change

$ins = "INSERT INTO melaraider SET domanda = '$quest',riposta1 = '$a1',riposta2 = '$a2',riposta3 = '$a3',riposta4 = '$a4'";

to this:

$ins = "INSERT INTO melaraider(domanda, riposta1, riposta2, riposta3, riposta4) VALUES('$quest','$a1', '$a2', '$a3', '$a4')";

NB: You only use SET when you are updating a value on the database.

For Example:

$ins = "UPDATE melaraider SET domanda = '$quest' WHERE mel_id = some_id";

insert into table error

6 Answers6