Mysqli prepared stmt - using object multiple times?

Hello all,

I am using mysqli prepared statements and I have moved to my first medium sized project which i'm having a problem with objects now. I have a broad understanding of the objects and instantiating an object (i think).

Here it is:

I have a page that i include in the pages using the database 'connectvars.php:

PHP Code:

---

$mysqli=new new mysqli('localhost', 'uname', 'pass', 'db'); 


---

I also have a page 'functions.php' which i have all my php functions that i include:

PHP Code:

---

function getUsers(){
global $mysqli;
$stmt=$mysqli->prepare(..........prepared statement etc.
}
function getPhoneNumbers(){
global $mysqli;
$stmt=$mysqli->prepare(..........prepared statement etc.
}
function getPictures(){
global $mysqli;
$stmt=$mysqli->prepare(..........prepared statement etc.
} 


---

Finally on my index page, if i call multiple functions on the same page, i get an object error, saying "Call to a member function bind_param() on a non-object", i've removed my $stmt->bind_param() and used error reporting and it's saying that the $stmt=$mysqli->prepare() failed.

I have a hunch it is because i'm trying to use the same instance of $mysqli at the same time? Am i close?

Thanks for the help!

PHP Code:

---

// this *should* be causing an error as well.
//$mysqli=new new mysqli('localhost', 'uname', 'pass', 'db');
// only use "new" once.
$mysqli = new mysqli( 'localhost','uname','pass','db' ); 


---

Assuming your connection script is running before you use the functions, I don't see why you'd be having any problems...
can you post the complete error (do print $mysqli->error;) and the complete code of the function?

-----------------------edit-----------------------
Also, while what you're doing should "work" just fine, it's doing so in a clumsy way and not really taking advantage of object-oriented programming. For example, you might organize it something like this (and please, feel free to ignore me if you'd rather focus on "getting it to work" first):

PHP Code:

---

<?php

class myDB{
    
    // your database credentials
    const DBhost = 'localhost';
    const username = 'uname';
    const password = 'pass';
    const DBname = 'db';

    private $connection;  // holds your mysqli connection
    private $statements;  // holds your prepared statement objects
    
    public function __construct(){
    try{
        // connect to database
        if( ($this->connection = new mysqli( self::DBhost,self::username,self::password,self::DBname )) === false ){
            throw new Exception( 'Could not connect to database.' );
        }
    }catch( Exception $e ){ 
        // bad error handling. don't do it this way in RL :)
        exit( $e->getMessage() );
    }
    }
    
    public function getUser( $param ){
    try{
        // prepare statement if not already done
        if( empty( $this->statements[__METHOD__] ) ){
            if( ($this->statements[__METHOD__] = $this->connection->prepare( "MY ? STATEMENT" )) === false ){
                throw new Exception( 'Failed to prepare statement: '.$this->connection->error );
            }
        }
        // bind input param
        if( ($this->statements[__METHOD__]->bind_param( 's',$param )) === false ){
            throw new Exception( 'Failed to bind param: '.$this->connection->error );
        }
        // execute statement
        if( ($this->statements[__METHOD__]->execute()) === false ){
            throw new Exception( 'Failed to execute statement: '.$this->connection->error );
        }
        // bind result
        if( ($this->statements[__METHOD__]->bind_result( $result )) === false ){
            throw new Exception( 'Failed to bind results: '.$this->connection->error );
        }
        // fetch result
        if( $this->statements[__METHOD__]->fetch() === false ){
            throw new Exception( 'Failed to fetch results: '.$this->connection->error );
        }
        // return result
        return $result;
    }catch( Exception $e ){ 
        exit( $e->getMessage() );
    }
    }
    
}

---

this could be abstracted even more; for example, you could have a single method that prepared/executed any of your statements, and you would specify the statement in the args. but look at this first.

PHP Code:

---

function getServiceTypes($id){
    global $mysqli;

    if($stmt=$mysqli->prepare("INSERT INTO sales_file_type WHERE `file`=?")){

    $stmt->bind_param('s',$id);
    $stmt->execute();
    $stmt->bind_result($z,$x,$a);
    while($stmt->fetch()){
        switch($a){
        case 1: echo 'Patrol';
            break;
        case 2: echo 'Guard';
            break;
        case 3: echo 'Camera';
            break;
        case 4: echo 'Special Event';
            break;
        case 5: echo 'RFP/RFQ';
            break;
        }
    }
    }
}


function getFileContacts($file){
    global $mysqli;
    
    $stmt=$mysqli->prepare("SELECT * FROM sales_file_contact WHERE `id`=?");
    $stmt->bind_param('s', $file);
        
} 


---

Both of these post the following error:

Code:

---

Fatal error: Call to a member function bind_param() on a non-object

---

throw in some error checking before you try to bind params:

PHP Code:

---

function getServiceTypes($id){
    global $mysqli;

    if($stmt=$mysqli->prepare("INSERT INTO sales_file_type WHERE `file`=?")){

    if( !($stmt instanceof mysqli_stmt) ){
        // BAD error checking. don't do this in RL :)
        exit( "prepare() didn't work: ".$mysqli->error );
    }

    $stmt->bind_param('s',$id);
    $stmt->execute();
    $stmt->bind_result($z,$x,$a);
    while($stmt->fetch()){
        switch($a){
        case 1: echo 'Patrol';
            break;
        case 2: echo 'Guard';
            break;
        case 3: echo 'Camera';
            break;
        case 4: echo 'Special Event';
            break;
        case 5: echo 'RFP/RFQ';
            break;
        }
    }
    }
} 


---

... although I can tell you now that the problem is your query. You can't INSERT ... WHERE. Are you trying to UPDATE?
--edit--
also, you'll only get results if you SELECT. Both INSERT and UPDATE will return true/false.

Duuuuuude! Sometimes sleep deprivation and too much coffee will make you overlook the DUMB little mistakes!!

THANKS!

prepare() didn't work: Commands out of sync; you can't run this command now

could you post your current query?

try adding this after you do $stmt->fetch():

PHP Code:

---

$stmt->close(); 


---

if that doesn't work, try this (though it shouldn't really be necessary):

PHP Code:

---

while( $mysqli->next_result() );  // yes, this is not a typo
$stmt->close(); 


---

this is all based on searching for the error message - I've never experienced this myself. I'll try to reproduce it.

PHP Code:

---

function getServiceTypes($id){
    global $mysqli;

    $stmt=$mysqli->prepare("SELECT `id`,`file`,`type` FROM sales_file_type WHERE `file`=?");
        if( !($stmt instanceof mysqli_stmt) ){
        // BAD error checking. don't do this in RL :)
        exit( "prepare() didn't work: ".$mysqli->error );
            }


    $stmt->bind_param('s',$id);
    $stmt->execute();
    
    $stmt->bind_result($z,$x,$a);
    while($mysqli->next_result()){
        switch($a){
        case 1: echo 'Patrol';
            break;
        case 2: echo 'Guard';
            break;
        case 3: echo 'Camera';
            break;
        case 4: echo 'Special Event';
            break;
        case 5: echo 'RFP/RFQ';
            break;
        }
    }$stmt->close();

} 


---

produced: prepare() didn't work: Commands out of sync; you can't run this command now

I've tried to reproduce your error, but I can't. Is this the first time you try to use a prepared statement in this script?
Have you sent *any* queries before this one?

also:

PHP Code:

---

//  NOT like this:
/*
while($mysqli->next_result()){ 
        switch($a){ 
        case 1: echo 'Patrol'; 
            break; 
        case 2: echo 'Guard'; 
            break; 
        case 3: echo 'Camera'; 
            break; 
        case 4: echo 'Special Event'; 
            break; 
        case 5: echo 'RFP/RFQ'; 
            break; 
        } 
    }$stmt->close(); 
*/
//  like this instead:
while($stmt->fetch()){ 
        switch($a){ 
        case 1: echo 'Patrol'; 
            break; 
        case 2: echo 'Guard'; 
            break; 
        case 3: echo 'Camera'; 
            break; 
        case 4: echo 'Special Event'; 
            break; 
        case 5: echo 'RFP/RFQ'; 
            break; 
        } 
    }
// use while loop only if necessary
//    while( $mysqli->next_result() );
// and, after some research, I discovered it should actually be like this:
//    while( $mysqli->more_results() ){ $mysqli->next_result(); }
    $stmt->close(); 


---

---------------------------edit---------------------------
actually, I *can* get that error message, but only if I use different prepared statements without closing the previous statements first:

PHP Code:

---

/*  psuedocode  */
$stmt_1 = $mysqli->prepare( "SOME SQL" );
$stmt_2 = $mysqli->prepare( "OTHER SQL" );

$stmt_1->bind_params()->execute()->bind_results()->fetch(); // works
$stmt_2->bind_params()->execute()->bind_results()->fetch(); // works // correction: _usually_ works
$stmt_1->bind_params()->execute()->bind_results()->fetch(); // fails

$stmt_1->bind_params()->execute()->bind_results()->fetch()->close(); // works
$stmt_2->bind_params()->execute()->bind_results()->fetch()->close(); // works
$stmt_1->bind_params()->execute()->bind_results()->fetch()->close(); // works 


---

AFAICT, the ->more_results() loop should be unnecessary.

This query is before the one thats been throwing the prepare error.

PHP Code:

---

$stmt=$mysqli->prepare("SELECT * FROM sales_file WHERE `id`=?");
$stmt->bind_param('s',$file);
$stmt->execute();
$stmt->bind_result($fid,$fdate,$fco,$fuser,$fname,$fterritory,$finterest,$faddyst,$faddycity,$faddystate,$faddyzip,$fcurrent,$fcurrentdesc,$fcontact);
$stmt->fetch(); 


---

When i change this: $stmt->fetch(); TO: while($stmt->fetch()){}

The query mentioned above will work. I DO NOT UNDERSTAND WHY...but i read this article http://www.daniweb.com/web-developme...is-command-now

Can you help explain this to me?

Thanks for your help again.

this is highly oversimplified, and I don't completely understand all of it myself, but think of it like this: when you get a resultset, it's organized in rows (this is true even if there is only one result row).

Code:

---

resultset: {
>  result1: [col2, col2, col3]
  ,result2: [col1, col2, col3]
  ,etc.
}

---

and, there's this thing called a "pointer" (which most people who use higher-level languages - like PHP as opposed to, say, C/C++ - have never heard of) which we use constantly, usually without realizing it exists. I marked the pointer position in the resultset above with a >.

when you use certain methods (like fetch(), in this case), it returns the item the the pointer is pointing at (first time: first row), and then advances the pointer.

so, after one call to fetch(), the resultset looks like this:

Code:

---

resultset: {
    result1: [col2, col2, col3]
>  ,result2: [col1, col2, col3]
  ,etc.
}

---

if you call the method multiple times, the pointer keeps advancing. eventually (or quickly, if there's only one row), the pointer is at the end of the resultset:

Code:

---

resultset: {
    result1: [col2, col2, col3]
  ,result2: [col1, col2, col3]
  ,etc.
>
}

---

we're kinda in limbo, here. PHP doesn't really know that there's no more results until it tries to get them, so it won't tell MySQL to close the resultset. As a consequence, MySQL can't run some other query in the meantime. "commands out-of-sync."

what you're doing with the while() loop if running fetch() repeatedly. when fetch() gets to that last position, it will see there's no more rows, and return FALSE. the while() loop will end, and PHP will tell MySQL to close the resultset.

A drawback of this is that, if you ever have more than one row in your resultset, your while() code will run on all of them. This probably isn't expected or desired. Unless it's *really won't work* for some reason, I'd suggest using the close() method instead. if needed, the while( more_results() ) loop I suggested earlier will do the same thing as your loop, but won't run any code on the rows it passes over.

Thanks for your time in explaining this, i appreciate it.