problem writing to database - invalid object or resource mysqli_stmt

[mapper_guy2007]

I'm having great difficulty in making PHP/MySQL code insert into a database table, a prepared statement with bound parameters. I keep getting the above error message. Following is the code I'm using, with the results of my debugging checks shown as "// result: ....."

<?php	// prepared statements: bound parameters - input	// specify connection inforequire_once('db_connect.php');	// connect to the server$link = mysqli_connect($db_host,$username,$password) or die("Problem connecting: ".mysqli_error($link));	// select a databasemysql_select_db($database) or die ("Could not select database because " . mysqli_error());	// check connection openif (!$link) {   printf("Connect failed: %s\n<br />", mysqli_connect_error());   exit();}else{   printf("open connection - host information: %s\n<br />", mysqli_get_host_info($link));}[i]// result: 'open connection - host information: localhost via TCP/IP '[/i]	// create the SQL statement, with ? to reflect the parameters to be supplied later.  $entry = 'INSERT INTO table VALUES(?, ?)';	//  allocates and initializes a statement object suitable for mysqli_stmt_prepare().$db_init= mysqli_stmt_init($link);echo "\$db_init = ";print_r($db_init).;echo "<br />";[i]// result: '[b]$db_init = mysqli_stmt Object ( )[/b] '[/i]	// prepare statement$prep_stmt = mysqli_stmt_prepare($db_init, $entry); echo "prepared stmt = ";print_r($prep_stmt);echo "<br />";[i]// result: '[b]prepared stmt = [/b]' [/i]	//  bind parameters -  'ss' tells MySQL to expect two strings (ss)mysqli_stmt_bind_param($prep_stmt, 'ss', $field1, $field2);[i]// result: '[b]Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given [/b]'[/i]  	// set parameters and do first execution  $field1 = 'something';  $field2 = 'something else';  mysqli_stmt_execute($stmt);[i]// result: 'Warning: mysqli_stmt_execute(): invalid object or resource mysqli_stmt'[/i]  	 // set parameters and do second execution  $field1 = 'another';  $field2 = 'another another';  mysqli_stmt_execute($stmt);[i]// result: 'Warning: mysqli_stmt_execute(): invalid object or resource mysqli_stmt'[/i]?>

"$link" appears to be OK as it creates an open connection through localhost; the problem appears to be caused either by the "$stmt = mysqli_stmt_init($link);" line of code, which does not seem to return a valid object or resource mysqli_stmt despite the result returned ('prepared stmt = mysqli_stmt Object ( )' ), or by the 'mysqli_stmt_prepare($stmt, $entry);' line. The other warnings result from this failure, I think. I've tried, without success, the suggestions of justsomeguy (July 7, 2007), including changing to mysqli_prepare(), which doesn't work either, it just throws a different error (mysqli_prepare() expects parameter 1 to be mysqli, object given)

$db_init = mysqli_stmt_init($conn); echo "\$db_init  = ";print_r($stmt);echo "<br />";[i]// result: '$db_init = mysqli_stmt Object ( )' [/i]$stmt = mysqli_prepare($db_init, $query_stmt);[i]// result: '[b]Warning: mysqli_prepare() expects parameter 1 to be mysqli, object[/b]'[/i] echo "prepared stmt = ";print_r($stmt);echo "<br />";// result: 'prepared stmt = ' mysqli_stmt_bind_param($stmt, 'd', $trimmed_dwf['0']);// result: 'Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, null given'  mysqli_stmt_execute($stmt);mysqli_stmt_close($stmt);

I've followed his suggestions before and they worked fine, but now something is off. I haven't changed the WAMP setup (using Web Developer Controller v0.999 beta) which seemed to be working before - is there anything in the config files that I should check?Does anyone have any ideas as to the correct method, or any thoughts about what else could be wrong,? and it's probably something very simple and stupid that I doing! many thanks in anticipation of your advice or pointers, while I still have some hair left.

[justsomeguy]

I think this will work, I've stripped out the comments and debugging stuff. With the first block of code, the mysqli_stmt_prepare function returns a boolean value, not a statement object. That's where that error is coming from. I think the second block is the better one to use though, I've added the connection stuff to the top of it. The error there was that you were sending mysqli_prepare the result from mysqli_stmt_init, you should be sending it the result from mysqli_connect instead.

require_once('db_connect.php');$link = mysqli_connect($db_host,$username,$password) or die("Problem connecting: ".mysqli_error($link));mysql_select_db($database) or die ("Could not select database because " . mysqli_error());if (!$link) {   printf("Connect failed: %s\n<br />", mysqli_connect_error());   exit();}else{   printf("open connection - host information: %s\n<br />", mysqli_get_host_info($link));}$entry = 'INSERT INTO table VALUES(?, ?)';$stmt = mysqli_prepare($link, $entry);mysqli_stmt_bind_param($stmt, 'ss', $field1, $field2);$field1 = 'something';$field2 = 'something else';mysqli_stmt_execute($stmt);mysqli_stmt_close($stmt);

[mapper_guy2007]

Thanks for your reply, Pirate Ninja, what you say makes perfect sense to me. But when I simply copied the code you suggested, and ran it unchanged. I got basically the same error: "Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given". I've even tried this on a different machine, with a different system (WAMP) in case it was a setup difficulty, and get the same message.I want to parametise the statement to improve security, but am now completely at a loss!

[justsomeguy]

Yeah, parameterization is a good idea and worth getting to work.Hmm, so it's still sending a boolean to the function. That's a little strange, obviously the mysqli_prepare function returns a mysqli_stmt value:mysqli_stmt mysqli_prepare ( mysqli $link , string $query )The only other thing I see is this:

Return Valuesmysqli_prepare() returns a statement object or FALSE if an error occured.

So if it returned a boolean then it had to return FALSE, so some error occurred. I'm not sure what the error would be. To verify that it's false you can use var_dump on it:$entry = 'INSERT INTO table VALUES(?, ?)';$stmt = mysqli_prepare($link, $entry);var_dump($stmt);You should be able to use the mysqli_error function to get the last error message, hopefully that will shed some details.echo mysqli_error($link);

[mapper_guy2007]

Thanks for your quick reply!"echo mysqli_error($link);" after "$link = mysqli_connect(etc)" returns nothing,"mysqli_get_host_info($link)" after "mysql_select_db(etc)" returns"host information: localhost via TCP/IP""var_dump" after '$stmnt = mysqli_query(etc)' returns "bool(false) " - no surprise there, I guess!I've used the same $link to query the database with a select statement, and it works OK:

require_once('db_connect.php'); $link = mysqli_connect($db_host,$username,$password);mysql_select_db($database) or die ("Could not select database because " . mysqli_error());$check = "SELECT * FROM table WHERE name = '$name'"; $qry = mysql_query($check) or die ("Could not match data because ".mysql_error());$num_rows = mysql_num_rows($qry); 

This returns one row (name is a unique column) if $name is present in table.Hope this helps point to something....I tried OO form, and got this error message at the same locations as above:"var_dump" after "$stmt = $mysqli->prepare(etc)" returns "bool(false) " (as before), and in addition the line "$stmt->bind_param((etc)" returns "Fatal error: Call to a member function bind_param() on a non-object " which I imagine is the effect of $stmt being (False)

require_once('db_connect.php');$mysqli = new mysqli($db_host,$username,$password,$database);    // no errors here$stmt = $mysqli->prepare("INSERT INTO table VALUES(?,?)");var_dump($stmt);          // returns "bool(false)"printf("Stmt Error: %s.\n", $stmt->error);          // returns "Error: "$stmt->bind_param('s', $field1, field2);printf("Error: %s.\n", $stmt->error);$stmt->execute();printf("%d Row inserted.\n", $stmt->affected_rows);$stmt->close(); $mysqli->close();

So I have no ideas now; is there anything in phpinfo that I should look for, an inactivated dll or ???? (php_mysqli.dll shows as a loaded PHP extension in PHP Version 5.2.1)Thanks for taking the time to try to sort this out! It's much appreciated.

[justsomeguy]

Use the mysqli_error function after the mysqli_prepare function, that's the one that's causing the error.

[mapper_guy2007]

Pounds head with keyboard!!!! stmt error = "No database selected"

require_once('db_connect.php');  // contains strings for  db_host, a username, a password, a database name$link = mysqli_connect($db_host,$username,$password) or die("Problem connecting: ".mysqli_error($link));// $link connects according to mysqli_get_host_info($link))mysql_select_db($database) or die ("Could not select database because " . mysqli_error());  // no error is thrown against this! but stmt error at this point is "no database selected"

Pounds head with keyboard again!!!! Realises that database should be specified in $link, or by using mysqli_select_db($link, $database); not mysql_select_db($database)and changes code to:

// this alternativerequire_once('db_connect.php');  // contains strings for  db_host, a username, a password, a database name$link = mysqli_connect($db_host, $username, $password, $database ) or die("Problem connecting: ".mysqli_error($link));// or thisrequire_once('db_connect.php');  $link = mysqli_connect($db_host,$username,$password) or die("Problem connecting: ".mysqli_error($link));mysqli_select_db(&link, $database) or die ("Could not select database because " . mysqli_error());  

Result from either method: One line inserted into database table. Thank you, Pirate Ninja, problem resolved at this point! :)I'll run the full page later tonight, and let you know how it works out. Out of curiosity, in your opinion, is one method (specifying db in $link, or with mysqli_select_db) better than another?Many heartfelt thanks for your great advice, and for re-teaching me the simple art of logically and systematically following the error back to its creation- something that's too easy to forget in the grip of frustration.

[justsomeguy]

Glad you got it working. Either method is fine, I typically use select_db on it's own like the second way, but that's only because I've been using the mysql extension mostly as opposed to mysqli. Most of the hosted servers I work on haven't upgraded to PHP5 yet.