A web page can be as simple as a business card, displaying your professional information to potential clients and describing your services. But it can be so much more! ProZ offers web hosting to its members, as you may know, and one of the most interesting features if you are aware of your site's potential is the databases: even the standard package gives you two of them, which is more than enough to develop all kinds of projects for your website.
What you want to do with this data storage is totally up to you, but you are a words expert; what do you know about making websites? Programmers can be very costly to hire, but consider this: with the proper tools and some time investment, you may be able to create what you need by yourself. There is plenty of help available on the Internet for beginners, and here we will see how to set up your database and use it, step by step.
Note that the programming examples use the PHP programming language.
Step One: Creating Your Database
Obviously, to use a database, you first need to create one. Luckily, ProZ uses cPanel, a very powerful yet user-friendly management tool. Once logged in to your cPanel account, go to the Databases section and click "MySQL® Databases". That page allows you to create, edit and delete databases as well as to manage users (who can log in to which database). In the Create New Database section, simply enter your database's name and click "Create Database". Your first database is ready to use!
Step Two: Preparing Your Database
Another great tool is phpMyAdmin, which you can access in the same section as above (Databases). Once there, choose your database's name on the left to enter it and view the tables it contains. SQL works with a system of tables in which columns can have many specific types to hold all kinds of information. To know which types and structures would best suit your needs, you should consult professionals or seek help online, as many free forums offer advice in this domain and can teach you the basics of data processing.
Your data will be stored in tables with names relevant to the information they contain. These names and those of the columns inside the tables will be important to retrieve the information from your website.
Step Three: Entering Data
There are two ways to enter your data. The first is to stay in phpMyAdmin and insert your information directly from the interface. You can also automate this process based on actions performed on your website (such as member registrations), in which case you should research the approriate code needed (brief examples are also given at step seven).
Step Four: Connecting to the Database
Here begins the programming part. I will provide an example and enter explanation between important lines of code, marked by PHP commentary (//).
// First, we set the server information (typically "localhost"), username and password used to log in to the database and save it in a variable ($connection).
$connection = mysql_connect("localhost","username","password");
// We then verify if the variable exists, which means, in this case, that the connection was established.
if (!$connection)
// If such is not the case, we interrupt the command and display an error message.
die ("Could not connect to database");
// We then select the database needed (its name is preceded by "your-username_"), or display an error message if the command fails.
mysql_select_db(username_database) or die("No connection");
With this cleared, you can access your data from anywhere in the page where this code is placed.
Step Five: Retrieving Data
There are many ways to fetch and select information, so I will simply demonstrate some of the most commonly used commands through examples.
// This first command will perform a MySQL query and select all information (*) from the table named "users". If there is an error, it will display an error message describing the problem.
$query = mysql_query("SELECT * FROM users") or die ('Error: '.mysql_error() );
// In this other example, you only pick the information from the columns "first_name" and "last_name", and you sort them alphabetically by last, then first name (ASCending).
$query = mysql_query("SELECT first_name, last_name FROM users ORDER BY last_name, first_name ASC");
// You could also choose only the 5 most recent users
$query = mysql_query("SELECT first_name, last_name, registration_date FROM users ORDER BY registration_date, last_name, first_name ASC LIMIT 5");
// SELECT queries can also have conditions; let's retrieve only male clients - note the use of single quotes (') to identify the desired content of the column.
$query = mysql_query("SELECT * FROM users WHERE gender = 'male'");
// Maybe you want to be less precise and select all users whose name starts with the letter B...
$query = mysql_query("SELECT * FROM users WHERE first_name LIKE 'B%'");
// contains the letters "Bob"...
$query = mysql_query("SELECT * FROM users WHERE first_name LIKE '%Bob%'");
// or does not end with "ve".
$query = mysql_query("SELECT * FROM users WHERE first_name NOT LIKE '%ve'");
Those are only a short demonstration of the most useful options; be sure to check PHP's official website (php.net) for complete information regarding any function in the PHP language.
To retrieve data, you also need something to handle it. The information must be saved into variables:
// Your $query variable will select the information you want to use. You can then assign it to a $data variable that will hold it in the form of an array, identified by column name...
$data = mysql_fetch_array($query);
// ... or by position in the table (column 0, 1, 2, 3...).
$data = mysql_fetch_row($query);
Step Six: Using the Data
You already have most of the job done. You have chosen the relevant information you are going to work with and stored it in variables that you can reuse in any way. Let us first see briefly how to get a particular cell of the tables we imported from MySQL in the form of arrays. These first examples suppose that you selected only one row (in our demonstration, this would correspond to one user).
// On a very technical level, both methods have different characteristics, but since you are making a small website by yourself, the main advantage of using mysql_fetch_array() instead of mysql_fetch_row() is that it is easier to identify columns:
echo $data[first_name]; // will print your user's first name on your page
// If you used mysql_fetch_row(), this will do the same if "first_name" is the *third* column in your "users" table (remember that the first column is 0):
echo $data[2];
// However, if you specify only certain columns in your SELECT query (we selected all using "*"), the columns are ordered using their order of appearance, not their order in the actual table). Hence, "SELECT column3, column1, column2" will be placed in the variables $data[0], $data[1] and $data[2], following the order of the query, not the order of the table.
// You can then easily use your variables together (let's now stick to mysql_fetch_array(), for clarity).
echo "Welcome to my site, $data[first_name] $data[last_name]. I hope you enjoy the tour.";
You may also want to create and display a list of your site's members. For this, you will need to use all of your table's rows. We will achieve this using PHP's "while" loop condition.
// First, the query to select our information:
$query = mysql_query("SELECT * FROM users ORDER BY last_name, first_name ASC");
// Followed by the loop:
while ($data = mysql_fetch_array($query)) { // As long as there are results (rows) to display, $data contains the current row's information.
// Here can be inserted a list element or table's row in which you use some of the data; for example, a table's row:
echo "<td>$data[first_name] $data[last_name]</td>
<td>$data[registration_date]</td>
<td>$data[country]</td>
<td>$data[age]</td>";
} // Do not forget to close the loop to avoid errors. Your table row will now be repeated every time your query finds a new row in your database; once there are no more results, the rest of your code will proceed, most likely by closing your table/ending your list.
Step Seven: Modifying the Data
Since you already have the data stored in variables, you could edit it and save the new values in your database. We will use a new example here, in which you are keeping track of your earnings of the past month and the whole year. You will use two tables: one is called "june" and contains dates and amounts of money for the month, and the other is called "summary" and simply contains months and total income per month for this year. In our example, you have already entered a few pay checks in your "june" table but now want to enter a new one and update the total for the month in your "summary" table.
I will omit all of the form-processing code and focus on handling the data to and from the database.
// First, we will enter our new amount in the table "june":
mysql_query("INSERT INTO june (amount) VALUES (135)");
// Note that, because of our table settings, the new row's ID is automatically created (auto_increment), which is why the only column we specify is "amount".
// Also, here is a secondary example to show variants:
mysql_query("INSERT INTO june (date, amount, source) VALUES ('15-06-2009', 135, 'Translation Agency')"); // Notice that only numbers do not require single quotes in the "values" section.
// Let's resume. You may have entered incorrect information. Since it is not the first time, you have created a page on your website to edit your database entries. The final query for your edit could be as follows:
mysql_query("UPDATE june SET amount = '153' WHERE date = '15-06-2009' AND source = 'Translation Agency'");
// AND and OR operators can be used for more precise searches and combined using parentheses. Unrelated example:
mysql_query("UPDATE june SET amount = '153' WHERE date = '15-06-2009' AND (source = 'Translation Agency' OR source = 'Anthony')");
// Here the date must be June 15, 2009, but the payment may come from the translation agency OR from Anthony (of course such a query does not really make sense in this context).
// Our data for June is now up to date. All that is left to do is to calculate our new total in our summary of the year.
// We will select our new information, which is the entry in "summary" that belongs to the month of June:
$query = mysql_query("SELECT amount FROM summary WHERE month = 'june'");
// We recover the data from the variable:
$data = mysql_fetch_row($query);
// And we create a variable containing our current (out-of-date) total for June (this is more convenient, but not necessary as we could simply use $data[0] directly):
$total = $data[0];
// We need to first update the total using either of these two lines (depending on your choice with the preivous line)...
$total = $data[0] + 153; // $total not created yet or
$total += 153; // $total already created on previous line
// before finally saving the updated total in our last query:
mysql_query("UPDATE summary SET amount = '$total' WHERE month = 'june'");
Step Eight: Closing the connection
When you are done working with your database, at the end of your script, you should shut the connection. This is done quite simply:
mysql_close(); // If only everything was that simple!
Conclusion
PHP and MySQL are not simple languages to learn if you have no previous experience, but combined, they can do great things. It is definitely worth exploring the possibilities (and learning to use new functions on php.net). Just start simply with online tutorials and ask advice from developer forums, and you will soon grow to enjoy creating new content for your site and realize that you have plenty of ideas to put your knowledge to use! I hope that this short breakdown of how to use a database will help you grasp the basics and inspire you to build your site past the "business card" status.
Canada