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.

version 2 : I could make some corrections in my code and database thanks to previous answers and this one works.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

//connect to db
$conn = mysqli_connect("localhost","root","");
$dbcon = mysqli_select_db($conn,"ershadbookstore");
if ( !$conn ) {
    die("Connection failed : " . mysqli_error());
} 
if ( !$dbcon ) {
    die("Database Connection failed : " . mysqli_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, pulisher, genre, format)
        VALUES ('test', 'test', 'test', 'test', 'test', 'test', 'test')";
if (mysqli_query($conn, $sql)) {
        $isbn="3";
        $sql =  "INSERT INTO Volume (isbn, bid, vnum, note, image)
                VALUES ('".$isbn."', '".mysqli_insert_id($conn)."', 'test', 'test', 'test')";
        if (mysqli_query($conn, $sql)) {
            $sql =  "INSERT INTO Publisher (isbn, pubnum, pyear, circulation, fpyear, pagenum, price, num)
                    VALUES ('".$isbn."', 'test', 'test', 'test', 'test', 'test', 'test', 'test')";
            if (mysqli_query($conn, $sql)) {
                $totalnum=0;
                $sql= "SELECT num FROM Publisher
                        WHERE (isbn = '".$isbn."')";
                $query= mysqli_query($conn, $sql);
                while($row = mysqli_fetch_array($query)){
                    $totalnum += $row['num'];
                }
                $sql1= "UPDATE Volume
                        SET tnum = '".$totalnum."'
                        WHERE isbn = '".$isbn."'";
                if (mysqli_query($conn, $sql1))
                {
                    echo "true";
                }
                else
                {
                    return "Error publisher table(tnum): " . $sql1 . "<br>" . mysqli_error();
                }                   
            }
            else{
                return "Error publisher table: " . $sql . "<br>" . mysqli_error();
            }
    }
    else {
        return "Error for volume table: " . $sql . "<br>" . mysqli_error();
    }
}
else {
    return "Error for book table: " . $sql . "<br>" . mysqli_error();
}

mysqli_close($conn);
?>

version 1: 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();
?>

spam: since this post is mostly code I can’t submit it unless I add some spam or delete some parts of my code and the second option just makes every thing complicated.

Answer

The problem is exactly as @PHPnoob described, but he did not point out where or why.

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
    $totalNum=$conn->query($sql);
    $sql1= "UPDATE Volume
            SET (tnum = 'test')
            WHERE (isbn= 'test')";
    if ($conn->query($sql1))
    {
        echo "true";
    }
    else
    {
        return "Error publication table: " . $sql1 . "<br>" . $conn->error;
    }
}

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.

Advertisement

Answer