Contents
INTRODUCTION
This tutorial is adapted from: http://www.w3schools.com/php/php_mysql_intro.asp
STEPS
0) Create subfolder 108.
1) Prepare Database
1.1) Run phpmyadmin
1.2) Take note that by default superuser name is “root” and the password is “root”.
1.3) phpmyadmin page appears in your web browser.
2) Create database.
2.1. Click Database tab.
2.2. Type “mydb”.
2.3. Click Create.
2.4. mydb created. you can see the database name in the database side panel.
2.5. Click mydb, type “mytable”, type “2”, click Go.
2.6. mytable name will appear under mydb in the side pane;.
2.7. click mytable name, click Insert tab. Type the name john and jenny and clik Go.
2.8) Click Browse tab. Check that the new records are shown.
3) Connecting To MySQL Database.
testconnection.php
|
<?php
// Create connection
$con=mysqli_connect("localhost","root","root","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>
|
3.1) testing with wrong database name.
3.2) you will get a warning message “unknown database”.
4) Create Database using PHP Script.
createdatabase.php
|
<?php
$con=mysqli_connect("localhost","root","root");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Create database
$sql="CREATE DATABASE my_db";
if (mysqli_query($con,$sql)) {
echo "Database my_db created successfully";
} else {
echo "Error creating database: " . mysqli_error($con);
}
?>
|
5) Create Table using PHP Script.
createtable.php
|
<?php
$con=mysqli_connect("localhost","root","root","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Create table
$sql="CREATE TABLE Persons(FirstName CHAR(30),LastName CHAR(30),Age INT)";
// Execute query
if (mysqli_query($con,$sql)) {
echo "Table persons created successfully";
} else {
echo "Error creating table: " . mysqli_error($con);
}
?>
|
5.1) Check in phpmyadmin that your operations above have really taken effect.
5.2) You can drop the table and recreate the table using SQL commands via phpmyadmin as well.
CREATE TABLE Persons
(
PID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(PID),
FirstName CHAR(15),
LastName CHAR(15),
Age INT
);
|
5.2) Click my_db path, click SQL tab, type the following SQL commands and click Go.
6) Insert Records using PHP Script.
insertrecords.php
|
<?php
$con=mysqli_connect("localhost","root","root","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
mysqli_query($con,"INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Peter', 'Griffin',35)");
mysqli_query($con,"INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Glenn', 'Quagmire',33)");
mysqli_close($con);
?>
|
7) Create HTML Form and PHP Script to insert date into the database.
7.1) Entry form.
insert.html
|
<html>
<body>
<form action="insert.php" method="post">
Firstname: <input type="text" name="firstname">
Lastname: <input type="text" name="lastname">
Age: <input type="text" name="age">
<input type="submit">
</form>
</body>
</html>
|
7.2) Entry processing.
insert.php
|
<?php
$con=mysqli_connect("localhost","root","root","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// escape variables for security
$firstname = mysqli_real_escape_string($con, $_POST['firstname']);
$lastname = mysqli_real_escape_string($con, $_POST['lastname']);
$age = mysqli_real_escape_string($con, $_POST['age']);
$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('$firstname', '$lastname', '$age')";
if (!mysqli_query($con,$sql)) {
die('Error: ' . mysqli_error($con));
}
echo "1 record added";
mysqli_close($con);
?>
|
7.3. Browse insert.html.
7.4) Enter data. Click Submit Query.
7.5) You should get success message.
8) Extract records from database using PHP Script.
8a) Basic display
displayrecords.php
|
<?php
$con=mysqli_connect("localhost","root","root","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM Persons");
while($row = mysqli_fetch_array($result)) {
echo $row['FirstName'] . " " . $row['LastName'];
echo "<br>";
}
mysqli_close($con);
?>
|
8b) Tabular Format Display.
displaytabulardata.php
|
<?php
$con=mysqli_connect("localhost","root","root","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM Persons");
echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['FirstName'] . "</td>";
echo "<td>" . $row['LastName'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
|
9) Extract selected record from table.
selectrecord.php
|
<?php
$con=mysqli_connect("localhost","root","root","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM Persons
WHERE FirstName='Peter'");
while($row = mysqli_fetch_array($result)) {
echo $row['FirstName'] . " " . $row['LastName'];
echo "<br>";
}
?>
|
10) Sort record in display.
sortrecords.php
|
<?php
$con=mysqli_connect("localhost","root","root","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM Persons ORDER BY age");
while($row = mysqli_fetch_array($result)) {
echo $row['FirstName'];
echo " " . $row['LastName'];
echo " " . $row['Age'];
echo "<br>";
}
mysqli_close($con);
?>
|
11) Update Records in table.
updaterecords.php
|
<?php
$con=mysqli_connect("localhost","root","root","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
mysqli_query($con,"UPDATE Persons SET Age=36
WHERE FirstName='Peter' AND LastName='Griffin'");
mysqli_close($con);
?>
|
12) Delete Record in table.
deleterecord.php
|
<?php
$con=mysqli_connect("localhost","root","root","my_db");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
mysqli_query($con,"DELETE FROM Persons WHERE LastName='Griffin'");
mysqli_close($con);
?>
|
No comments:
Post a Comment