Lecture 23
Database Connectivity in PHP
Mr. Mubashir Ali
Lecturer (Dept. of Computer Science)
dr.mubashirali1@[Link]
1
Summary of the previous lecture
• Writing regular expression in PHP
• Validating user’s input
• String functions
Mubashir Ali - Lecturer (Department of
2
Computer Science).
Outline
• Creating database in MySQL using WAMP
• Connecting PHP with MySQL
• Inserting data in database
• CONNECTIONS: user registration
Mubashir Ali - Lecturer (Department of
3
Computer Science).
1. Creating database in MySQL using WAMP
• Go to home page of WAMP server
• Select PHP myadmin
• Login to MySql
• Enter database name and click create
database button
Mubashir Ali - Lecturer (Department of
4
Computer Science).
1. Creating database in MySQL using WAMP…
• Creating a table in a database:
• Select database from the list of databases
• Enter table name and number of columns
• Enter name, data-type and other required
properties for columns
• Click create table button
Mubashir Ali - Lecturer (Department of
5
Computer Science).
2. Connecting PHP with MySQL
• mysql_connect(“hostname” ,”username”,
”password”)
– mysql_connect(“localhost”,”root”,””)
• mysql_select_db(“database name”)
– mysql_select_db(“testdatabase”)
Mubashir Ali - Lecturer (Department of
6
Computer Science).
2. Connecting PHP with MySQL…
Connect with
Empty password Error
server
Host name message
username
Select database Database name
Mubashir Ali - Lecturer (Department of
7
Computer Science).
3. Inserting data in database
• Create form to receive input from user
• On action page
– Retrieve user’s input
– Validate user’s input (optional)
– Establish connection with database
– Write insert command
– Execute command
Mubashir Ali - Lecturer (Department of
8
Computer Science).
3. Inserting data in database…
• Insert SQL command:
INSERT INTO `table_name`
(list of columns)
VALUES (list of values)
INSERT INTO users
(‘user_Name’,’user_Email’,’user_Password’)
VALUES (‘$name’,’$email’,’$password’)
• mysql_query(query to execute)
Mubashir Ali - Lecturer (Department of
9
Computer Science).
3. Inserting data in database…
post
reg_action.php
for later use
name
email
password
Mubashir Ali - Lecturer (Department of
10
Computer Science).
3. Inserting data in database…
User’s input
is retrieved
Database connection
Insert
command
Command is
executed
Mubashir Ali - Lecturer (Department of
11
Computer Science).
3. Inserting data in database…
Header function
location
message
Close warnings
Message is displayed
Mubashir Ali - Lecturer (Department of
12
Computer Science).
3. Inserting data in database…
message
Mubashir Ali - Lecturer (Department of
13
Computer Science).
4. CONNECTIONS: user registration
• Create database
– Database name: connections
– Table name: users
• Table columns:
– user_Id int 4
– User_Name text 25
– User_Email varchar 35
– User_Password varchar 20
– User_Picture varchar 50
Mubashir Ali - Lecturer (Department of
14
Computer Science).
4. CONNECTIONS: user registration…
post
name
email
password
Mubashir Ali - Lecturer (Department of
15
Computer Science).
4. CONNECTIONS: user registration…
Connection to
the server
Database name
Database selection
Mubashir Ali - Lecturer (Department of
16
Computer Science).
4. CONNECTIONS: user registration…
User’s
input is
retrieved
Database connection
Name
validation
Mubashir Ali - Lecturer (Department of
17
Computer Science).
4. CONNECTIONS: user registration…
Email
validation
Insert
command
Command i
executed
Mubashir Ali - Lecturer (Department of
18
Computer Science).
Summary
• Creating database in MySQL using WAMP
• Connecting PHP with MySQL
• Inserting data in database
• CONNECTIONS: user registration
Mubashir Ali - Lecturer (Department of
19
Computer Science).
References
• Chapter 30, “Beginning PHP and MySQL” by
W. Jason Gilmore, Apress publisher, 4th
edition; 2010, ISBN-13 (electronic): 978-1-
4302-3115-8.
Mubashir Ali - Lecturer (Department of
20
Computer Science).
