In the previous php mysql tutorial
we went over the basics of php and mysql. With some information on installing the software(s).
Setting up a MYSQL Database
Before we go any further the first thing we need to do is create a database so we know what to build our php scripts around. For this tutorial we are going to base our examples on an address book application. Really database construction is a whole tutorial on it own, If you are planning to create a fairly complex site your going to need a database to match. Here is a list of mysql relational database pages.
1. Mysql Relational Database training couse - http://www.wellho.net/course/mqfull.html
2. Official MYSQL Relational database white paper, features some good information. - http://www.mysql.com/it-resources/wh...s/embedded.php
Okay back to our tutorial, there is a few ways in which you can create the database for example you can use a powerful GUI application called phpmyadmin http://www.phpmyadmin.net/
for more information on installing phpmyadmin visit the site. or you can manually do it using a php script both are as acceptable as each other but do try out phpmyadmin.
Okay now we need to decide on what we want our database to store and how it stores the information. Considering we are going to use an address book as our tutorial lets consider what we will need to take from the user in order to make it a useful program. Here is a list of fields that we will be using during this tutorial feel free to add your own if you feel confident enough just remember to keep the changes consistant though the series of this tutorial:
# First Name
- Pretty obvious
# Last Name
- Again… Pretty obvious
# The persons phone number
- We’ll assume the maximum a phone number can have in the UK is 11 Digits I think it is true in the UK (were I’m from)… I could be wrong.
# Mobile Number
This will store the mobile phone number of that person allowing 11 numbers to be stored as the mobile number.
# The persons email address
- This will store the email address of that person.
# Country Of Residence
- This is were the individual is residin.
Great, now we know what we want out of our database lets go on to create the tables using a php script, but feel free to use phpmyadmin here is a tutorial for phpmyadmin A quick note: http://www.micfo.com/flash/php.htm
This tutorial is fantastic as it’s a flash tutorial so it’s super quick to understand and do for yourself.
PHP Script: creating tables for the database
To create a table in php all you need to do is execute the following code once, on your system and php will create everything requested.
@mysql_select_db($db) or die( "Unable to select database Please contact the administrator.");
$query="CREATE TABLE address_book (id int(7) NOT NULL auto_increment,first_name varchar(50) NOT NULL,last_name varchar(50) NOT NULL,phone_number varchar(11) NOT NULL,mobile varchar(11), email varchar(50) NOT NULL,PRIMARY KEY (id),UNIQUE id (id))";
Okay now i’ll explain each element:
Lines 1-3: These are the variables which store our username, password and name of database all are required to connect to mysql. You can find out your username, password and database name through phpmyadmin, cpanel or your web hosts technical support.
Line 4: this is the mysql connect function it tells mysql your connection credentials and trys to login.
Line 5: Once mysql connect ‘connects’ us it then uses mysql_select_db function to tell mysql which database we would like to access. If for any reason we cannot connect due to server outage or mysqls off then the following error will be returned to the users browser.
“Unable to select database Please contact the administrator.”
Lines 6-10 - Will Create the SQL query:
# CREATE TABLE address_book - Creates the table address_book in the database we specified in line 5.
# id int(7) NOT NULL auto_increment - Create the ‘id’ field allowing upto 7 numbers to be entered no other characters will be allowed beacause we have specified this field int which means integer. its another way of saying number in mathematical slang. The auto_increment means to add one so the first entry will be 1 the second 2 and so on.
# first_name varchar(50) NOT NULL - This creates the first_name field and sets it to a maximum number of letters, characters or numbers in this case it’s fifty varchar() means you can enter letters, characters or numbers into the mysql field. NOT NULL is another way of saying not empty.
# last_name varchar(50) NOT NULL, - Creates the first name field. Limited to 50 chars.
# phone_number varchar(11) NOT NULL, - Creates the phone_number field. Limited to 11 chars. NOT NULL so the field is required.
# mobile varchar(11) - Creates the mobile field. Limited to, again 11 chars. This field is not required. as NOT NULL is not specified.
# email varchar(50) NOT NULL, - Creates the email field again not required and limited to 50 chars.
# PRIMARY KEY (id),UNIQUE id (id))”; - This means the ‘id’ field is the primary key or in other words the field is used mainly for indentification. UNIQUE means that this field cannot contain muliple records with the same ‘id’.
Now all you need to do is upload the code in a text file and run the script in your browser, Once you’ve ran it then check in phpmyadmin to see if the table and fields have been created.
See the web version at my blog www.chauy.com
, why not have a quick look in the php tutorials
section if you have a little free time.
Part 3 Coming Soon. Thanks for letting me ramble