MySQLi Bulk Prepared statements on large site?
I have been digging around stack and all sorts of other sites looking for the best answer to my questions.
I am developing a very large and growing monster of a website, in the form of an information management system. At the core it is running off of PHP and MySQL. I have just updated code, in the more general sense, to mysqli, but without taking full advantage of all of the features. That is part of what I am working on now.
I have read a ton about prepared statements and this is something I certainly need to put to use given the number of statements that get re-used.
- I am looking at making in the realm of about 50 prepared statements, being used across nearly 200 different pages. Is there a recommended way to do this? All examples I have seen deal with 1 or 2.
- Due to the ever growing nature of the site, using databases and such, one of the things that I liked with the previous mysql is that it didn't require a connection specified for each query, but does with mysqli. I had to tweak my functions due to this. Is there a recommended solution for this?
I built the site in a procedural form rather that object oriented, but I am always open to suggestions, regardless of the format they use.
I'll try to be as accurate as possible, but I'm not an expert.
Your first point: You're probably looking for Stored Procedures. Basically you can store certain logic of your application for repetitive usage.
Prepared Statements, however, are different. They basically mean "Parse once, execute many times" but they're not stored on the server and carried out across connections.
In PHP, each "page load" is a different thread with its own variables and thus its connections to the database, so you cannot really use the Prepared Statement again.
As for your second point, mysql_query() doesn't require a connection handle to be passed to it simply because it assumes you want to use the last created connection.
mysql_connect(); mysql_query("SELECT * FROM table");
$link = mysql_connect(); mysql_query("SELECT * FROM table", $link);
are the same. So using the connection implicitly doesn't mean scalability.
That's as far as I can write without providing possibly wrong information, so I highly recommend to you really read about this, and then if you have some question everybody here would be happy to answer.