Latest Stories

You can use PHP, (in conjunction with SQL and HTML), to create database driven websites.

To create a database driven website in PHP, you need a database management system (DBMS). Common database systems include MySQL, Microsoft SQL Server, and Oracle.

Your DBMS can be located either on the same computer that the website is on, or on another server. It's good practice to separate your database server from your web server, but if you've only got one machine to develop on, sharing the same machine shouldn't cause any problems (as long as it's powerful enough to run a web server and database server etc).

Anyway, once you have a database with some tables and some data, you can connect to it and query it.

MySQL is a database system commonly used with PHP websites. The following examples demonstrate how to connect and query a MySQL database.
Connecting to the Database

Before you can query your database, you need to connect to the database server, then locate the database. Once you've done this, you can send in your SQL code to do your queries.

To connect to the database server:
mysql_connect("localhost", "web_user", "LetMeIn!") or die(mysql_error());

The above code uses the mysql_connect function to connect to the database server. We provide the following parameters: Server, Username, Password. PHP needs this info so that it knows which server to connect to. In this example we are just connecting to the local machine so we use "localhost" as the server.

We have also used the PHP die and mysql_error functions to be used in the event there's an error and PHP can't connect to the server. This will display the error message which can assist us in determining the cause of the problem.

To select the database:
mysql_select_db("MyDatabase") or die(mysql_error());

The above code uses the mysql_select_db function to select the database from the database server. You need to do this because, your database server could contain many databases. You need to tell PHP which database to use.

Again we use the die and mysql_error functions to be used in the event of an error.
Querying the Database

You can use the mysql_query function to send a SQL query to the database:
$result = mysql_query("SELECT * FROM Individual")
or die(mysql_error());

What we do here is, assign the results of a query to the $result variable. The query is acheived by passing a SQL statement to the mysql_query as a parameter. In this SQL statement, we are selecting all records from the "Individual" table.

Once again we use the die and mysql_error in case there's an error.
Displaying the Results

To display the results, you need to loop through the results of the query and display each record with each iteration of the loop:

while($row = mysql_fetch_array($result)){
echo $row['FirstName'] . " " . $row['LastName'];
echo "<br />";
}

Here we use a while loop to loop through the results of the query. The while loop keeps iterating until it finishes the last result. This means we can display each result as it iterates past that result. We display the result using $echo and the PHP $row variable, indicating which columns we want to display.

The Whole Code

Combining the above code, (and adding comments), results in something like this:

<?php
// Connect to the database server
mysql_connect("localhost", "web_user", "LetMeIn!") or die(mysql_error());

// Open to the database
mysql_select_db("MyDatabase") or die(mysql_error());

// Select all records from the "Individual" table
$result = mysql_query("SELECT * FROM Individual")
or die(mysql_error());

// Loop thru each record (using the PHP $row variable),
// then display the first name and last name of each record.
while($row = mysql_fetch_array($result)){
echo $row['FirstName']. " - ". $row['LastName'];
echo "<br />";
}
?>
Read More ...

Latest Stories

What is a Database?

A database is a collection of data. That may sound overly simplistic but it pretty much sums up what any database is.
A database could be as simple as a text file with a list of names. Or it could be as complex as a large, relational database management system, complete with in-built tools to help you maintain the data.
Before we get into dedicated database management systems, let's start with the basics - let's look at a simple text file example.

Text File

Imagine we have a text file called "Individual.txt", and that the contents look like this:
Notepad text file We could use this information to do things such as send an email to everyone on our list. We could do this because, due to the way we designed the list, we know that each row contains a different individual, and the information on that row is related to that individual. Also, the items in each row are separated by commas. Therefore, we know that the email address next to "Homer" is his email address. We could also call each row a record. Therefore, we currently have 4 records in our database.
With a small list like this, a text file may serve our purposes perfectly.

Spreadsheet

Another option would be to store it in a spreadsheet using spreadsheet software (for example, Microsoft Excel). That way, we could do some extra things with our list (such as format it, or sort by first name/surname etc).
A spreadsheet program like Excel makes these tasks relatively easy to do. Also, programs like Excel organize the data into rows and columns, making your data easier to comprehend. Something like this:
Excel spreadsheet

Database Software

A better option would be to store the data in a database table using specialized database software, such as Microsoft Access. Something like this:
Microsoft Access database table

So What's the Difference?

You may be wondering what the difference is between the last two examples (Excel vs Access). After all, both examples have the data organized into rows and columns.
There are many differences between spreadsheet software and database software. The rest of this tutorial will show you why database software is a much better option for creating databases.
Read More ...