Post on 07-Jan-2016
description
PHP & MySQL
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Checking
2 PHP Workshop: 29 June 2 July 2010, UiTM Pahang
PHP
syntaxdone!!!
Nowplay
around with
database
Before that,
create a folder
name phpweb
in htdocs
folder
Databases
Existing RDBMS libraries in PHP (located in php.ini file)
MySQL
MS SQL Server
Oracle
Postgre SQL
SQLite
Firebird
3 PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Databases
How about other RDBMS?
Need to Google it first and IF available for PHP, download the
library file
Configure the library file inside php.ini
e.g. IBM DB2
4 PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Not recommended if you use
xampp/lamp/wamp/vetrigo or
any PHP bundle packages
MySQL
Its a FREE RDBMS (http://www.mysql.com)
Commonly used with PHP
How to manage your database?
MySQL Command Line Client (100% using SQL statement)
[OR]
MySQL administration software tool (click, click & click)
e.g. phpMyAdmin (OSS, Freeware & Web-based)
e.g. DBTools Manager (Freeware & Windows-based) looks alike MS
Access
5 PHP Workshop: 29 June 2 July 2010, UiTM Pahang
MySQL Lab Session
6
Launch DBTools Manager
Create a new database named phpweb
Create fields below:
studentid: integer, autoincremental = true
studentname: varchar (255 char)
studentnumber: integer
studentusername: varchar
studentpassword: varchar
Save the table as student
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
PHP & MySQL
2 ways to establish database connection
mysql_connect()
Establish a new connection each time a PHP page is called up
and then close connection after completing the request using
mysql_close()
mysql_pconnect()
Persistent connection BUT it wont close after completing the
request
Suitable for pages that do have a heavy usage
Need to set up number of concurrent users
7 PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Now, create 1
folder in htdocs.
Rename the folder
to phpweb
PHP & MySQL Lab Session
8 PHP Workshop: 29 June 2 July 2010, UiTM Pahang
phpweb folder is
your root folder.
Place all PHP files,
images, etc in this
folder
Use Notepad or
Dreamweaver to
write PHP codes
PHP & MySQL Lab Session A [1/2]
Save as
dbconn.php
inside
htdocs/phpweb/
PHP & MySQL Lab Session A [2/2]
// continue from previous slide
$dbconn = mysql_connect($host, $user, $pass);
if(isset($dbconn)){
mysql_select_db($dbname, $dbconn) or die("Error: " .
mysql_error() . "");
}
else{
echo "Error: Could not connect to the database.";
}
?>
10 PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Save as
register.php
inside phpweb
folder
PHP & MySQL Lab Session B [1/1]
11
Name:
Number:
Username:
Password:
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
PHP & MySQL Lab Session C [1/4]
12
PHP & MySQL Lab Session C [2/4]
13
// continue from previous slide
$sql0 = "SELECT studentnumber FROM student WHERE studentnumber =
$number";
$query0 = mysql_query($sql0) or die ("Error: " . mysql_error());
$row0 = mysql_num_rows($query0);
if($row0 != 0){
echo "Record is existed";
}
// continue to next slide
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
PHP & MySQL Lab Session C [3/4]
14
// continue from previous slide
else{
/* execute SQL INSERT command */
$sql2 = "INSERT INTO student (studentname, studentnumber,
studentusername, studentpassword)
VALUES ('" . $name . "', '" . $number . "', '" . $username . "', '" .
$password . "')";
mysql_query($sql2) or die ("Error: " . mysql_error());
/* display a message */
echo "Data has been saved";
}
}// close if isset()
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
PHP & MySQL Lab Session C [4/4]
15
// continue from previous slide
/* close db connection */
mysql_close($dbconn);
?>
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Save as
register0.php
inside phpweb
folder
Testing your code
16
Launch a web browser
Type in: http://localhost:8080/phpweb/register.php
You should see an HTML form
Enter required values
Hit Submit button
If success, you should see Data has been saved
message
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
PHP & MySQL Lab Session D [1/1]
17
Login Page
Username:
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Save as
login.php
inside phpweb
folder
PHP & MySQL Lab Session E [1/3]
18
PHP & MySQL Lab Session E [2/3]
19
// continue from previous slide
else{
/* execute SQL command */
$sql = "SELECT * FROM student WHERE studentusername =
'$username' AND studentpassword = '$password'";
$query = mysql_query($sql) or die("Error: " . mysql_error());
$row = mysql_num_rows($query);
if($row == 0){
echo "Invalid Username/Password. Click here to login.";
}
// continue next slide
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
PHP & MySQL Lab Session E [3/3]
20
// continue from previous slide
else{
$r = mysql_fetch_assoc($query);
$_SESSION['username'] = $r['studentname'];
header("Location: menu.php");
}
}
}
mysql_close($dbconn);?>
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Save as
login0.php
inside phpweb
folder
PHP & MySQL Lab Session F [1/1]
21
Hi, [Logout]
Search
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Save as
menuAdmin.php
inside phpweb
folder
PHP & MySQL Lab Session G [1/1]
22
Hi, [Logout]
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Save as
menu.php
inside phpweb
folder
PHP & MySQL Lab Session H [1/1]
23
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Save as
logout.php
inside phpweb
folder
Testing your code
24
Launch a web browser
Type in: http://localhost:8080/phpweb/login.php
You should see an HTML form
Enter username and password
Hit Login button
If success, you should see Login Success message
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Ok, done with
registration and
login processes
Break
25 PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Next, lets go to
search process
Save as
search.php
inside phpweb
folder
PHP & MySQL Lab Session I [1/5]
26
Enter Student Number
Student Number:
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
PHP & MySQL Lab Session I [2/5]
27
PHP & MySQL Lab Session I [3/5]
28
//continue from previous slide
if($row == 0){
echo "No record found";
}
else{
$r = mysql_fetch_assoc($query);
$studentid = $r['studentid'];
$studentnumber = $r['studentnumber'];
$studentname = $r['studentname'];
$studentpassword = $r['studentpassword'];
?>
//continue next slide
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
PHP & MySQL Lab Session I [4/5]
29
//continue from previous slide
ID:
Save as
search0.php
inside phpweb
folder
PHP & MySQL Lab Session I [1/5]
30
//continue from previous slide
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Ok, done with
registration and
login processes
Break
31 PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Next, lets go to
update and delete
processes
Simply create a
menu page using
Notepad/Dreamwe
aver
PHP & MySQL Lab Session J [1/2]
32
PHP & MySQL Lab Session J [2/2]
33
/* delete process */
if(isset($_POST['Delete'])){
$id = $_POST['id'];
$sql = "DELETE FROM student WHERE studentid = $id";
$query = mysql_query($sql) or die ("Error: " . mysql_error());
echo "Delete success";
}
/* close connection */
mysql_close($dbconn);
?>
PHP Workshop: 29 June 2 July 2010, UiTM Pahang
Save as
process0.php
inside phpweb
folder
These are PHP files that supposedly you have created
during lab session
a) dbconn.php
b) register.php => register0.php
c) login.php => login0.php =>
menuAdmin.php/menu.php => logout.php
d) search.php => search0.php => process0.php
Were going to use these files for next session
Checking
34 PHP Workshop: 29 June 2 July 2010, UiTM Pahang
1. Use mysql_connect() or mysql_pconnect() to
establish database connection
2. Use mysql_select_db() to select database
3. Use mysql_query() to execute SQL statement
4. Use mysql_num_rows() to check data existence
(use with SELECT statement)
5. Use mysql_fetch_assoc() for data retrieval
Checking
35 PHP Workshop: 29 June 2 July 2010, UiTM Pahang
The End
36 PHP Workshop: 29 June 2 July 2010, UiTM Pahang