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?](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) — Dharman, Jan 24 '19 at 20:19
Are you using database transactions ? You might need to *commit* your changes so they become permanent in database. — GMB, Jan 24 '19 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, Jan 24 '19 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, Jan 24 '19 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, Jan 24 '19 at 20:32
@GMB - Where do you see the OP using transactions? (I do think this code is a good candidate for it though). — M. Eriksson, Jan 24 '19 at 20:46
@MagnusEriksson : I was just asking, because that *could have been* a reason for data not showing while all db ops ran fine (which was the inital problem statement). But you are correct, there is no sign of transactions in the code. — GMB, Jan 24 '19 at 20:49
I added "mysqli_report(..." in the begennig and it helped a lot. thank you very much, it helped a lot. but there is another problem so I edited my question and added the new code. — Leyla Krz, Jan 24 '19 at 20:55
@Dharman " $sql= "SELECT SUM(num) FROM Publisher WHERE (isbn=='10')"; " inserts the result of Select statement (a number) into $sql variable. this number is necessary and must be written in the table. i could explain more if you want. — Leyla Krz, Jan 24 '19 at 21:00
No it doesn't work like that. Try something along the lines `$totalNum->num_rows`, although you are using `SUM(num)` which defeats the whole purpose — Dharman, Jan 24 '19 at 21:06
@Dharman doesn't "$totalNum->num_rows" return the number of selected rows? if yes, I want sum of the values in a column, so it doesn't help. — Leyla Krz, Jan 24 '19 at 21:17

Dharman · Accepted Answer · 2021-09-10T21:34:57.490

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.

score 0 · Answer 2 · answered Jan 24 '19 at 20:29

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 '19 at 20:29

exception_thrown

529
1
7
22

"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](http://php.net/manual/en/mysqli.query.php) – Dharman Jan 24 '19 at 20:38

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

2 Answers2