In a recent post to his blog Rubayeet Islam compared the more traditional way of running a query in MySQL versus a prepared statement with the MySQLi extension.
Consider a PHP-MySQL application where the information of 1000 users is being retrieved from the database by running a for loop [...] in each iteration, the first thing the MySQL engine does is to parse the query for syntax check. Then it sets up the query and runs it. Since the query remains unchanged during each iteration(except for the value of user_id), parsing the the query each time is definitely an overhead. In such cases use of prepared statements is most convenient.
He explains what prepared statements are and some of the advantages around them and includes some benchmarking examples to show the differences - about a five second jump in favor of MySQLi.
On the Internet Super Hero blog, they take a quick look at what's new in the MySQL native driver version that will be included in the upcoming PHP version, PHP 5.3.
PDO_MYSQLND is in the PHP CVS repository at php.net: PDO_MYSQL has been patched (PHP 5.3, PHP 6.0). Try out PDO_MYSQL with the MySQL native driver for PHP (mysqlnd). Its has new features.
They do a short recap of what the native driver libraries are all about and some of the advantages to using them. They look at some of the "memory tricks" supported by the driver and a look at the prepared statement and procedure support.
Stefan Mischook has posted a new introductory video for those just starting out with SQL in their PHP scripts - a look at using a (MySQL) database.
I've finally released my first video on using SQL statements in PHP pages. This is the next step after connecting to a MySQL database. I hope you find the videos useful.
The video looks at the "big four" - insert, update, delete and select, but focuses on the select statements and how to use them with function like mysql_fetch_assoc and mysql_fetch_array to get the results out of your database.
Harry Roberts has a quick post to his blog today showing a method he's come up with to handle PHP arrays from inside of SQL statements (a port of the JsonSQL library to PHP).
Trent Richardson created a very small and simple JsonSQL library for JavaScript which allows you to run an extremely limited subset of SQL against a Json array/object. I quickly ported it over to PHP 5 and it works like a charm, although the syntax for the WHERE clause isn't exactly the same but the rest ported across properly.
He's included some sample source code of it in action - selecting information out of an array of user information, once changing their order and the other grabbing a username. Complete source for the library is included too.
DevShed has posted the latest part of their introduction to PHP series, this time focusing on one of the more useful features of the language - conditionals and loops.
We discussed statements briefly in our last article and even got a sneak peek of an IF-statement. Sure I mean, the image was blurry, and the moment it saw us it ran off into the forest. But we saw it. Honest. So in this article, we're going to take a much closer look at PHP statements and even start learning about loops.
They cover if statements (as mentioned) as well as introduce you to its companions, else and else if. The finish off the article with a brief look at one of the simpler loops, the for loop.
Charles Rowe shares four reasons/benefits he's come up with that should make you think about choosing MySQLi over the normal MySQL libraries for PHP for your application.
There still seems to be a lot of confusion over the differences between the two extensions despite the length of time that mysqli has been in the wild. I wanted to briefly review the four major benefits of mysqli.
He also includes a few more links to further information (besides the explanation for each of the topics listed above) including an article from the Zend Developer Zone and a tutorial covering making the switch to MySQLi.
In this new post on Raphael Stolt's blog, he shows a way that you can take XML that holds SQL information (in his example INSERTs and DELETEs) and transforms them into SQL statements via XSL stylesheets.
A canny data import technique that emerged from praxis, while working on the import of data-centered XML resources, is utilitizing the abilities of Xslt. The generation of the required SQL statements actually only needs a simple Xsl stylesheet which might import for an PHP XSLTProcessor object or pass to the xsltproc command line tool. Both further described approaches are based upon the libxslt library and are assuming the use of XSLT 1.0.
He starts with an example bit of XML that has the XSL stylesheet at the top that will be used to transform the data and the information to perform inserts on several "partner" values in the XML below. Following this, he creates a PHP class to load the file and apply the stylesheet.
He also mentions a few different approaches to the same problem - XSLTProcessor class approach returning a single SQL string , xsltproc approach and the XSLTProcessor class approach using the ability to use PHP functions as XSLT functions.
There's a quick tip from Vinu Thomas on his blog for anyone having issues with UTF-8 data in a MySQL database (as accessed by PHP).
Most of us have had problems with UTF-8 problems in PHP and MySQL. Here's how to modify your database and table to be UTF-8 compliant. Most of the time we do set the character set to utf8 but forget to set the collation set to utf8.
He includes both SQL statements you'll need to make the transition - two ALTER statements that update the properties of a database and change the encoding on a specific table to UTF-8 compatibility.
In the constant pursuit of exploring what else is out there, Jeff Mooretook a look at using PDO and MDB2 in some of his scripts and tried it out in a simple test program:
I was just putting together a small test program and I thought I would try using PDO. I really haven't done anything serious with PDO, just try it a couple times. Unfortunately, this didn't work and it took me a few minutes to figure out why. Actually, I still don't know exactly why it doesn't work, but I did find a way to make it work.
He found PDO slightly different to work with than what he was used to so he moved on to MDB2 to see how the same code would fare there. Things worked smoothly there, handling the prepared statements he wanted to use perfectly.
In another part of their series of excerpts from the Zend PHP Certification Guide (from Zend), DevShed has posted this article talking about debugging and performance issues and methods.
Making mistakes is human, and so is fixing them. In your day-to-day programming adventures, it's inevitable to introduce bugs in your PHP code, especially when you're writing very complex applications with tens of thousands of lines of code spread across tens of files.
In this article they look at things like flattening if statements, spplitting single commands across multiple lines, and the difference between =, ==, and ===.
