Get started with MySQL
Last update on: 10-10-2008This is the beginning of when you will start love PHP and make MySQL as your friend when thinking about building dynamic websites :-), we will start working with data bases.
You can do everything you want with date base, and increasingly it makes your life easier to manipulate data, you can quickly build all sorts of scripts, such as guest-book, news, forum, online store and so on.
But before embarking, we must understand the operation, then lets start ;-)
Create a MySQL table
CREATE TABLE test_tbl ( id int NOT NULL auto_increment, date varchar(20) NOT NULL, name1 varchar (50) NOT NULL, email varchar(55) NOT NULL, PRIMARY KEY (id) );
How it works?
To create a mysql table via phpmyadmin or other, you need to use the expression CREATE TABLE then the name of that table, in this example test_tbl, attention, the .,- are not considerate!!.
on the second line, id int auto_increment, means that we create an id column that will be incremented by a number to each record, like it says auto increment means that it will be all alone during insertion.
Then we define the other column, date, with a maximum length of 20 characters (varchar (20)) ... etc
The last line shows that the id is the primary key of the table in question.
Example of insertion:
to insert a record into a table, you must use the insert into()
INSERT INTO test_tbl (id,date,name1,email) values('','10-10-2008','leo','webmaster@iteachweb.net');
#You can also be shorter with only the data to insert
INSERT INTO test_tbl values('','10-10-2008','leo','webmaster@iteachweb.net');
connect to a data base with php:
To connect from a php page, you must use the following parameters:
$db = mysql_connect('host server','your login','your password') or die ("server connexion error");
mysql_select_db('data base name',$db) or die ("base connexion error");
If you have correctly completed the host, login, password and data base, no error message should appear.
Lets take a specific example, go to phpmyadmin, and then insert the following:
MySQL Table and data:
CREATE TABLE test_tbl (
id int NOT NULL auto_increment,
date varchar(20) NOT NULL,
name1 varchar (50) NOT NULL,
email varchar(55) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO test_tbl VALUES('1','10-10-2008','leo','webmaster@iteachweb.net');
Okey! We created the mysql table and inserted the data into the data base.
How to display the result from your site?
Display the content of the test_tbl table:
//get connected to the data base $db = mysql_connect('host','login','password') or die ("connexion error"); //select the data base mysql_select_db('base name',$db) or die ("base connexion error"); //to retrieve data, we use the select function //select the columns id,date,nmae1,email form the test_tbl table $req = mysql_query("SELECT id,date,name1,email from test_tbl") or die ("sql error ".mysql_error()); //to display we use 2 functions, the while() loop and mysql_fetch_array() //that includes data from the query as an associative array while ( $result = mysql_fetch_array($req)) { print 'The id ->'.$result['id'].' date ->'.$result['date'].' Name->'.$result['name1'].' email ->'.$result['email'].'<br>'; } //always close the connexion mysql_close(); The output will be: The id ->1 date ->10-10-2008 Name-> leo email-> webmaster@iteachweb.net The id ->2 date ->10-10-2008 Name-> sarah email-> sarah@sarah.com The id ->3 date ->10-11-2008 Name-> john email-> john@johndomain.com The id ->4 date ->10-12-2008 Name-> jack email-> jack@jackdomaine.com
That is how we do to retrieve data from a mysql table, now lets see how we can insert data from a webpage.
The html code for the web page:
<html><head><title>form</title> <head> <body> <form method="post" action="insert.php"> <input type="text" name="name1"><br / > <input tupe="text" name="email"><br / > <input type="submit" name="submit" value="Send"> </form> </body> </html>
The insert.php page:
//1st connect to the host server $db = mysql_connect('host','login','password') or die ("connexion error"); //2nd we select the data base mysql_select_db('base name',$db) or die ("base connexion error"); //we define the date $date = date("m-d-Y"); //now we insert the data into the table mysql_query("INSERT INTO test_tbl (id,date,name1,email) VALUES ('','$date','$_POST[name1]','$_POST[email]') ") or die ("insertion error ".mysql_error()); //close the connexion mysql_close();
Make sure you practice these examples and make changes and add other things to be able to understand well this lesson.
If you did we will now move to how to update and delete date from data base.
PHP and MySQL's lessons:
Introduction To PHPGet Started With PHP
PHP Variables
PHP Variables Of Environment
PHP Conditions
PHP Looping
PHP Cookies
PHP Working With Dates
PHP Working With Arrays
PHP Working With Files
PHP Play With Strings
PHP And Forms
Send Emails With PHP
The Include Statement
Get Started With MySQL
MySQL Update And Delete
The WHERE Clause
MySQL Functions
Guestbook Script
Websites Directory Script
Multiple Pages With PHP
Create Your Forum With Php