|
With the development and availability of open source and powerful tools like PHP
and mySQL, combining the power of HTML and dynamic data has never been easier.
Now with just a simple learning curve, anyone can get started in creating a
truly dynamic web site, one that interacts with its users in every possible way.
This tutorial will help you get started in working with mySQL and PHP. It will
assume that you have no prior experience in using mySQL with PHP before. After
reading this tutorial, you should get an idea on what a database is, how to use
it, and how to access it. You can then go ahead and start reading on the many
more advanced topics regarding database systems.
Introduction to mySQL
MySQL has become a very popular database for usage in personal and commercial
web sites. It is fast, easy, supported by a wide scale of web based scripting
languages, and best of all its free to use. Before we start showing off code,
there is one rule to remember when working with mySQL and PHP. That rule is very
simple; a basic query consists of several parts in PHP:
- Connect To mySQL
- Select The Database
- Define The Query
- Issue The Query
- Return The Data If Any
Almost all mySQL queries when used with PHP follow the above rule. There are
some exceptions like when editing or deleting data.
What is a Query?
A query is simply a command to issue to the database to perform a certain
task. The most basic of queries are those to fetch data, edit data, and delete
data. Of course there are many more, but for the sake of this tutorial we will
be only covering these basics among several others.
How a Database is Divided
You could think of a database as a notebook and the different tables
in it as different sections of the notebook.
Think about, for example, your math notebook for school. You probably divided it
into different sections; Lectures, Sections, Homework, Labs, Handouts. But
whenever you use the notebook, regardless of the section your working on, your
using it for a common purpose and that's math! A database can be thought of the
same way. It organizes different tables holding different data but revolving
around the same purpose.
Let's provide a real life example though of a database instead of all those
school related analogies. The LimitLess Studios web site uses a database on a mySQL server to serve dynamic content. Our database is divided into several
tables:
- News Table - Houses all our news that we publish on the
Home Page.
- Tutorial Table - Houses all our tutorials.
- User Table - Houses information on our users like User
Names and Passwords.
In-Depth Look At Tables
If sections of a notebook are like tables in a database, then the individual
elements for each section are like the fields for each table. Confused? Let's
break it up a second.
Fields, or Attributes if you want to use the literal term, of a
table are the different elements that define how the data will be stored in a
table. For example, take a look at any page in your notebook and look at some
common elements between them. You might have a page number, a date, a lecture
number, and finally the actual notes for the lecture. Even though the elements
are the same, no two pages will contain the same value for the same element. For
example, you can't have two pages numbered 2 in your notebook, but rather page 1
and page 2.
The same analogy can be applied to tables. Consider our Users table mentioned
before. The fields that make it up can be:
- Email Address
- User Name
- Password
On each page in your notebook, these common elements have
values. Each page can be considered a row in a database. A row is simply
a record of data in the database with values for each field being
different.
Again look at your notebook, each page has a content for each element, whether
it be the date or page number. Yet this content is different but they share the
same basic layout. You can't have a page without a page number for example.
Okay enough with the introductions. Let's get down to business and show some
code to create a database and one table so we can show examples on some basic
queries. Usually, depending on your host, you will not have privilege to create
a database unless they do it for you or through some sort of Control Panel they
have provided.
Which ever the way, create a database called examples. On the next page,
we will provide examples on how to create the table through code or using
phpMyAdmin if you have it available to you.
phpMyAdmin greatly simplifies the
task of creating tables in a database - So if you have it, it would be better to
use it!
Okay let's see some code. Use the below code to create a mySQL table using PHP.
An explanation will follow but pay close attention to the comments and you
should get an idea of what is going on.
<?php
// Open Connection To mySQL.
// Replace 'username' and 'password' with appropiate login values for your server.
$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );
// Select mySQL Database.
mysql_select_db( 'examples' , $mysqlLink );
// Create Query To Create Table.
$sql = 'CREATE TABLE `table0` ('
. ' `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, '
. ' `Name` TEXT NOT NULL, '
. ' `Age` INT NOT NULL'
. ' )'
. ' TYPE = myisam;';
// Issue Query.
mysql_query( $sql );
// Close mySQL Connection.
mysql_close( $mysqlLink );
?>
|
What have we done??? You can pretty much guess. Let's divide
the code into sections and explain what is going on in each section so that
things are easier to understand.
<?php
// Open Connection To mySQL.
// Replace 'username' and 'password' with appropiate login values for your server.
$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );
// Select mySQL Database.
mysql_select_db( 'examples' , $mysqlLink );
?>
|
Basically what we are doing here is opening a link to mySQL
on the first line. This function, mysql_connect takes three arguments;
the address of the mySQL server; this will usually be 'localhost', the User
Name, and the Password for the mySQL server.
After this, we selected the database we previously created so we can issue
queries to it. Selecting the database is only required once during an entire
script's execution, unless the mySQL connection is closed.
- Tutorial written by Limitless
|
