Prepared Statements Problem

PHP
1

I’ve been staring at this for a long time now and have tried everything I can think of. I’m working on a class to handle all data connections using prepared connections. The query function works perfectly. However, I wanted to add functionality for a database insert. In order to do this I created a second functino called insert (because the first function throws an error during an insert because there are no return results).

However, I can not get the insert function to work for the life of me. It is very simple and I’ve broken it down into even simpler form but it continues to not work In the insert function I’ve temporarily overridden the parameters with a static sql query (that I have confirmed works). Any suggestions on why this is not working? Anything to do with multiple instances of mysqli?

I’ve tried a couple debugging techniques but have been unable to find a way to print out any error messages that might occur in the process.

<?php
class Database {
                protected $mysqli;

                public function __construct() {
                        include ('conf/db.conf');
                        $this->mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

                        $this->mysqli->autocommit(false);
                }

                public function query($sql, $type, $values) {

                        $items = array();
                        $mysqli = $this->mysqli;

                        $this->clean($values);
                        if($stmt = $mysqli->prepare($sql)){
                                call_user_func_array('mysqli_stmt_bind_param', array_merge(array($stmt, $type), $values));
                                $stmt->execute();
                                $meta = $stmt->result_metadata();
                                while($field = $meta->fetch_field()){
                                        $params[] = &$row[$field->name];
                                }

                                call_user_func_array(array($stmt, 'bind_result'), $params);

                                while($stmt->fetch()){
                                        foreach($row as $key => $val){
                                                $c[$key] = $val;
                                        }
                                        $items[] = $c;
                                }
                                $stmt->close();
                        }

                        return $items;
                }
 public function insert($sql, $type, $values) {
                        $mysqli = $this->mysqli;
                        $this->clean($values);
                        $sql = "INSERT INTO Tickets (submitDate, tDesc, pid, cid) VALUES('2010-03-10', 'test', 15, 10)";
                        if($stmt = $mysqli->prepare($sql)){
                                //call_user_func_array('mysqli_stmt_bind_param', array_merge(array($stmt, $type), $values));
                                $stmt->execute();
                                $stmt->close();
                        }

                }
}
2

could you post a var_dump of array_merge(array($stmt, $type), $values) along with the sql.

3

Note that the first function “query” works just fine. The function I’m concerned with is insert. You will notice in insert that I’ve temporarily commented out the line which uses array_merge.

Regardless: below is the var dump for an instance of the “query” function (the working function) array_merge:

{ [0]=>  object(mysqli_stmt)#5 (0) { } [1]=>  string(1) "i" [2]=>  int(14) }

SQL:

SELECT t.tid,submitDate,closedDate,tDesc,model,OS,RAM,name,location,count FROM Tickets AS t 
JOIN Computers AS c ON t.cid=c.cid 
JOIN Sites AS s ON c.sid = s.sid 
JOIN (SELECT COUNT(tid) AS count, tid 
FROM Actions GROUP BY tid) AS ac ON ac.tid=t.tid WHERE t.pid = ? 
ORDER BY t.submitDate DESC
4

I did some additional debugging of the code. All the debugging I have done indicates that the row is being inserted. But when I go to check my database there is no row there.

SQL Print: INSERT INTO Tickets (submitDate, tDesc, pid, cid) VALUES(?, ?, ?, ?)

array_merge dump: array(6) { [0]=> object(mysqli_stmt)#5 (0) { } [1]=> string(4) “ssii” [2]=> string(10) “2010-03-12” [3]=> string(3) “tes” [4]=> int(14) [5]=> int(37) }

rows inserted: 1

Status of stmt->execute: Success

These errors were printed with this code:

        if($stmt = $mysqli->prepare($sql)){
call_user_func_array('mysqli_stmt_bind_param', array_merge(array($stmt, $type), $values));
print "<b>SQL Print: </b>".$sql."\\r\
<br />";
print "<b>array_merge dump: </b>";
print var_dump(array_merge(array($stmt, $type), $values));
print "<br />";
$return = $stmt->execute();
 print "<b>rows inserted: </b>".$stmt->affected_rows."<br />";
$stmt->close();
if($return) print "<b>Status of stmt->execute:</b> Success";
else print "Error";
 }

Any other ways I can attempt to debug this problem? I’m just not sure how to interact with the prepared statements. In addition to this I have taken the above printed sql inserted the variables word for word and ran it though the database command line. It inserts just fine with no errors.

5

Maybe you’re inserting to a different database?

6

I had considered that but I don’t know how that would be possible. If you look at my first post this insert function is part of a larger class. The constructor in the class handles the database connection. In this class there is also a query function which works perfectly and inserts to the correct database. Because the database connection information is exactly the same for both functions you would assume that both functions would be able to connect just fine.

7

I was trying to replicate the code locally(I don’t use the mysqli ext), and I Just noticed this


$this->mysqli->autocommit(false);

You need to manually commit it if you want the result saved.

Also, your var_dump suggests that $values is not an array of values by reference, and they should be. In php 5.3 it’s an error if they aren’t.

8

That was the problem. I removed that once earlier and didn’t seem to make a difference. So there may have been multiple problems I have already fixed. Once again, thanks for your help.