So you think you can dance.. err… program? You want to build a website, not a plain static site using only HTML programming language, but a dynamic one where you will be able to store data. Remember that even blogs like this one use PHP/MySQL. All your user’s input, comments, feedbacks, contact info essential for your business to follow up on them, is stored in a database. You will have access to this database and run queries to extract your needed data.
Although there are other programming and databases methods available, for the purpose of this article I am going to write about the PHP programming language and MySQL database. This is the most widely used combination on the web. It could be implemented on a Windows Server, if you don’t have any other choice, but not recommended. You need an Apache Server, which most hosting companies have available as primary choice.
These are our weapons: Apache Server, PHP knowledge, MySQL. Let’s start this step-by-step tutorial.
Create a database through your cPanel account (your hosting provider will give you one, if they don’t, find a new one). You may also create a database accessing directly to phpMyAdmin. As an example, we name this database cars.
Here is the script to create our database named “cars”:
CREATE DATABASE cars;
USE cars;
CREATE TABLE usedcars (
id int UNIQUE NOT NULL,
make varchar(40),
model varchar(40),
year varchar(40),
PRIMARY KEY(id)
);
INSERT INTO usedcars VALUES(1,’DontCare’,’GolfCart’,’2009′);
We then create a user, ferrari, assign a password, i-cant-drive-55, and we give permission to user ferrari to access database cars. Kids, don’t do this at home… or on the streets.
When creating a password, please make sure to make it difficult to guess, using upper and lower cases, numbers and special characters.
Having this info, we now create a script to open a connection to the database:
<?php
$database = “cars”;
$username = “ferrari”;
$password = “i-cant-drive-55”;
$hostname = “localhost”;
//connection to the database
$dbhandle = mysql_connect($database, $hostname, $username, $password)
or die(“Unable to connect to MySQL”);
echo “Connected to MySQL”;
?>
The “localhost” indicated above is very common, it is where your database resides, usually on the same server as your hosting account. If it’s different for you, ask your hosting provider.
Running this script successfully, you will see the message “Connected to MySQL”. If you see the message “Unable to connect to MySQL” check your connection settings, make sure database, username, password, hostname, are correct.
That’s it. We have created a connection to our database.
OK, let me continue by using a function to perform a query. This function is named mysql_query(). It returns a resource that contains the results of the query, called the result set. To examine the result we’re going to use the mysql_fetch_array function, which returns the results row by row.
Let’s add the code to our script:
<?php
$result = mysql_query(“SELECT id, make, model, year FROM cars”);
while ($row = mysql_fetch_array($result)) {
echo “ID:”.$row{‘id’}.” Make:”.$row{‘make’}.”Model:”.$row{‘model’}.”Year:“.$row{‘year’}.”<br>”;
}
?>
The above query will return (based on the records stored in your database)
ID: 1
Make: DontCare
Model: GolfCart
Year: 2009
The following step is not really necessary, but it is a good habit to close the connection.
<?php
//close the connection
mysql_close($dbhandle);
?>
Have fun!
