« a quick note | main | frbr and music recordings »
MySQL and PHP
30 Jan 08
I learned some SQL a few years ago, and with a refresher find that it’s fairly straightforward. And of course the nice thing about scripting your queries is that you can test them until they’re correct, and then forget about them.
Integrating this into PHP is still a bit of a challenge for me after, what, a day-and-a-half of trying? Things to remember:
- Five basic steps to querying
- Open the database connection
- Select the database (it seems to me these first two have been combined in PHP’s mysqli functions, but I’m using a slightly older text)
- Run the query
- Fetch the query results into an array
- Format and print the results
- Steps one through three require error handling
- The array that’s created in step three is an associative array in which the field name given in the query corresponds to the data returned. The array is processed one row at a time. There’s no need to include the table name, because the array corresponds to the query structure, rather than the table structure. This is easy to visualize if you think of the table that is displayed when a query is run from the mysql command prompt.
So it’s fairly simple to generate a flat html table from data that exist across multiple database tables. I’d like to figure out how to generate not-so-flat data; for example, suppose (just suppose) I had a database in which a number of books are stored in one table, and their chapters are stored in another. I’d like an output that would list the book, then the contents, for each book. The simple approach described above won’t do it. Maybe in another day-and-a-half this too will seem simple, but right now I just can’t quite figure it out.
Williams, Hugh E. and David Lane. Web Database Applications with PHP and MySQL. Sebastapol, CA: O’Reilly, 2004.
Posted by pzed on January 30, 2008 at 1.49pm
Categories: libraries, music, web stuff
