Internet Database Options
Database technologies
There are a number of database management systems (DBMS) suitable for web applications. MySQL is possibly the most ubiquitous, but other DBMS may be better suited for specific applications
MySQL is, as mentioned above, extremely common. It is suitable for most applications because it provides many different ways of storing information. One format may be faster, another may allow for more users at once.
An alternative DBMS is PostgreSQL. PostgreSQL offers more flexibility, and as such has significantly more complexity. It can recognize several different languages for retrieving data, rather than just SQL, and has many object orientated features, such as tables being able to inherit properties of ‘parent’ tables.
I, personally, am only familiar with MySQL, and in the rest of this article I’ll discuss how MySQL works with PHP.
Connecting to and accessing a database
In PHP, there are five steps to accessing a database. The first is actually connecting to the host where the database is found. For MySQL the method of connecting follows the form mysql_connect(DB_HOST, DB_USER, DB_PASSWORD). DB_HOST being the address where the database is found, DB_USER and DB_PASSWORD being the username and password used in MySQL.
The second part is selecting the database to be used. A particular copy of MySQL can, and probably will be hosting several databases. mysql_select_db(DB_NAME) is how to choose a particular MySQL database, where DB_NAME is the name of the database in question.
The third part is to create a SQL query. SQL, or Structured Query Language is an almost universal way of accessing data from databases. Most types of database use this language, including MySQL, Microsoft Access and many others. The results of the query are usually stored in a variable for later access. $linkresult = mysql_query("SELECT * FROM `links` WHERE `category` = 'games'"); is an example of a SQL query being used to access a MySQL database in PHP.
The fourth stage is reading and displaying the results from the query. The following snippet reads off the results and displays them as an HTML list of links with images:
if (mysql_num_rows($linkresult) > 0) {
while($row = mysql_fetch_object($linkresult)){
echo "<li><a href=\"".$row->url."\" title=\"".$row->linkname."\" ><img src=\"img/".$row->id.".gif\" alt=\"".$row->linkname."\" /></a></li>";
}
}
This is a fairly complicated example, but there are many different ways to display the query results.
Finally, after using the database’s data, one should clean up after oneself, to free up system resources, and to prevent clashes should you need to run other database queries in the same script. mysql_free_result($linkresult); empties the variable holding the query results.
To summarize, the steps in connecting to and accessing a database are:
- Connecting to the database host
- Selecting the database
- Querying the database
- Displaying query results
- Cleaning up