[T'Shaunik]

I have a query in a function (the function works to this point) that returns the error:
PHP Fatal error: Call to a member function bind_param() on a non-object in functions.php on line 339.

I have tried removing variables and hard-coding info in, but the cause of the problem continues to elude me.

This is inside a while loop, looping through multiple results from one table to find out if it exists in another to determine what it should be doing next.

$acct = 0; /*made sure the variable is clear so it IS getting the result from the database*/
$stmt = "SELECT accountNum FROM mainTable WHERE accountNum = $accountNum AND publisher = $publisher";/*this is for verifying the variables being put into the query*/
if(!$existingCheck = $existingMysqli->prepare("SELECT accountNum FROM mainTable WHERE accountNum = ? AND publisher = ?"))
{
     echo "Prepare failed for existing check: (" . $existingMysqli->errno . ") " . $existingMysqli->error ."<BR>";
}else
{
     $existingSTMT->bind_param("is", $accountNum, $publisher);
     $existingSTMT->bind_result($acct);
     $existingSTMT->execute();
     $existingSTMT->fetch();
     $results = $existingSTMT->num_rows;
     echo "$stmt --- Num of Results: $results, account num: $acct<BR>";
}

What it outputs is:
SELECT accountNum FROM mainTable WHERE accountNum = 123 AND publisher = XYZ --- Num of Results: 0 acct: 123
Prepare failed existing check: (0)

So it is connecting and getting the correct account number from the main table since that variable was cleared out prior, yet it keeps telling me that the query is bad. It does keep looping through and grabbing all the accounts that it should be doing, but this stops it from continuing what it needs to do.

Any ideas on where I could have gone wrong?
Thanks in advance for the help!

[Atli]

You are using the wrong variable names. You define $existingCheck as your statement, but then try to use $existingSTMT to bind and execute on. That's why you're getting that error, because $existingSTMT is, in fact, not an object.

[T'Shaunik]

Thanks for the reply, Atli. I just looked at my code and it does have the correct variable there. *facepalm* I typed the wrong thing there when I was cleaning up the commented code for posting. It should have read:

$acct = 0; /*made sure the variable is clear so it IS getting the result from the database*/
$stmt = "SELECT accountNum FROM mainTable WHERE accountNum = $accountNum AND publisher = $publisher";/*this is  verifying the variables being put into the query*/
if(!$existingSTMT = $existingMysqli->prepare("SELECT accountNum FROM mainTable WHERE accountNum = ? AND publisher = ?"))
{
     echo "Prepare failed for existing check: (" . $existingMysqli->errno . ") " . $existingMysqli->error ."<BR>";
}else
{
     $existingSTMT->bind_param("is", $accountNum, $publisher);
     $existingSTMT->bind_result($acct);
     $existingSTMT->execute();
     $existingSTMT->fetch();
     $results = $existingSTMT->num_rows;
     echo "$stmt --- Num of Results: $results, account num: $acct<BR>";
}

[Atli]

You 100% sure your names match? The code you posted should not be showing that error. I even tried it over here just to be sure, and it works fine.

[T'Shaunik]

Atli, on 14 May 2013 - 01:01 AM, said:

You 100% sure your names match? The code you posted should not be showing that error. I even tried it over here just to be sure, and it works fine.

I use Notepad++ and when you double click a word, it highlights all matches in the document and those variables all match. That's what I can't figure out about this - it shouldn't be showing that error, especially when it's spitting out the right account number.

All I can think of after spending this much time trying to figure this out is that there is some bug with MySQL because it gives a result at the same time that it gives an error.

[Atli]

No, this isn't a MySQL problem. Those errors are happening in the PHP code, not the MySQL code. All MySQLi does is either prepare the query successfully and return a statement object, or fail to prepare it and return FALSE.

If you add var_dump($existingSTMT); just above the bind_param call, what does it print out?

[T'Shaunik]

Atli, on 14 May 2013 - 02:49 AM, said:

No, this isn't a MySQL problem. Those errors are happening in the PHP code, not the MySQL code. All MySQLi does is either prepare the query successfully and return a statement object, or fail to prepare it and return FALSE.

If you add var_dump($existingSTMT); just above the bind_param call, what does it print out?

It prints out:
object(mysqli_stmt)#13 (10) { ["affected_rows"]=> int(0) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(2) ["field_count"]=> int(1) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(1) }

I speculated about it being with MySQL because I cleared out the variable and the query is returning the correct account number in that variable, yet it gives an error trying to bind_param to the query and yet doesn't spit out an error on that query. Two things happen like they would if it is working, yet another happens like it's not working.

Many thanks for working through this with me.

[JackOfAllTrades]

From the notes on the PDO::execute() man page:

Quote

Some drivers require to close cursor before executing next statement.

Could that maybe be the issue?

On another note, could your multiple queries perhaps be solved via a JOIN?

[Atli]

He's using MySQLi, JackOfAllTrades, not PDO.

I'm with you on the JOIN though. Although, I'm not sure an actual join is required. If the publisher value is static, something like this would return all the existing account numbers:

SELECT accountNum 
FROM mainTable 
WHERE 
    publisher = ? AND
    accountNum IN(1, 2, 3, 4, 5)

The IN() clause would just have to be constructed by PHP and injected into the SQL, but that's not complicated to do.

T, on 14 May 2013 - 08:07 PM, said:

It prints out:

Does it do that on every iteration? You mentioned this was being executed in a loop, so clearly some iterations are executing correctly, but something about some of them are causing this issue. - What you posted is clearly marked as object(mysqli_stmt) so a 'call on a non-object' error on that doesn't make any sense.


Just to rule out some weird syntax bug that I can't spot, try re-organizing the code a bit. Something like this:

$sql = "SELECT accountNum FROM mainTable WHERE accountNum = ? AND publisher = ?";
$existingSTMT = $existingMysqli->prepare($sql);
if ($existingSTMT) {
	$existingSTMT->bind_param("is", $accountNum, $publisher);
	$existingSTMT->bind_result($acct);
	$existingSTMT->execute();
	$existingSTMT->fetch();
	$results = $existingSTMT->num_rows;
	echo "$sql ($accountNum, $publisher) --- Num of Results: $results, account num: $acct<BR>";
}
else {
	echo "Prepare failed for existing check: (" . $existingMysqli->errno . ") " . $existingMysqli->error ."<BR>";
}

I can't see why it would change anything, but on the off chance that your version of PHP is doing something odd, this should definitely work. The way you have the assignment of the statement and a negate operator (!) all crammed into the IF condition never felt right to me.

Another thing to note there is the order of the IF blocks. It somehow always makes more sense to test for success and default to failure than to test for failure and default to success.

[T'Shaunik]

JackOfAllTrades, on 14 May 2013 - 09:58 PM, said:

From the notes on the PDO::execute() man page:

Quote

Some drivers require to close cursor before executing next statement.

Could that maybe be the issue?

On another note, could your multiple queries perhaps be solved via a JOIN?

I have the query using a new connection so as to eliminate any chance that a previous query is causing a problem with that connection.

As for using a JOIN, I am not sure if I am able to use that the way it's needed for this. Some of the tables in the database have unique names based on the user that needs to access them, but not all users can have unique ones (some are combined into one for multiple users), so I have to build it based on how it's getting the information required (I am integrating this with ancient software so I need to separate certain information so as to prevent any information from falling between the cracks). The outermost loop grabs the table names and finds who it needs to run this for. To my knowledge, I haven't heard of a way to do a JOIN between two tables when one of those table names needs to be grabbed from the other one so it can do the querying.

Atli, on 14 May 2013 - 10:47 PM, said:

Does it do that on every iteration? You mentioned this was being executed in a loop, so clearly some iterations are executing correctly, but something about some of them are causing this issue. - What you posted is clearly marked as object(mysqli_stmt) so a 'call on a non-object' error on that doesn't make any sense.

It prints that out each time, not once correctly executing and getting the information it needs. Here's a few of the lines:
object(mysqli_stmt)#13 (10) { ["affected_rows"]=> int(0) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(2) ["field_count"]=> int(1) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(1) }
Prepare failed existing check: (0)
object(mysqli_stmt)#13 (10) { ["affected_rows"]=> int(0) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(2) ["field_count"]=> int(1) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(2) }
Prepare failed existing check: (0)
object(mysqli_stmt)#14 (10) { ["affected_rows"]=> int(0) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(2) ["field_count"]=> int(1) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(3) }
Prepare failed existing check: (0)
object(mysqli_stmt)#14 (10) { ["affected_rows"]=> int(0) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(2) ["field_count"]=> int(1) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(4) }
Prepare failed existing check: (0)
object(mysqli_stmt)#15 (10) { ["affected_rows"]=> int(0) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(2) ["field_count"]=> int(1) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(5) }
Prepare failed existing check: (0)

Atli, on 14 May 2013 - 10:47 PM, said:

Just to rule out some weird syntax bug that I can't spot, try re-organizing the code a bit. Something like this:

$sql = "SELECT accountNum FROM mainTable WHERE accountNum = ? AND publisher = ?";
$existingSTMT = $existingMysqli->prepare($sql);
if ($existingSTMT) {
	$existingSTMT->bind_param("is", $accountNum, $publisher);
	$existingSTMT->bind_result($acct);
	$existingSTMT->execute();
	$existingSTMT->fetch();
	$results = $existingSTMT->num_rows;
	echo "$sql ($accountNum, $publisher) --- Num of Results: $results, account num: $acct<BR>";
}
else {
	echo "Prepare failed for existing check: (" . $existingMysqli->errno . ") " . $existingMysqli->error ."<BR>";
}

I can't see why it would change anything, but on the off chance that your version of PHP is doing something odd, this should definitely work. The way you have the assignment of the statement and a negate operator (!) all crammed into the IF condition never felt right to me.

Another thing to note there is the order of the IF blocks. It somehow always makes more sense to test for success and default to failure than to test for failure and default to success.

I changed the code to what you suggested and now it prints out for each query:
Prepare failed for existing check: (1065) Query was empty

I also had it print out the accountNum and publisher that it was using in the query each time and those match up with what it should be using for the queries.

[JackOfAllTrades]

My bad on PDO vs. mysqli. So used to seeing PDO.

[T'Shaunik]

JackOfAllTrades, on 15 May 2013 - 10:51 AM, said:

My bad on PDO vs. mysqli. So used to seeing PDO.

Hey no problem, Jack. We all miss something sometimes - part of being human. Thanks for trying to help me out!

[T'Shaunik]

This query doesn't work, but before it, the others do, so maybe I can get rid of the troublesome query. I'm not familiar with using conditionals in a query, which is what I'm guessing this would need.

Is there a way to have the query check to see if an account of that publisher matches in the mainTable and if so, update a couple fields, and if it doesn't, insert all the fields from the table it's taking the account numbers from?

[andrewsw]

T, on 15 May 2013 - 08:32 PM, said:

Is there a way to have the query check to see if an account of that publisher matches in the mainTable and if so, update a couple fields, and if it doesn't, insert all the fields from the table it's taking the account numbers from?

This sounds like you could investigate INSERT.. ON DUPLICATE KEY UPDATE, but I'm not sure of your exact requirement. Basically, if there are existing records, this would update some fields, otherwise it will create new rows.

[T'Shaunik]

andrewsw, on 15 May 2013 - 09:09 PM, said:

This sounds like you could investigate INSERT.. ON DUPLICATE KEY UPDATE, but I'm not sure of your exact requirement. Basically, if there are existing records, this would update some fields, otherwise it will create new rows.

Yeah that sounds like exactly what I would need. It needs to check to see if there is a record where accountNum = x AND publisher = y.
If there is a record with those two fields matching, then it just needs to update two or three fields from the other table that it's grabbing the accountNum and publisher from.
If there isn't a match with those two fields, then it would need to insert all the fields from the other table.

Right now, I am grabbing the accounts and publishers from one table, then looping to update the main table.
With the INSERT/DUPLICATE UPDATE, it looks like I could have it do *INSERT/UPDATE* WHERE t1.updated = 'N' and cut out the loop completely.

As a side question, in that INSERT/UPDATE query where it looks for t1.updated = 'N', it shouldn't be a problem to then have it also update t1.updated = 'Y' at the same time, would it?