Jump to content

cannot use prepared statement


jimfog
 Share

Recommended Posts

I am having difficulty implementing prepared statement...I am getting the following error:

 

Call to a member function bind_param() on a non-object...

 

The code is this:

$stmt=$connection->prepare('INSERT into appointments VALUES( ?, ?, ?, ?, ?, ?, ? )');        $stmt->bind_param('siisii',$name,$start,$end,$origin,$staf,$bookedfor);         $stmt->execute();         $stmt->close();

The above is enclosed in a function...for now I will not post the rest code of the function as I do not think it is necessary plus I would like to keep things simple.

 

The error message appears when bind_param is called.

So...what I did is var_dump $connection...the result is this:

object(mysqli)#1 (19) { ["affected_rows"]=> int(-1) ["client_info"]=> string(79) "mysqlnd 5.0.11-dev - 20120503 - $Id: 1514feb3700aa52d513182fcdc87f2c66f06d152 $" ["client_version"]=> int(50011) ["connect_errno"]=> int(0) ["connect_error"]=> NULL ["errno"]=> int(1136) ["error"]=> string(47) "Column count doesn't match value count at row 1" ["error_list"]=> array(0) { } ["field_count"]=> int(1) ["host_info"]=> string(20) "localhost via TCP/IP" ["info"]=> NULL ["insert_id"]=> int(0) ["server_info"]=> string(6) "5.6.15" ["server_version"]=> int(50615) ["stat"]=> string(134) "Uptime: 47970 Threads: 3 Questions: 955 Slow queries: 0 Opens: 77 Flush tables: 1 Open tables: 67 Queries per second avg: 0.019" ["sqlstate"]=> string(5) "00000" ["protocol_version"]=> int(10) ["thread_id"]=> int(128) ["warning_count"]=> int(0) }

meaning...$connection is indeed an object...which is then assigned to $stmt...

 

If my logic above is correct...then why am I getting this error?

Link to comment
Share on other sites

You're not running bind_param on $connection, you're running it on $stmt. They aren't the same, it would be more helpful to see what $stmt is. You can also check the manual. mysqli::prepare returns a mysqli_stmt object, or false if an error occurred:http://php.net/manual/en/mysqli.prepare.phpIf it's not returning an object, then it must be returning false, which means the prepare failed.

Link to comment
Share on other sites

well...I get FALSE.

 

So the prepare statement is not executed.

 

I cannot understand why.

Link to comment
Share on other sites

This probably is the problem:

Column count doesn't match value count at row 1

You have seven placeholders but, judging from the parameters you're passing to it, there are only six columns.

Link to comment
Share on other sites

I cannot understand why.

Hence the ability to be able to print the error message from MySQL. There's no reason to guess what the problem is, just print the error message from MySQL. You can do it with the mysqli extension just like you can do it with the old mysql extension.
Link to comment
Share on other sites

This probably is the problem:

You have seven placeholders but, judging from the parameters you're passing to it, there are only six columns.

I fixed that but the problem remains...

Here is the code:

        $stmt=$connection->prepare('INSERT into appointments VALUES( ?, ?, ?, ?, ?, ? )');        $stmt->bind_param('siiisi',$name,$bookedfor,$start,$end,$origin,$staf);        $stmt->execute();

Hence the ability to be able to print the error message from MySQL. There's no reason to guess what the problem is, just print the error message from MySQL. You can do it with the mysqli extension just like you can do it with the old mysql extension.

Look at the code to see what error printing code I used.

         $stmt=$connection->prepare('INSERT into appointments VALUES( ?, ?, ?, ?, ?, ? )');         printf("Error: %s.n", $stmt->error);//this here is at line 152        $stmt->bind_param('siiisi',$name,$bookedfor,$start,$end,$origin,$staf);        $stmt->execute();

here is what I got:

Notice: Trying to get property of non-object in C:Apache24htdocsAppointmentsAdministratoradmin_db_code.php on line 152Error: .

Again... $stmt is considered a non-object.

 

The prepare statement never gets executed...

I can understand that from the fact that var_dump(ing) $stmt gives false.

Link to comment
Share on other sites

So if $stmt is false, why are you trying to get an error message with $stmt->error? You know it's false, it's not an object. That's not where the error message is.

What's next then...any ideas?

Link to comment
Share on other sites

The error did not happen on the return value of the prepare statement, the error happened on the prepare statement. Check the $connection object.

it seems the problem is what is described in post 4.

A problem arises with prepared statements...at least for me which I have not used them before.

The prepare statements "require" that the number of values entered in the db matches the number of columns of the specific table.

If not....then the statement fails.

 

Yes...but I want to fill a specific number of columns of the table.

By not using prepare statements I could specifically set which columns will be chosen for entering data(in an INSERT statement).

 

How can I do that with prepared statements?

Link to comment
Share on other sites

I fixed it...thanks.

 

I just had to modify the INSERT statement accordingly.

Link to comment
Share on other sites

There is one last thing though that needs to be addressed for which I do not want to create a separate topic and that is error handling of a prepared statement.

 

When not using prepared statement error handling was going like this:

    $result = $connection->query('INSERT into appointments(Bookfrom,startDate,endDate,apps_origin,staffID,bookedfor) values("'.$name.'","'.$start.'","'.$end.'","'.$origin.'","'.$staf.'","'.$bookedfor.'")');          if (!$result){         printf("Errormessage for result insert into appointments query: %sn", $connection->error);         return false;          }

So the question is how am I going to handle errors when using prepared statements?

The PHP manual does not help a lot.

 

It just uses a conditional to test if the prepare statement return TRUE and then proceed with the rest of the statements(http://php.net/manual/en/mysqli.prepare.php).

I am not sure though this the optimal error handling.

Link to comment
Share on other sites

First you prepare the statement, if that did not work then you catch the error in the else statement.

 

After each execute() you can check the value of $stmt->errorno, if the value is 0 then don't do anything, if it's not zero, you can print the value of $stmt->error or store it into an error log file or whatever you usually do when you encounter an error.

Link to comment
Share on other sites

First you prepare the statement, if that did not work then you catch the error in the else statement.

 

After each execute() you can check the value of $stmt->errorno, if the value is 0 then don't do anything, if it's not zero, you can print the value of $stmt->error or store it into an error log file or whatever you usually do when you encounter an error.

Here is what you describe above in code...tell me please if this is what you mean.

  if ($stmt = $connection->prepare('INSERT into  appointments (Bookfrom,startDate,endDate,apps_origin,staffID,bookedfor) VALUES( ?, ?, ?, ?, ?, ? )'))        {         $stmt->bind_param('siisii',$name,$start,$end,$origin,$staf,$bookedfor);         $stmt->execute();         ($stmt->errno===0)?'':printf("Error: %s.n", $stmt->error);         $stmt->close();        }        else        {printf("Error: %s.n", $stmt->error);}
Link to comment
Share on other sites

In the else statement, since $stmt does not exist you look for $connection->error.

 

What you wrote for the statement should work. I don't like the use of the conditional operator for that kind of situation, it's better to use an if() to make the code more readable.

Link to comment
Share on other sites

In the else statement, since $stmt does not exist you look for $connection->error.

 

What you wrote for the statement should work. I don't like the use of the conditional operator for that kind of situation, it's better to use an if() to make the code more readable.

But why $stmt does not exist?

If the prepare statement fails it will have a value of FALSE.

 

 

You are saying it is better to use an if....but it is obvious I am using an if statement.

 

Unless you are referring to the ternary operator I am using to check the execute command

Link to comment
Share on other sites

If the query fails to be prepared $stmt is a boolean, not an object, so it can't have a property $stmt->error. That's why you look for $connection->error instead.

 

Yes, I was referring to the ternary operator. I call it the conditional operator because that's actually what it does, if another ternary operator is invented the word "ternary" would be ambiguous.

Link to comment
Share on other sites

I think this should be OK now...

  if ($stmt = $connection->prepare('INSERT into  appointments (Bookfrom,startDate,endDate,apps_origin,staffID,bookedfor) VALUES( ?, ?, ?, ?, ?, ? )'))        {         $stmt->bind_param('siisii',$name,$start,$end,$origin,$staf,$bookedfor);         $stmt->execute();        if($stmt->errno!==0)        {printf("Error: %s.n", $stmt->error);}         $stmt->close();        }        else        {printf("Error: %s.n", $connection->error);}
Edited by jimfog
Link to comment
Share on other sites

  • 2 weeks later...

anybody?

Link to comment
Share on other sites

Is it working or not?

It is working of course...

My question is if the code,the way it is right now,if there is a chance that some errors might missed?

 

I do not know what errors these might be...it is strictly a logic issue here.

Link to comment
Share on other sites

You fix errors to make sure that something works. If it's working and it's working as it was designed to then there are no errors in it.

 

There are a few different types of errors to deal with.

 

The most basic ones are lexical and syntactical errors, if these errors are present then PHP will tell you, so you can be sure that you don't have any of those.

 

The next level would be runtime errors. The program compiles right, but during runtime something that the program wasn't expecting happens. PHP tells you if it encountered a runtime error so if you haven't gotten any runtime error messages then you don't have any of those errors. PHP won't show runtime errors in sections of code that haven't been executed, so if you want to be completely sure that your program doesn't have runtime errors then make a test program that runs all of your functions.

 

Ontop of that are logical errors. That means that the program works fine but it is doing something different than you intended it to. Sometimes these errors are obvious, sometimes you don't find out about them for a long time. In order to find all of the logical errors you need to test all possible cases for your program.

 

In your most recent block of code you have three cases to test:

1. The data gets inserted into the database and is exactly the same as the data you sent to it.

2. The block of code correctly identifies an error when a prepared statement could not be created.

3. The block of code correctly identifies an error when the statement failed to execute.

 

Here's how you could test them:

1. Run the program, then check the database (with phpMyAdmin, for example) and look for rows with the information you sent to it.

2. Deliberately make the prepared statement fail by passing a bad value instead of a proper SQL string.

3. Deliberately make the statement execution fail by, for example, passing the wrong amount of parameters.

Link to comment
Share on other sites

I checked everything you mentioned above....and it is OK.

 

There is one small detail though.

The prepare statement you see is located inside a function.

 

If an error is produced should I use RETURN to exit the function?

Link to comment
Share on other sites

I would also suggest, since often beginners ask these kind of questions, is to start getting into unit testing. It is a good way to discipline yourself because it often exposes to developers how coupled and complicated their code is (because writing tests initially always seems very daunting and overwhelming). As you progress and start adhering to good OOP design principles, and start creating classes with interfaces that follow the Single Responsibility Principle, you will find that unit testing becomes very easy, which leads to greater confidence in your code. As Ingolme pointed out, when you do encounter logic errors (like an oversight or an edge case in your business logic) you can create a test for that and know that you won't add any regressions to the code that you currently have.

Link to comment
Share on other sites

If the rest of the code that's in the function depends on the result of the prepared statement then you should return. If it is not dependent then it's better not to leave the function so that the program can run as normally as possible.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...