Skip to content
Advertisement

using “INSERT INTO” statement in php code doesn’t work

I’m trying to build a database for a bookstore with 3 tables: Book, Volume and Publication. I’m using mysqli() and the code neither works nor echoes any errors.

<?php
//connect to db
$conn = new mysqli("localhost", "root", "", "ershadbookstore");
if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
#new recoed: if the new record is inserted into book successfully, another new record is inserted into volume table. the same goes for volume and phblication table. at the end the total number of this volumn is editted in the rows with the same isbn.
$sql = "INSERT INTO Book (name, vnum, writer, translator, publisher, genre, format)
        VALUES ('test', 'test', 'test', 'test', 'test', 'test', 'test')";
if ($conn->query($sql) === TRUE) {
        $last_bid = $conn->insert_id;
        $sql =  "INSERT INTO Volume (isbn, bid, vnum, note, image)
                VALUES ('test', 'test', 'test', 'test', 'test')";
        if ($conn->query($sql) === TRUE) {
            $sql =  "INSERT INTO Publication (isbn, pubnum, pyear, circulation, fpyear, pnum, price, num)
                    VALUES ('test', 'test', 'test', 'test', 'test', 'test', 'test', 'test')";
            if ($conn->query($sql) === TRUE) {
                $sql= "SELECT SUM(num) FROM Publication
                        WHERE (isbn='test')";
                if ($conn->query($sql) === TRUE) {
                    $totalNum=$conn->query($sql);
                    $sql1= "UPDATE Volume
                            SET (tnum = test)
                            WHERE (isbn= test)";
                    if ($conn->query($sql1) === TRUE)
                    {
                        echo "true";
                    }
                    else
                    {
                        return "Error publication table: " . $sql1 . "<br>" . $conn->error;
                    }
                }                           
            }
            else{
                return "Error publication table: " . $sql . "<br>" . $conn->error;
            }
    }
    else {
        return "Error for volume table: " . $sql . "<br>" . $conn->error;
    }           
}
else {
    return "Error for book table: " . $sql . "<br>" . $conn->error;
}           
$conn->close();
?>

Advertisement

Answer

The problem is that you are checking with === TRUE.

As PHP Manual says:

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

While your error checking would work for INSERT or UPDATE queries the SELECT query will not return true. In fact checking like this for boolean values is completely unnecessary.

Remove the check for === TRUE and your code should work fine.

$sql= "SELECT SUM(num) FROM Publication
        WHERE isbn='test'";
if ($conn->query($sql)) { // removed === TRUE
    $sql1= "UPDATE Volume
            SET tnum = 'test'
            WHERE isbn= 'test'";
    if ($conn->query($sql1))
    {
        echo "true";
    }
    else
    {
        return "Error publication table: " . $sql1 . "<br>" . $conn->error;
    }
}  

If you enable mysqli error reporting, you don’t need any if statements, which will make your code much simpler:

$sql = "SELECT SUM(num) FROM Publication
    WHERE isbn='test'";
$conn->query($sql);

$sql1 = "UPDATE Volume
    SET tnum = 'test'
    WHERE isbn= 'test'";
$conn->query($sql1);

echo "true";

In addition, your values in the last update queries are missing quotes.

SET (tnum = 'test')
WHERE (isbn= 'test')";

Also there is no need to execute your SELECT query twice to get the values. You should refactor your code so that the second query ($totalNum=$conn->query($sql);) is not needed.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement