If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

Prepared statements and instantiation

I have a working database accessed through php and mysqli. To increase security I am in the process of replacing legacy queries with prepared statements. I am following a number of tutorials and forum entries and I get the proper results following the examples rote, but when I try to adapt the code to my database the stream of errors don't seem to end -- so obviously, I am doing something very wrong and I think it is improper instantiation.

I have highlighted the point of the first error in red -- I have tried substituting $db for $mysqli and I get an undefined variable error. ...also tried instantiating $mysqli and a double instantiation error occurs - I expect that it is illegal to connect to the same database twice.

It is probably obvious that this is my first effort at prepared statements -- I would greatly appreciate an explanation of a fix in addition to the code. Thanks for your help.

The point of having a database class/wrapper is that it contains all the lower-level database logic and your application (main code) only forms the queries and validates/supplies the data being put into the queries and calls the database class methods to actually perform the database operations.

You db class does have a ->query() method, but it is foo-bar. You should not be making a database connection every time you run a query. You should be making a database connection once when you create an instance of the db class.

Get your existing db class organized and working with a normal (non-prepared) query, then add a prepared query method to it with the logic necessary to prepare, dynamically bind any input data, run the query, check for errors, dynamically bind any result,

If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

CFMaBiSmAd -> I see your point on reconnecting to the database with each query, and I will definitely fix that.

But what about $mysqli->prepare()? That is a built-in function that should be accessible without re-creating it in my db class. Again, I think that problem lies with instantiation - These prepared statements work fine outside my script - but do not work when I tried to integrate them, so I am doing something wrong... Any thoughts?

The $db variable is an instance of your class. It's not an instance of the msyqli class. When you call methods of your class, they must exist in your class.

An alternative would be for your class to extend the mysqli class. In this case, if you called the ->prepare() method, it would be calling the base mysqli ->prepare() method.

However, I suspect your intention is to reduce the amount of code you have in your main application code to run each prepared query by having a class method that performs all the repetitive steps needed when you prepare/bind inputs/execute/bind the result/and fetch the data?

You kind of need to define what you are trying to accomplish with your db class before you go any further.

As to your existing class definition, it's not using the call-time connection details and it has three different places where it can make a database connection. It needs definition and cleanup before you try to add anything else to it.

If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

CFMaBiSmAd -> I tested my db class. The connection occurs only one time and multiple queries do not result in multiple connects. So it appears to be working as intended. You previously mentioned defining my intentions; I want to replace my legacy (insecure) queries with prepared statements to increase data security over the net. As written, everything works as designed and no known bugs exist.

So, can anyone explain what I am doing wrong with prepared statements? It appears to me that the db instantiation conflicts with mysqli instantiation. Both require a connection to the database which is throwing the fatal errors. How do I incorporate an instantiation of mysqli without breaking the code?

I have discovered that multiple instantiations are not the problem. I have instantiated both $db and $mysqli and they work fine together.

So I am working my way through prepared statements and have a partial solution, but also a snag. The following function is a means of using prepared statements without having to declare the bound parameters in advance - in essence, it provides the flexibility of choosing fetched fields dynamically.

I have successfully pulled the fields names, but the values are not being fetched. The function works as expected until the while ($stmt->fetch()) conditional statement, which is not looping. This failure may be related to the previous line; the call_user_func_array statement (both statements highlighted in red). Although I have read the manual on the callback statement, I don't have a good understanding of its function or how to test it, and I think that is the place to start.

For reference, this function replaces code at and below the $mySQL instantiation posted above, although I think this post can be evaluated on its own.

The code you posted isn't working because your ->execute() method failed because you are not binding any input parameter for the ? placeholder in the query. If you were checking for errors from the ->execute() method you would be getting - No data supplied for parameters in prepared statement

If you are going to write an advanced database function/class, you must have error checking logic in your code so that you don't attempt to use data that doesn't exist.

The ->prepare() method can fail due to sql syntax errors, the bind_parm code can fail with an error, the ->execute() method can fail with an error, and the bind_result code can fail with an error. You must check at each step if your code worked before trying to use the result from that step.

Last edited by CFMaBiSmAd; 05-27-2013 at 01:09 AM.

If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

Thanks to CFMaBiSmAd for the suggestion on error checking. After adding that coding I discovered there was an error in my query that chunked up the debugging further down. Good call, I will remember that step in the future!

Goal: To build an associative array in the format: field_name => value.

I have made significant headway; I can display field names and values but now I have a head-scratcher... Array $parameters[] serves double duty: It first collects the field names in the first loop but they are overwritten in the second loop. I need to save both field names and values to build the associative array, but every attempt to do so breaks the second loop.

Notes: $parameters[] initially contains the field names but those are lost during the 2nd loop when $parameters[] elements are filled with values. I tried assigning $fields to $columnNames[] but that broke the 2nd loop and I don't know why... Then I tried assigning $parameters[] to $columnNames[] and that also broke the 2nd loop. I also tried using different array names to separate field names from values, but every attempt breaks the 2nd loop. If that isn't enough, if I comment out the first loop, the 2nd loop breaks and I don't understand that dependency either.

So here's my latest code. As always, please explain the logic along with any code offerings. Thanks!

A more specific question: In the short snippet below, $columnNames[] contains field names in the top while loop prior to the bind statement, and then contains values after the bind statement. How do I place the field names in a separate array prior to the bind? Every effort to do so has broken the foreach loop.