尝试使用PHP / Mysqli将数据插入数据库

Question

I'm trying to execute an Insert query to write data into a Database. I'm using Mysqli and PHP. The code looks OK for me. However, every time I go to the webpage to check if the form works, the query gets executed an a new row is created in the DB (empty).

I'm pretty sure there is something wrong with the last if statement. Could you advise?

BTW, the snippet is only for the PHP to execute the sql query, since the form is working just fine.

Thanks!

$servername = "localhost";
$username = "root";
$password = "mysqlpassword";
$dbname = "bowieDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$album = $_POST['album'];
$relyear = $_POST['relyear'];
$label = $_POST['label'];
$chart = $_POST['chart']; 
$track1 = $_POST['track1'];
$track2 = $_POST['track2'];
$track3 = $_POST['track3'];
$track4 = $_POST['track4'];
$track5 = $_POST['track5'];

$sql = "INSERT INTO Albums (album, relyear, label, chart, track1, track2,     track3, track4, track5)
VALUES ('$album', '$relyear', '$label', '$chart', '$track1', '$track2',     '$track3', '$track4', '$track5')";

$result = mysqli_query($conn, $sql);
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

Answer 1

You are mixing Procedural and Object Orientated SQL interactions.

This is Procedural:

$result = mysqli_query($conn, $sql);

This is Object Orientated:

$conn->query($sql) 

You can not use both with the same connection details, you should do one or the other throughout your code. The best one to use is Object Orientated approach, so rework the Procedural code to:

$result = $conn->query($sql);
if ($result) {
...

So actually you can simply remove the line starting $result = ... and let the IF statement query you already have handle itself.

Other notes:

• Use MySQL error feedback such as checking if(!empty($conn->error)){print $conn->error;} after SQL statements. See example code below...

• Use the following PHP error feedback too, set at the very top of your PHP page:

...

  error_reporting(E_ALL);
  ini_set('display_errors',0);
  ini_set('log_errors',1);

• you need to read up and be aware of SQL injection that can destory your database should someone POST data that also happens to be MySQL commands such as DROP.

---

Code for Comment:

if ($_SERVER['REQUEST_METHOD'] == "POST") {
     //run SQL query you already have coded and assume
     // that the form has been filled in. 
    $result = $conn->query($sql);
    if ($result) { 
        //all ok
     }
     if(!empty($conn->error)) {
        print "SQL Error: ".$conn->error;
     } 
}