25

I have a function that generates a prepared INSERT statement based on an associative array of column names and values to be inserted into that column and a table name (a simple string):

function insert ($param, $table) {
        $sqlString = "INSERT INTO $table (".implode(', ',array_keys($param)).') VALUES ('.str_repeat('?, ', (count($param) - 1)).'?)';
        if ($statement = $this->conn->prepare($sqlString)):
            $parameters = array_merge(array($this->bindParams($param), $param));
            call_user_func_array(array($statement, 'bind_param', $parameters));
            if (!$statement->execute()):
                die('Error! '.$statement->error());
            endif;
            $statement->close();
            return true;
        else:
            die("Could Not Run Statement");
        endif;
    }

My problem is that $this->conn->prepare (it's part of a class, conn is a NEW mysqli object, which works with no issues) returns false, but does not give me a reason why!

Here is a sample $sqlString that gets built for the prepare statement:

INSERT INTO students (PhoneNumber, FirstName, MiddleInit, LastName, Email, Password, SignupType, Active, SignupDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

Can anyone see a problem with this parameterized statement? Any reason the prepare function would return false?

  • 2
    Do you get an error message (see http://php.net/manual/en/mysqli.error.php when prepare returns false ? ) ; if yes, what is it ? – Pascal MARTIN Aug 02 '09 at 20:53
  • 2
    Have you tried running the actual INSERT statement in your database, to see that you have the correct SQL? – DOK Aug 02 '09 at 20:54
  • 2
    I just turned on mysqli_report(MYSQLI_REPORT_ALL) to get a better understanding of what was going on - turns out that one of my field names was incorrect - you'd think that prepare() would throw an exception, but it fails silently. –  Aug 02 '09 at 21:01
  • @Pascal - Prepare() returns false on error, it does not return an ACTUAL error I could read, it just failed. –  Aug 02 '09 at 21:02
  • @Andrew : that is why I suggested using a method/property that should indicate the error's message. Anyway : your problem is solved ? (from your previous comment, it seems to be, but i'd prefer to be sure) – Pascal MARTIN Aug 02 '09 at 21:21
  • @Pascal: Problem Solved! Thanks! Now on to other errors :) –  Aug 02 '09 at 21:26
  • Then, can you say something visible, at the beginning of your question, to indicate that the problem is solved ? That way, people won't try solving it again (and have more time to help others ;-) ) ; obviously, you can't accept an "answer", as there is none and you are the one who solved the problem ^^ – Pascal MARTIN Aug 02 '09 at 21:29
  • If you want exceptions use pdo. All you have to do is to set the error mode to "throw exceptions": $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); http://php.net/pdo – VolkerK Aug 02 '09 at 23:00
  • I made a mistake of trying to bind `$tableName` and `$tableColumnName`s. I now realize it was dumb to "*think*", that `->prepare($query)` is just a find-and-replace on steroids. It actually does something ie. prepares statement. Can't prepare dinner without any ingredients ;) – s3c May 04 '21 at 13:02

2 Answers2

73

I'm copying the solution into this answer so this can be given an upvote, otherwise the question will appear in the "unanswered questions" forever. I'm marking this answer CW so I won't get any points.

@Andrew E. says:

I just turned on mysqli_report(MYSQLI_REPORT_ALL) to get a better understanding of what was going on - turns out that one of my field names was incorrect - you'd think that prepare() would throw an exception, but it fails silently.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    @P5Coder, PHP and MySQL are the worst solutions, except for all the other solutions that have been tried. :-) – Bill Karwin Mar 31 '14 at 17:56
  • @BillKarwin You must never have tried other high level languages or databases. PHP is arguably the worst designed language ever and MySQL is not ACID compliant using its default engine. – vee_ess Jun 27 '14 at 21:06
  • 1
    @vee_ess, the default engine is InnoDB since MySQL 5.5, released in 2010. InnoDB supports ACID about as well as most other databases. – Bill Karwin Jun 27 '14 at 21:10
  • @BillKarwin That's a good point regarding InnoDB becoming the default engine in recent versions. It's still certainly not better than SS, DB2, or Oracle (all ACID compliant) when considering IO and memory performance or scalability. – vee_ess Jun 27 '14 at 21:22
  • 3
    @vee_ess, well every RDBMS needs careful tuning and constant monitoring to keep it working. They all have their idiosyncrasies. But MySQL has the advantage of being free. I have consulted for dozens of companies who are quite successful relying on MySQL. But you're entitled to your opinion of course. – Bill Karwin Jun 27 '14 at 21:38
1

These are the main reasons I got this issue.

  • Error in the query
  • Trying to run two simultaneous queries (commands out of sync)

First you need to know the exact cause. for that, add following code.

if ($stmt === FALSE) {
   die ("Error: " . $mysqli->error);
}

If you are running two simultaneous queries, store values from your first statement will resolve the issue.

$first_stmt->store_result()
javatar
  • 1,332
  • 1
  • 17
  • 24
  • the problem is with this answer that `$mysqli->error` is quite frequently "" unless you manually turn on error report which for mysqli usually defaults to off – MikeT Apr 06 '23 at 08:38