Internet Programming

Chapter Five part II

Data Base Manipulation
using PHP

1 Prepared by Sudi.M 12/17/2025

 Introduction to MySQL

MySQL is the most popular open source database

server.
Database is an organized collection of data.
A database defines a structure for storing
information.
Database stores data in tables.
Tables are composed of rows, and rows are
composed of columns in which values are
stored(cells).
A row of a table represents a record, and a column
of a table represents the value of a single attribute
of the record.
2 Prepared by Sudi.M 12/17/2025
 Database Tables
A database most often contains one or more tables.
Each table has a name (e.g. "Customers"
or"Orders").
Below is an example of a table called "Persons":
First Last Age
Name Name
Abebe Yonas 33
Belachewu Kedir 56
Hana Temesgen 23
Each table contains records (rows) with data.
The table above contains three records (one for
each person) and three columns (LastName,
FirstName, age).

3 Prepared by Sudi.M 12/17/2025

 Queries
A query is a question or a request.
With MySQL, we can query a database for
specific information and have a recordset
returned.
Look at the following query:
SELECT LastName FROM Persons
The query above selects all the data in the
LastName column Lastin the Persons table, and
will return a recordset
Name like this:
Yonas
Kedir
Temesgen
4 Prepared by Sudi.M 12/17/2025
 Connecting to a MySQL
Database
 Before you can access and work with data in a database,
you must create a connection to the database.
 In PHP, this is done with the mysql_connect() function.
Syntax
mysql_connect(servername,username,password);
 Parameter Description
servername. Specifies the server to connect to. Default
value is "localhost:3306"
username. Specifies the username to log in with. Default
value is the name of the user that owns the server process
password . Specifies the password to log in with. Default is
""
 Note: There are more available parameters, but the ones
listed above are the most important.
5 Prepared by Sudi.M 12/17/2025
 Example
In the following example we store the
connection in a variable ($con) for later use in
the script.
The "die" part will be executed if the connection
fails:
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// some code
?>
6 Prepared by Sudi.M 12/17/2025
 Closing a Connection
The connection will be closed as soon as the script
ends.
To close the connection use the mysql_close()
function.
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// some code
mysql_close($con);
?>
7 Prepared by Sudi.M 12/17/2025
 Create a Database
A database holds one or multiple tables.
The CREATE DATABASE statement is used
to create a database in MySQL.
Syntax
CREATE DATABASE database_name
To get PHP to execute the statement above
we must use the mysql_query() function.
This function is used to send a query or
command to a MySQL connection.

8 Prepared by Sudi.M 12/17/2025

 Example
 In the following example we create a database called
"my_db":
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
if (mysql_query("CREATE DATABASE my_db",$con))
{
echo "Database created";
}
else
{
echo "Error creating database: " . mysql_error();
}
mysql_close($con);
?>

9 Prepared by Sudi.M 12/17/2025

 Create a Table
 The CREATE TABLE statement is used to create a
database table in MySQL.
 Syntax
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
.......
)
 We must add the CREATE TABLE statement to the
mysql_query() function to execute the command.
 Important: A database must be selected before a table
can be created.
 The database is selected with the mysql_select_db()
function.
10 Prepared by Sudi.M 12/17/2025
 Note: When you create a database field of type varchar,
 Example
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
if (mysql_query("CREATE DATABASE my_db",$con)){ //
Create database
echo "Database created";
}
else {
echo "Error creating database: " . mysql_error();
}
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE person ( FirstName varchar(15),
LastName varchar(15), Age int )";
mysql_query($sql,$con);
mysql_close($con);
?>
11 Prepared by Sudi.M 12/17/2025
 MySQL Data Types
Numeric Data Description
Types
int(size) Hold integers only. The
smallint(size) maximum number of
tinyint(size) digits can bespecified
mediumint(size) in the size parameter
bigint(size)
Hold numbers with
decimal(size,d) fractions. The
double(size,d) maximum number of
float(size,d) digits can be specified
in the size parameter.
The maximum number
of digits to the right of
12 Prepared by Sudi.M the decimal is 12/17/2025
specified in the d
 MySQL Data Types…
Textual Description
Data Types
char(size) Holds a fixed length string (can contain letters,
numbers, and special characters). The fixed size
is specified in parenthesis
varchar(si Holds a variable length string (can contain
ze) letters, numbers, and special characters).
The maximum size is specified in parenthesis
tinytext Holds a variable string with a maximum length
of 255 characters
text Holds a variable string with a maximum length
blob of 65535 characters
mediumte Holds a variable string with a maximum length
xt of 16777215 characters
mediumbl
13 ob
Prepared by Sudi.M 12/17/2025
longtext Holds a variable string with a maximum length
 MySQL Data Types…
Date data type Description
date(yyyy-mm-dd) Holds date and/or time
datetime(yyyy-mm-dd
hh:mm:ss)
timestamp(yyyymmddhhm
mss)
time(hh:mm:ss)
Collection Data Description
Types
enum(value1,value ENUM is short for ENUMERATED list.
2,ect) Can store one up to 65535 values
listed within the () brackets.
set SET is similar to ENUM. However,
SET can have up to 64 list items and
can store more than one choice
14 Prepared by Sudi.M 12/17/2025
 Primary Keys and Auto Increment
Fields
Each table should have a primary key field.
A primary key is used to uniquely identify the rows in
a table.
Each primary key value must be unique within the
table.
Furthermore, the primary key field cannot be null.
The primary key field is often an ID number, and is
often used with the AUTO_INCREMENT setting.
 AUTO_INCREMENT - automatically increases the
value of the field by 1 each time a new record is
added.
To ensure that the primary key field cannot be null,
we must add the NOT NULL setting to the field.
15 Prepared by Sudi.M 12/17/2025
 Example
The following example sets the personID
field as the primary key field.
$sql = "CREATE TABLE person
(
personID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(personID),
FirstName varchar(15),
LastName varchar(15),
Age int
)";
mysql_query($sql,$con);
16 Prepared by Sudi.M 12/17/2025
 Insert Data Into a Database
Table
 The INSERT INTO statement is used to add new records to
a database table.
 Syntax
INSERT INTO table_name VALUES (value1, value2,....)
 You can also specify the columns where you want to
insert the data:
INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)
 Note: SQL statements are not case sensitive.
INSERT INTO is the same as insert into.
 To get PHP to execute the statements above we must use
the mysql_query() function.
 This function is used to send a query or command to a
MySQL connection.
17 Prepared by Sudi.M 12/17/2025
 Example
 The following example adds two new records to
the "Person" table:
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
mysql_query("INSERT INTO person (FirstName, LastName, Age)
VALUES ('Peter', 'Griffin', '35')");
mysql_query("INSERT INTO person (FirstName, LastName, Age)
VALUES ('Glenn', 'Quagmire', '33')");
mysql_close($con);
?>
18 Prepared by Sudi.M 12/17/2025
 Insert Data From a Form Into a
Database
 Now we will create an HTML form that can be used to
add new records to the "Person" table.

<html>
<body>
<form action="[Link]" 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>

19 Prepared by Sudi.M 12/17/2025

 Insert Data From a Form Into a
Database…
When a user clicks the submit button in the
HTML form in the example above, the form
data is sent to "[Link]".
The "[Link]" file connects to a
database, and retrieves the values from the
form with the PHP $_POST variables.
Then, the mysql_query() function executes
the INSERT INTO statement, and a new
record will be added to the database
table.3
Below is the code in the "[Link]" page:

20 Prepared by Sudi.M 12/17/2025

 Insert Data From a Form Into a
Database…
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$sql="INSERT INTO person (FirstName, LastName, Age)
VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
mysql_close($con)
?>

21 Prepared by Sudi.M 12/17/2025

 Select Data From a Database
Table
The SELECT statement is used to select
data from a database.
Syntax
SELECT column_name(s) FROM
table_name
To get PHP to execute the statement above
we must use the mysql_query() function.
This function is used to send a query or
command to a MySQL connection.

22 Prepared by Sudi.M 12/17/2025

 Example
 The following example selects all the data stored in the
"Person" table (The * character selects all of the data in the
table):
if (!$con)
{
<?php
$con = mysql_connect("localhost","peter","abc123");
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$result = mysql_query("SELECT * FROM person");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'] . " " . $row['LastName'];
echo "<br />";
}
mysql_close($con);
?>

23 Prepared by Sudi.M 12/17/2025

 Example…
 The example above stores the data returned by the
mysql_query() function in the $result variable.
 Next, we use the mysql_fetch_array() function to return
the first row from the recordset as an array.
 Each subsequent call to mysql_fetch_array() returns the
next row in the recordset.
 The while loop loops through all the records in the
recordset.
 To print the value of each row, we use the PHP $row
variable ($row['FirstName'] and $row['LastName']).
 The output of the code above will be:
Abebe Yonas
Belachewu Kedir
Hana Temesgen
24 Prepared by Sudi.M 12/17/2025
 Display the Result in an HTML
Table
HTML table: <th>Lastname</th>
<?php </tr>";
$con = while($row =
mysql_connect("localhost","pet mysql_fetch_array($result))
er","abc123"); {
if (!$con) echo "<tr>";
{ echo "<td>" . $row['FirstName'] .
die('Could not connect: ' . "</td>";
mysql_error()); echo "<td>" . $row['LastName'] .
} "</td>";
mysql_select_db("my_db", $con); echo "</tr>";
First Last
$result = mysql_query("SELECT * }
Name Name
FROM person"); echo "</table>";
echo "<table border='1'> Abebe Yonas
mysql_close($con);
<tr> ?> Belachewu Kedir
<th>Firstname</th> Hana Temesgen
25 Prepared by Sudi.M 12/17/2025
 The WHERE clause
 To select data that matches a specific criteria, add a
WHERE clause to the SELECT statement.
 Syntax
SELECT column FROM table
WHERE column operator value
 The following operators can be used with the WHERE
clause: Operator Description
= Equal
!= Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive
range
26 Prepared by Sudi.M 12/17/2025
LKE Search for a pattern
 The WHERE clause…
Note: SQL statements are not case
sensitive.
WHERE is the same as where.
To get PHP to execute the statement we
must use the mysql_query() function.
This function is used to send a query or
command to a MySQL connection.

27 Prepared by Sudi.M 12/17/2025

 Example
 The following example will select all rows from the "Person"
table, where FirstName='Peter':
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$result = mysql_query("SELECT * FROM person WHERE
FirstName='Peter'");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'] . " " . $row['LastName'];
echo "<br />";
}
?>

28 Prepared by Sudi.M 12/17/2025

 The ORDER BY Keyword
The ORDER BY keyword is used to sort the
data in a recordset.
Syntax
SELECT column_name(s)
FROM table_name
ORDER BY column_name

29 Prepared by Sudi.M 12/17/2025

 Example
 The following example selects all the data stored in the
"Person" table, and sorts the result by the "Age" column:
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$result = mysql_query("SELECT * FROM person ORDER BY
age");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'];
echo " " . $row['LastName'];
echo " " . $row['Age'];
echo "<br />";
}
mysql_close($con);
?>
30 Prepared by Sudi.M 12/17/2025
 Sort Ascending or Descending
If you use the ORDER BY keyword, the sort-
order of the recordset is ascending by
default (1 before 9 and "a" before "p").
Use the DESC keyword to specify a
descending sort-order (9 before 1 and "p"
before "a"):
SELECT column_name(s)
FROM table_name
ORDER BY column_name DESC

31 Prepared by Sudi.M 12/17/2025

 Order by Two Columns
It is possible to order by more than one
column.
When ordering by more than one column,
the second column is only used if the
values in the first column are identical:
SELECT column_name(s)
FROM table_name
ORDER BY column_name1,
column_name2

32 Prepared by Sudi.M 12/17/2025

 Update Data In a Database
The UPDATE statement is used to modify
data in a database table.
Syntax
UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value
To get PHP to execute the statement above
we must use the mysql_query() function.
This function is used to send a query or
command to a MySQL connection.

33 Prepared by Sudi.M 12/17/2025

 Example
 The following example updates some data in the
"Person" table:
<?php
$con = mysql_connect("localhost","peter","abc123");
First Last Age
if (!$con) Name Name
{ Abebe Yonas 36
die('Could not connect: ' . mysql_error());
Belache Kedir 56
} wu
mysql_select_db("my_db", $con); Hana Temesg 23
mysql_query("UPDATE Person SET Age = en'36'
WHERE FirstName = ‘Abebe' AND LastName =
‘Yonas'");
mysql_close($con);
?>
34 Prepared by Sudi.M 12/17/2025
 Delete Data In a Database
The DELETE FROM statement is used to
delete records from a database table.
Syntax
DELETE FROM table_name
WHERE column_name = some_value
To get PHP to execute the statement above
we must use the mysql_query() function.
This function is used to send a query or
command to a MySQL connection.

35 Prepared by Sudi.M 12/17/2025

 Example
The following example deletes all the records in
the "Person" table where LastName=‘Yonas':
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{ First Last Age
Name Name
die('Could not connect: ' . mysql_error());
} Belache Kedir 56
wu
mysql_select_db("my_db", $con);
Hana Temesg 23
mysql_query("DELETE FROM Person WHERE
en
LastName=‘Yonas'");
mysql_close($con);
?>

36 Prepared by Sudi.M 12/17/2025

37 Prepared by Sudi.M 12/17/2025