A simple MySQL query is returning a syntax error over mysqli_connect, but the identical, copy-pasted query is successful in both the CLI and phpMyAdmin.
Consider this example for MySQL 8.0:
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "USE aTable; INSERT INTO aTable (`aColumn`) VALUES ('aValue');";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
When the PHP runs it prints an error:
USE aTable; INSERT INTO aTable (aColumn
) VALUES ('aValue');
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 'INSERT INTO aTable (aColumn
) VALUES ('aValue')' at line 1
However when the same query is pasted into phpMyAdmin it tells me:
1 row inserted.
Inserted row id: 6 (Query took 0.0063 seconds.)
USE aTable; INSERT INTO aTable (aColumn
) VALUES ('aValue');
Why are they different?
CodePudding user response:
Remove the USE from the PHP code because the database is already selected in the mysqli_connect method.
$sql = "USE aTable; INSERT INTO aTable (`aColumn`) VALUES ('aValue');";
Should be:
$sql = "INSERT INTO aTable (`aColumn`) VALUES ('aValue');";
Also, make sure that you're not using the database name in the INSERT query.