Getting error with prepared statement query

Question

I'm facing a problem for several days now, and I'm staring blind at it, and can't seem to find the source myself.

I've made a class to execute my queries. The actual query executing part looks like this:

<?php
public function query( $method, $table, $data, $where_text='', $where_data='' ) {
    $this->check_db_status();

    $method = strtoupper( $method );

    $fieldsArray = array();
    $valuesArray = array();
    $paramsArray = array();
    $format = '';
    $queryText = '';

    switch( $method ) {
        case 'SELECT' :
            $queryText = 'SELECT %s FROM ' . $table . ' ';
            foreach( $data as $field => $value ) {
                $fieldsArray[] = $value;
            }
        break;
        case 'UPDATE' :
            $queryText = 'UPDATE ' . $table . ' SET %s ';
            foreach( $data as $field => $value ) {
                $fieldsArray[] = $field.'=?';
                $format .= $this->get_value_type( $value );
                $paramsArray[] = $value;
            }
        break;
        case 'DELETE' :
            $queryText = 'DELETE FROM ' . $table . ' ';
        break;
        case 'INSERT' :
            $queryText = 'INSERT INTO ' . $table . ' (%s) VALUES (%s) ';
            foreach( $data as $field => $value ) {
                $fieldsArray[] = $field;
                $format .= $this->get_value_type( $value );
                $valuesArray[] = '?';
                $paramsArray[] = $value;
            }
        break;
        default :
            $this->get_error( 'Error in method switch' );
        break;
    }

    if( $where_text ) {
        $queryText .= $where_text;
        if( $where_data ) {
            foreach( $where_data as $value ) {
                $format .= $this->get_value_type( $value );
                $paramsArray[] = $value;
            }
        }
    }

    $fields = implode( ',', $fieldsArray );
    $values = implode( ',', $valuesArray );

    $query = sprintf( $queryText, $fields, $values );

    // DEBUG
    echo '<pre>';
    echo 'query: ' . $query . '<br />
    echo 'format: ' .' . $format . '<br />';
    print_r( $paramsArray );
    echo '</pre>';


    $stmt = $this->mysqli->prepare( $query );

    if( $stmt === false or $stmt == NULL ) {
        $this->get_error( 'Error while preparing the statement' );
    }

    if( $format and $paramsArray )
        call_user_func_array( 'mysqli_stmt_bind_param', array_merge( array( $stmt, $format ), $paramsArray ) ); 

    if( $stmt->execute() ) {
        $result = 0;
        switch( $method ) {
            case 'INSERT' :
                $result = ($stmt->insert_id) ? $stmt->insert_id : true;
            break;
            case 'UPDATE' :
            case 'DELETE' :
                $result = ($stmt->affected_rows) ? $stmt->affected_rows : true;
            break;
            case 'SELECT' :
                $meta = $stmt->result_metadata();
                $fields = $result = array();
                while ($field = $meta->fetch_field()) { 
                    $var = $field->name; 
                    $$var = null; 
                    $fields[$var] = &$$var; 
                }
                call_user_func_array(array($stmt,'bind_result'),$fields);
                $i = 0;
                while( $stmt->fetch() ) {
                    $result[$i] = array();
                    foreach( $fields as $k => $v)
                        $result[$i][$k] = $v;
                    $i++;
                }
            break;
        }
        $stmt->close();
        $this->query_cnt++;

        return $result;
    }
    else {
        $this->get_error();
    }
}
?>

Now I'm trying to make an other class to store my sessions in my own database. The write function looks like this:

<?php
public function write_session( $session_id, $session_data ) {

    $query  = $this->sql->query( 'INSERT', 'sessions', array( 'ses_id'=>$session_id, 'ses_time'=>time(), 'ses_start'=>time(), 'ses_data'=>$session_data, 'ses_check'=>$this->check ), 'ON DUPLICATE KEY UPDATE ses_time=?, ses_data=?', array(time(),$session_data));

    if($query) {
        return true;
    }
    else {
        return false;
    }
}
?>

I keep getting this error:

Warning: mysqli::prepare() [mysqli.prepare]: Couldn't fetch mysqli in /.../class.db.php on line 124

Line 124 is the line with $stmt = $this->mysqli->prepare( $query );. It is triggered by the first line of the write_session.

If put in a debug part in the database class to show the query, it gives this output:

query: INSERT INTO sessions (ses_id,ses_time,ses_start,ses_data,ses_check) VALUES (?,?,?,?,?) ON DUPLICATE KEY UPDATE ses_time=?, ses_data=?
format: [siissis]
Array
(
    [0] => a98696a8416fc898f2c07e05f39735dc
    [1] => 1402201705
    [2] => 1402201705
    [3] => test|s:11:"someValuess";
    [4] => 40b17cb572d9bf5eaadad99b7904e0a4889a31d0
    [5] => 1402201705
    [6] => test|s:11:"someValuess";
)

Which seems fine to me.... what am I overlooking?

Edit

Table definition of sessions:

 sessions (
   ses_id varchar(32) NOT NULL,
   ses_time int(11) NOT NULL,
   ses_start int(11) NOT NULL,
   ses_data text NOT NULL,
   ses_check varchar(40) NOT NULL,
  PRIMARY KEY  (ses_id)
 )

Why do you call `mysqli_stmt_bind_param` when you're using mysqli through object oriented style?? shouldn't it be `$stmt->bind_param()` ? — Zerquix18, Jun 08 '14 at 04:57
@Zerquix18, it is called, see http://stackoverflow.com/a/16236455/1930721 . This way the parameters can be an array. — GreyRoofPigeon, Jun 08 '14 at 05:11
http://www.php.net/manual/ru/mysqli.query.php#103422 Point #4 — u_mulder, Jun 08 '14 at 06:48
@u_mulder this guy simply confused this 4th issue with one of the other three. It makes me wonder how freely people relay some gossips seeing even no proofcode, left alone own experience — Your Common Sense, Jun 08 '14 at 08:15
@u_mulder, how does that help me? The code works fine on other queries, but with this piece it doesn't. — GreyRoofPigeon, Jun 08 '14 at 08:45
By following this link you can learn the reason for the problem you experience — Your Common Sense, Jun 08 '14 at 08:50
Well, I'm not an expert like the most of you. It s probably point 4, but I don't get how that reflects of my code. Some more directions/ help would be wishful — GreyRoofPigeon, Jun 08 '14 at 12:50

FuzzyTree · Accepted Answer · 2014-06-10T20:15:36.613

1

A pointed out in the link provided in the comments, your problem appears to be

4. You mixed OOP and functional calls to the database object.

Specifically, you use a mysqli object here

$stmt = $this->mysqli->prepare( $query );

But then proceed to make a functional mysqli call here

if( $format and $paramsArray )
    call_user_func_array( 'mysqli_stmt_bind_param', array_merge( array( $stmt, $format ), $paramsArray ) );

So try replacing the code above with its corresponding OOP version

if($format and $paramsArray) {
    $stmt->bind_param($format,$paramsArray);
}

Also replace

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

With

$stmt->bind_param($format,$fields);

http://www.php.net//manual/en/mysqli-stmt.bind-param.php

Implement __wakeup

Another possibility is that your db connection may have been closed due to serialization. Try reconnecting by implementing __wakup i.e.

public function __wakeup()
{
    $this->mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
}

edited Jun 10 '14 at 20:15

answered Jun 09 '14 at 01:10

FuzzyTree

32,014
3
54
85

Thank you for your comment, I'm however still getting the same error. When I execute the query outside the sessions class, it does work. But inside it, it won't. Got any other ideas? – GreyRoofPigeon Jun 10 '14 at 19:18
@LinkinTED try implemeting `__wakeup` in your class. See update – FuzzyTree Jun 10 '14 at 20:15
yesterday just before I went to bed I took away the `__destruct` code, and the errors were gone. I'll try your `__wakeup` code when im home. Thank you!! – GreyRoofPigeon Jun 11 '14 at 06:55
I've added the `__wakeup` function, it however doesn't work. It still closes the connection. Can you explain why the connection is closed at all? The wakeup function isn't called (tried it by adding an `echo` to it) – GreyRoofPigeon Jun 11 '14 at 17:56
@LinkinTED can you post your `__destruct`? Also how are you initializing your `mysqli` object (besides `__wakeup`)? – FuzzyTree Jun 11 '14 at 18:04
I found it, stupid human error. In the `__destruct` function I set `$db_online = false;` which should be `$this->db_online = false;`. Before every query I check if the connection is up. If not, it's reinitialized . Thanks for your help!! – GreyRoofPigeon Jun 11 '14 at 18:09

Getting error with prepared statement query

1 Answers1