Home > Software design >  Inconsistency of MySQL syntax errors depending on whether query made from command line or mysqli_con
Inconsistency of MySQL syntax errors depending on whether query made from command line or mysqli_con

Time:11-14

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.

  • Related