Justin Carmony recently put together a blog post looking at the biggest database design oversights that PHP developers can make in their applications.
I've thought of some of the biggest oversights I've had when working with PHP and MySQL and put them in a list. This is my personal list, and I'm sure some people can think of some other oversights that belong on the list as well. This list is just for PHP & MySQL, not PHP and any database. I know many people like using software like Doctrine to allow switching between different database types. That is beyond the scope of this article.
He includes a list of five - not having a data access layer, designing for only one database connection, not including developer logging, having queries written in procedural code and no separation of reads and writes to the database.
The Zend Developer Zone has a new tutorial posted today from Vikram Vaswani about accessing a database with the help of the MDBTools PECL extension to read from a Microsoft Access datbase.
In this article, I'll introduce you to PHP's MDBTools extension, which provides an API to programmatically read data from Microsoft Access database files. If your project involves working with such files, extracting database records either for calculations or for conversion to other formats, you'll find this extension invaluable.
He steps through the installation of the extension (via the pecl command line tool) and some sample code that grabs the tables, all of their attributes and how to select the data out from them.
On the NETTUTS.com site, there's a new tutorial that looks to be a basic introduction to the world of Object-oriented programming in PHP.
Numerous examples from robots to bicycles have been offered as "easy" explanations of what OOP is. I've opted to show you how OOP works with a real-life example, for a programmer. By creating a MySQL CRUD class you can easily create, read, update and delete entries in any of your projects, regardless of how the database is designed.
Their CRUD example not only serves as an introduction to OOP, but can also be an introduction to MySQL support for those that haven't used it before. They create six functions - the four for CRUD and a connect and disconnect. The full source is available for download too.
WebReference.com has another excerpt from the Addison-Wesley book "PHP and MySQL Web Development" covering the connection between PHP and MySQL using their example from previous chapters.
In this chapter, we explain how to access the Book-O-Rama database from the Web using PHP. You learn how to read from and write to the database and how to filter potentially troublesome input data.
The chapter looks at setting up the connection, choosing the table, running queries against it, getting the information from the results and using prepared statements to do it all in a bit safer way. They also briefly touch on other interface methods (like the PEAR MDB2 library).
In this new post to his blog, Brandon Savage looks at a few methods you can use to help reduce the overhead connecting to the database can cause.
Below are a list of my top five quick-and-dirty strategies for improving database performance in web applications. These suggestions are culled from recent experience and mixed with some ideas that I've implemented in my own code. They're not high level, but they are something we need consistent reminders about.
Here's the short version of the list:
Try caching
Reduce the number of queries that run
Use indexes
Optimize data usage
Avoid functions in WHERE statements
You'll have to check out his post for the full versions, though.
The O'Reilly School of Technology now offers a series of PHP certification classes developers can attend to learn more about PHP and SQL on a variety of projects.
The PHP/SQL Programming Certificate Series is comprised of four courses covering beginning to advanced PHP programming, beginning to advanced database programming using the SQL language, database theory, and integrated Web 2.0 programming using PHP and SQL on the Unix/Linux mySQL platform.
Another class will be released in December 2008 - PHP/SQL 3: Seamless Web 2.0 Integration . The classes come it at a bit under $400 USD and include access to an online sandbox for testing (complete with web server, unix-shell and database installations). Completion of all four courses earns the student a certification from the University of Illinois's Professional Development department.
DevShed finishes off their series on database security in PHP applications with this look at protecting your application and data from the threat of SQL injections.
In this article we will continue to explore various attacks that can be made on a database and how to prevent these attacks. We will also build the last page of our site.
Their example script is a login for a secured area of the site and contains a possible place for an injection - non-validated user input. With something as simple as making the username a single quote, an attacker could find out more about your database structure and use that to get further into your systems. To avoid it, they recommend validating with the mysql_real_escape_string function as a first line of defense.
Kevin Waterson has posted a new tutorial today looking at a key part of any web application - the configuration settings and how they can be stored.
PHP applications come in many shapes and sizes. Some used locally from command line, and more commonly, for web based applications. More often than not, regardless of size or type, some form of configuration variables will be stored for global access. [...] Each options has its pros and cons. Here each of these options is explored to see which method is right for your application.
He looks at four different options:
an ini file that can be parsed/modified directly by PHP
an XML file slightly more complex, but still simple for PHP to use
a PHP file with things like PHP arrays defining settings
and a database with one or more configuration tables
Each type comes with some example code and format to give you an idea of how they'd work.
DevShed continues their look at security in (PHP) web application in this next-to-last part of their series - it focuses on database a password security.
In this article we will discuss security for databases accessed through the Internet. We will also examine the issue of password management, since handling that task properly will help us make our web site and its applications more secure.
They continue to use their "Internet cafe" application as an example to show proper escaping practices, the creation of a password management script (including validation of format) and includes a full listing of the code so far.
Lorna Mitchell has an introduction to Zend_Db, the Zend Framework database abstraction layer posted to her blog today:
I recently worked on a project which was based on Zend Framework - I haven't worked with it before and I was temporarily confused by the existing implementation of some of the database-level stuff. After much reading and untangling of code, I'm now pretty clear how this should look, so here's my overview. I'm not going to go into setting up a whole application, but this is a quick primer on how data models go together.
She talks about table modeling, fetching rows/data from the model and working with rows/rowsets.
