using "INSERT INTO" statement in php code doesn't work

Question

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

Try with mysqli exceptions enabled: How to enable MySQLi exception mode? — Dharman
– Dharman ♦, Commented Jan 24, 2019 at 20:19
Are you using database transactions ? You might need to commit your changes so they become permanent in database. — GMB
– GMB, Commented Jan 24, 2019 at 20:20
There is a problem in the last (UPDATE) query where the values need to be surrounded with quotes : UPDATE Volume SET (tnum = test) WHERE (isbn= test) => UPDATE Volume SET (tnum = 'test') WHERE (isbn= 'test'). Is this a typo ? — GMB
– GMB, Commented Jan 24, 2019 at 20:23
I added "mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);" as explained (at the end before "$con->close()") but there is no result again. — Leyla Krz
– Leyla Krz, Commented Jan 24, 2019 at 20:31
I'm a newcomer in both php and English language and I have no idea what are database transactions exactly but I think the answer is no. thank you any way. — Leyla Krz
– Leyla Krz, Commented Jan 24, 2019 at 20:32

Dharman · Accepted Answer · 2021-09-10 21:34:57Z

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.

exception_thrown · Accepted Answer · 2019-01-24 20:29:33Z

0

Also remove the triple equals === you are not comparing so you don not need a comparison operator so instead use !== FALSE

Example

if ($conn->query($sql) !== FALSE)

so this is saying if its not FALSE, than its TRUE so it will continue to run.

answered Jan 24, 2019 at 20:29

exception_thrown

5831 gold badge8 silver badges24 bronze badges

1 Comment

Dharman Over a year ago

"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." - mysqli::query

Collectives™ on Stack Overflow

using "INSERT INTO" statement in php code doesn't work

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related