Prepared statement difficulty

dowake · September 6, 2021, 3:09am

I have 18 fields in my MySql db. I have a query that works great until I add a new field to upload. I receive the error message: PHP Fatal error: Uncaught Error: Call to a member function execute() on bool in /home/…
I have removed the field and it works again. I have removed a different field and it works fine with the new field. Is there a length error or a field count error that would be the cause?
Here is the snippet where the error is generated, in the execute statement.

 if(isset($_POST['productname'])){
        $product = htmlentities($_POST['productname']); 
        $imgproductname = strtolower(str_replace(" ", "_", (htmlentities($_POST['productname']))));    
        $origin  = htmlentities($_POST['origin']);
        $l1perlb = htmlentities($_POST['l1perlb']);
        $l2perlb = htmlentities($_POST['l2perlb']);
        $l3perlb = htmlentities($_POST['l3perlb']);
        $l1perkg = htmlentities($_POST['l1perkg']);
        $l2perkg = htmlentities($_POST['l2perkg']);
        $l3perkg = htmlentities($_POST['l3perkg']);
        $costlb  = htmlentities($_POST['costlb']);
        $costkg  = htmlentities($_POST['costkg']);
        $quantity= htmlentities($_POST['quantity']);
        $source  = htmlentities($_POST['source']);
        $comment = htmlentities($_POST['comment']);
        $preview = htmlentities($_POST['preview']);
        $image   = '"https://mywebsite.com/images/' . $imgproductname . '.' . $ext . '"';
        $image   = '<a href=' . $image . '>View Pic</a>';
        $ip      = $_SERVER['REMOTE_ADDR'];


            $sql ='INSERT INTO product(productname, origin, l1perlb, l2perlb, l3perlb, l1perkg, l2perkg, l3perkg, costlb, costkg, quantity, source, comment, image, ip) VALUES(:productname, :origin, :l1perlb, :l2perlb, :l3perlb, :l1perkg, :l2perkg, :l3perkg, :costlb, :costkg, :source, :comment, :preview, :image, :ip)';  

            $stmt = $pdo->prepare($sql);

            $stmt->execute(['productname'=>$product, 'origin'=>$origin, 'l1perlb'=>$l1perlb, 'l2perlb'=>$l2perlb, 'l3perlb'=>$l3perlb, 'l1perkg'=>$l1perkg, 'l2perkg'=>$l2perkg, 'l3perkg'=>$l3perkg, 'costlb'=>$costlb, 'costkg'=>$costkg, 'source'=>$source,  'comment'=>$comment, 'preview'=>$preview, 'image'=>$image, 'ip'=>$ip]);

The error arrived when I added preview=>$preview in the execute statement.
The query is fine when I remove this field.

ErnieAlex · September 6, 2021, 4:20am

In the second line you are missing the underscore. $POST is not the same as $_POST.
$POST is just a variable. $_POST is an array of posted inputs.

Next, you set $image twice. That does not make sense. Why set it a second time. Either remove the first or second assignment of $image.

Lastly, in the execute, you set the “productname” to $product. Is that what you want?

Hope that helps get it working. Good luck.

phdr · September 6, 2021, 6:39pm

The current php error is a follow-on error because the prepare() call failed and the code continued to run past that point, but you don’t know why it failed because there is no error handling present. You always need error handling for statements that can fail. For database statements, the easiest way of adding error handling for the connection, query, prepare, and execute, is to use exceptions for errors and in most cases let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will get displayed/logged the same as php errors.) The exception to this rule are for user recoverable errors, such as when inserting/updating duplicate or out of range data. In this case, which may apply to what you are currently trying to do, your code would catch the exception, test if the error number is for something the user can fix, then setup and display a message telling the user what was wrong with the data that they submitted. For all other error numbers, just re-throw the exception and let php handle it.

You would set the pdo error mode to exceptions when you make the database connection. The pdo connection already always uses an exception for a connection error. Making this change will cause the query, prepare, and execute statements to use exceptions for errors too.

Next, here’s a list of things that will clean up and simplify the posted code -

htmlentities is an OUTPUT function. It is used when you output data in a html context. Do NOT use it on data you are storing in a database table, because it modifies the data values.
Do NOT create variables for nothing. This is just a waste of typing time. Keep the input data as an array variable, then operate on elements in this array throughout the rest of the code. This will also support dynamically validating and processing the data (todo as a more advanced programming activity.)
The form processing code should trim, then validate all the inputs, storing validation error messages in an array, using the field name as the array index. After the end of the validation logic, if the errors array is empty, use the submitted data. Test/display the content of the errors array at the appropriate point in the html document when you re-display the form.
Don’t store application specific domain, path, or html markup in the database. For the image, just store the filename in the database. Any of the other information should be applied when you output the content on a web page.
If you use positional prepared-query place-holders ?, you can eliminate a lot of the typing and clutter, which may be contributing to the current error. You would just supply an array of the values, corresponding to the column list, to the execute call.