Mysqli prepared stmt - using object multiple times?

[crobinson42]

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:

$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:

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!

[traq]

// 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

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.

[crobinson42]

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:

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

[traq]

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

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.

[crobinson42]

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

THANKS!

[crobinson42]

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

[traq]

could you post your current query?

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

$stmt->close(); 

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

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.

[crobinson42]

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

[traq]

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:

//  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:

/*  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.

[crobinson42]

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

$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.