Databases are normally an integral part of your website. Whenever you see dynamic content (e.g. news posts, event listings, calendars etc.), you can almost guarantee it’s driven by a database. Due to the open source nature of PHP, the number one choice when it comes to databases is MySQL. As I use MySQL day in, day out, I’ll be focusing on this, but don’t forget there are other options available. These include PostgreSQL, SQLite, and Microsoft SQL Server (commonly just called “MSSQL”) which are all worth checking out. Your choices aren’t just limited to these though as there are plenty of enterprise level database servers (which include MySQL and others by the way!) available.
So what is a database?
In summary, a database is somewhere you can store data. All the databases I mentioned in the preamble are based on SQL which stands for Structured Query Language. They are all based on a common language (I say based because they extend the SQL language and add their own features) which attempts to give pre-defined structure to storing and retrieving data. Not only do you have a huge choice of different SQL servers, you have a choice of different database storage, such as Amazon S3, MongoDB, CouchDB and many others. CouchDB for example is what’s known as a document-orientated database, but that’s a whole different kettle of fish.
SQL and database storage in general is a mammoth topic, so before you go any further into this article, I suggest you read up on some MySQL introductory tutorials first. I’ll continue this PHP tutorial with the assumption you know basic MySQL.
Read the rest of this entry »
It annoys me when I see tutorials for learning PHP with code like this:
$fd = @file("doesnotexist");and this:
$md = mysql_connect("localhost", "user", "pass") or die("Could not connect!");It’s bad practice in the real programming world and shouldn’t happen. The at symbol (@) is used to silence any errors caused by calling a function as shown above, when proper error handling should be implemented. Similarly, the practice of using “or die” to error check needs to stop being taught.
Using this language in PHP really needs to stop because there are much better ways to handle errors, such as using exceptions, or any sort of error handling except this really. My personal favourite is using an exception and catching it further down the stack. There’s loads of tutorials on error handling so I won’t go into it, but I just wanted to vent my hatred for this poor excuse for shoddy coding.
And while I’m at it, stop using mysql_* functions and start using PDO! 
I didn’t even think you could do this:
UPDATE table SET something = REPLACE(something,'oldtext','newtext');
How handy! For replacing text in MySQL tables that is… not for anything else really.
I’m just writing a query at the moment that filters out test records from a database. These records have a foreign-keyed value that identifies test apps, e.g. a table with:
id name
1 TEST
2 ANOTHER_VALUE
3 SOMETHING_ELSE
This is joined onto the main data table, sensibly with a query like this:
SELECT *
FROM the_data
LEFT JOIN the_values ON the_data.some_value = the_values.id
WHERE the_values.name != 'TEST'
The gotcha is however, that this doesn’t work as you’d expect. If a record in “the_data” table doesn’t have a corresponding entry in “the_values” e.g. if “the_data.some_value” is 0 (as in my instance, it doesn’t need to have a corresponding value), then these entries are completely excluded. The solution (simple once you understand how it works…) is to check for NULL values too:
SELECT *
FROM the_data
LEFT JOIN the_values ON the_data.some_value = the_values.id
WHERE (the_values.name IS NULL OR the_values.name != 'TEST')
A quick tip – you can import a CSV into a MySQL table pretty easily. The file must exist on the server and you must create the table first, but this command will import your CSV into a MySQL table easily:
LOAD DATA LOCAL INFILE '/tmp/data.csv'
INTO TABLE `data_table`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
