An Introduction to MySQL Databases
Added 11:13pm, Monday 18th May, 2009.Written by Valeria.
0 Comments. Add Yours!
MySQL databases combined with PHP programming provide the backbone for many websites, ours included. There is exceptional possibilities available combining the two, but in order to create a dynamic, database driven website, we have to know how to connect!
This is the first of my tutorials on MySQL basics. In order to break up the reading into more manageable chunks I'm going to write this as a series rather than one long tutorial. This tutorial will show you how to connect to our database and insert a table for use in the rest of our tutorials.
Being able to utilise the powers of a database and the data it can send and receive to your viewers is an awesome feature that every web developer should be eager
to do. Databases can be used for just about everything, providing you know how to do it. Before we can take giant leaps, though, we have to take the first couple of
steps. This tutorial is at the grass roots level of PHP coding and will show you how to connect to a database, how to add tables to this database and how to insert and
retreive some data.
If you're not familiar with PHP, be sure to browse through our PHP Tutorials and learn the basics before attempting this tutorial. We
will be using PHP to connect to the database!
Getting Started
Before we go anywhere, we need a database. We can't connect to or modify any data without a database! There isn't a command to create databases in PHP; you'll have to browse through your web host's control panel and create one. For our tutorial, we'll be using the database sample. When you've got your database created and the username and password set, it's time to connect to our database. We do this with the function mysql_connect(). This function simply connects to the server we specify.Important!
It's important to remember that just using this function will not connect us to a database. Also, you have to define the connection details (Host, Username, Password and Database) must be defined before we parse the connection to the database. To do this, just make sure that the connection function is after your connection details have been defined.
It's important to remember that just using this function will not connect us to a database. Also, you have to define the connection details (Host, Username, Password and Database) must be defined before we parse the connection to the database. To do this, just make sure that the connection function is after your connection details have been defined.
<?;
$connection = mysql_connect("host", "username", "password");
mysql_select_db("database", $connection");
?>
Notice we've put the connection details into the $connection variable. This isn't a necessary step, but it makes life a lot easier if
we're into this habit in the long run. When scripts get more and more advanced, we can sometimes have hundreds of queries and functions in one page; keeping
things as seperated as possible keep things nice and clear.
And that's our connection sorted! With just these two functions, we've connected to a database! See how simple it is? Now we'll look at how to send and retrieve simple
data with equally simple functions.
Creating Fields into the Database
Before we can enter details to a database, we need a table to enter them to. We create a table with a simple function: CREATE TABLE. This is used in conjunction with a variety of parameters to create our table. For our example, we're going to create a table called "profile" and create four fields; ID, name, age, country.Exceptionally Important:
When creating databases, tables and fields, always use lowercase for everything. MySQL is very case sensitive when you least want it to be and will treat "Sample" and "sample" as two completely different fields. Using uppercase will undoubtedly lead to frustration in the long run. Get into the habit now of making sure all your database, table and field names are lowercase.
When creating a table through PHP, we have to define the various parameters our various fields will use to define what data can and can't be entered. For example,
we always want to set an ID field to an integer (INT for short). This means that the ID can only be a number. Any information that tries to insert into the database in
the ID field that isn't a number will be truncated.
When creating databases, tables and fields, always use lowercase for everything. MySQL is very case sensitive when you least want it to be and will treat "Sample" and "sample" as two completely different fields. Using uppercase will undoubtedly lead to frustration in the long run. Get into the habit now of making sure all your database, table and field names are lowercase.
Truncated is a fancy word for delete. Truncating a database literally means removing fields from it; in the form of submitting data, truncating means
information is deleted or parsed before being entered into the database. A perfect example is using INT.
We want to define all four of our fields. For name and country, we want to use the TEXT parameter to make sure any characters can be entered. For age, we'll want
to use an INT again to ensure it's a number. The chances are nobody will be over 1000, so we'll also limit the age field to 3 characters. All of this is done with the
following code:
mysql_query("CREATE TABLE _NameOfOurNewTable_ (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name TEXT NOT NULL,
age INT( 3 ) NOT NULL,
country TEXT NOT NULL
) ENGINE = MYISAM
This probably looks like jibberish at the moment, so let's break this down line by line and explain what it does.
mysql_query("CREATE TABLE _NameOfOurNewTable_ (
This line is telling our parser to execute the mysql_query within the brackets. CREATE TABLE is the function used to begin
creating a table. The paremeters that come after obviously define how the table is made and what settings we're using.
The first parameter is the name of the table we want to create. This can obviously be anything, but remember my tip about keeping everything lowercase. The
capitilisation above is just to make the words stand out.
After this, we want to open the brackets to define the parameters of each individual field.
Whenever entering information to a database through PHP, always be sure not to quote things like your table names, field names,
etc.
'id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY, is creating our ID field. You might be wondering why every field in our
above script is defined as NOT NULL. This is to make sure that data is entered when later entering information to our table. PHP
My Admin, a very popular (and efficient) database editing tool adds this in; I have not encountered many other parsing engines that add this command in.
AUTO_INCREMENT tells our database to automatically increase this field's number by one everytime a new query is inserted to
the database. For fields that are serving as an ID, which obviously goes up everytime you insert data, AUTO_INCREMENT should
be used. It makes life a lot easier and saves you doing it manually!
PRIMARY KEY is telling our database to set the ID field as our primary key. Every table needs a primary key. The primary key is a
unique field that must have a unique value for every entry in that table. The primary key is essentially used to tell each entry apart. You can set fields such
as names, emails, etc, as your primary key, but it makes life very complicated in the long run.
Even with no use whatsoever for an ID in your script, it keeps things very simple if you use one anyway as your primary key. Each time some
data is added to your database, the ID will rise by one thanks to auto increment. This will ensure you always have a unique primary key and prevent errors.
We don't need to explain what the rest does, except perhaps for age. For name and country, TEXT defines this field as text,
which means we can enter just about anything into this field and the database won't be too picky. For our age field, we're using INT again, and with the use of (3), we're limiting this field to 3 characters.
You can limit any field by putting a number in brackets after the field type. For example, if we wanted to restrict country to five characters, we
would use TEXT(5) instead of TEXT. It's best to avoid using restrictions on fields that have
an unpredictable length.
The last line, ) ENGINE = MYISAM is closing the paremets (courtesy of the closing bracket) and telling our parser what sort of
table to create. Because we're using MySQL, we want to use MYISAM, which is MySQL's default storage engine. This means data is entered into MySQL databases by
default through the MYISAM engine. Very rarely will your engine be any different from this when using a MySQL database.
To play along, copy the above query into your file with your database connection, upload it to your page and refresh. Providing everything is in order, it will create the
four fields along with the profile table. This gives us something to enter data onto in our next tutorial.
Comments are disabled.
