SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Prepared Statements Problem

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

                    }
    }
    Follow Me On Twitter: BryceRay

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    could you post a var_dump of array_merge(array($stmt, $type), $values) along with the sql.

  3. #3
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code PHP:
    { [0]=>  object(mysqli_stmt)#5 (0) { } [1]=>  string(1) "i" [2]=>  int(14) }
    SQL:
    Code 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
    Follow Me On Twitter: BryceRay

  4. #4
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code PHP:
            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\n<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.
    Follow Me On Twitter: BryceRay

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe you're inserting to a different database?

  6. #6
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Follow Me On Twitter: BryceRay

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was trying to replicate the code locally(I don't use the mysqli ext), and I Just noticed this
    PHP Code:
    $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. #8
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Follow Me On Twitter: BryceRay


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •