jimfog Posted December 18, 2014 Share Posted December 18, 2014 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 More sharing options...
justsomeguy Posted December 18, 2014 Share Posted December 18, 2014 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 More sharing options...
jimfog Posted December 18, 2014 Author Share Posted December 18, 2014 well...I get FALSE. So the prepare statement is not executed. I cannot understand why. Link to comment Share on other sites More sharing options...
Ingolme Posted December 18, 2014 Share Posted December 18, 2014 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 More sharing options...
justsomeguy Posted December 18, 2014 Share Posted December 18, 2014 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 More sharing options...
jimfog Posted December 19, 2014 Author Share Posted December 19, 2014 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 More sharing options...
justsomeguy Posted December 19, 2014 Share Posted December 19, 2014 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. Link to comment Share on other sites More sharing options...
jimfog Posted December 19, 2014 Author Share Posted December 19, 2014 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 More sharing options...
justsomeguy Posted December 19, 2014 Share Posted December 19, 2014 The error did not happen on the return value of the prepare statement, the error happened on the prepare statement. Check the $connection object. Link to comment Share on other sites More sharing options...
jimfog Posted December 22, 2014 Author Share Posted December 22, 2014 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 More sharing options...
jimfog Posted December 22, 2014 Author Share Posted December 22, 2014 I fixed it...thanks. I just had to modify the INSERT statement accordingly. Link to comment Share on other sites More sharing options...
jimfog Posted December 23, 2014 Author Share Posted December 23, 2014 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 More sharing options...
Ingolme Posted December 23, 2014 Share Posted December 23, 2014 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 More sharing options...
jimfog Posted December 24, 2014 Author Share Posted December 24, 2014 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 More sharing options...
Ingolme Posted December 24, 2014 Share Posted December 24, 2014 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 More sharing options...
jimfog Posted December 24, 2014 Author Share Posted December 24, 2014 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 More sharing options...
Ingolme Posted December 24, 2014 Share Posted December 24, 2014 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 More sharing options...
jimfog Posted December 25, 2014 Author Share Posted December 25, 2014 (edited) 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 December 25, 2014 by jimfog Link to comment Share on other sites More sharing options...
jimfog Posted January 7, 2015 Author Share Posted January 7, 2015 anybody? Link to comment Share on other sites More sharing options...
Ingolme Posted January 7, 2015 Share Posted January 7, 2015 Is it working or not? Link to comment Share on other sites More sharing options...
jimfog Posted January 7, 2015 Author Share Posted January 7, 2015 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 More sharing options...
Ingolme Posted January 7, 2015 Share Posted January 7, 2015 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 More sharing options...
jimfog Posted January 8, 2015 Author Share Posted January 8, 2015 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 More sharing options...
thescientist Posted January 8, 2015 Share Posted January 8, 2015 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 More sharing options...
Ingolme Posted January 8, 2015 Share Posted January 8, 2015 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now