Scribd
Upload
11 views58 pages

Unit4. PHP

database managment

Uploaded by

mbishlaurien
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
11 views58 pages

Unit4. PHP

database managment

Uploaded by

mbishlaurien
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 58

HYPERTEXT PREPROCESSOR (PHP)

 The PHP Hypertext Preprocessor (PHP) is a programming


language that allows web developers to create dynamic
content that interacts with databases. PHP is basically used
for developing web based software applications.
 What is PHP?
 PHP stands for: Hypertext Preprocessor
 PHP is a server-side scripting language
 PHP scripts are executed on the server
 PHP supports many databases (MySQL, Informix, Oracle,
Sybase, Solid, PostgreSQL, Generic ODBC, etc.)
 PHP files can contain text, HTML tags and scripts
 PHP files are returned to the browser as plain HTML
 PHP files have a file extension of ".php", ".php3", or
".phtml"

Advanced Web Design and Development


3/26/2025 1
By Rutarindwa J.P
What Can PHP Do?
• PHP can generate dynamic page content
• PHP can create, open, read, write, delete, and close files on the
server
• PHP can collect form data
• PHP can add, delete, modify data in your database
• PHP can restrict users to access some pages on your website

 To get access to a web server with PHP support, you can:


 Install Apache (or IIS) on your own server, install PHP, and
MySQL
 Or find a web hosting plan with PHP and MySQL support

Advanced Web Design and Development


3/26/2025 2
By Rutarindwa J.P
Basic PHP Syntax
• PHP code is executed on the server, and the plain HTML
result is sent to the browser.
• A PHP scripting block always starts with <?php and ends with
?>. A PHP scripting block can be placed anywhere in the
document.
• On servers with shorthand support enabled you can start a
scripting block with <? and end with ?>.
• For maximum compatibility, we recommend that you use the
standard form (<?php) rather than the shorthand form.
<?php
?>
• A PHP file normally contains HTML tags, just like an HTML
file, and some PHP scripting code.
Advanced Web Design and Development
3/26/2025 3
By Rutarindwa J.P
Basic PHP Syntax
• <html>
• <body>
• <?php
• echo "Hello World";
• ?>
• </body>
• </html>
• Each code line in PHP must end with a semicolon. The
semicolon is a separator and is used to distinguish one set of
instructions from another.
• There are two basic statements to output text with PHP: echo
and print. In the example above we have used the echo
statement to output the text "Hello World".
• Note: The file must have a .php extension. If the file has a
.html extension, the PHP code will not be executed.

Advanced Web Design and Development


3/26/2025 4
By Rutarindwa J.P
Variables in PHP
 Variables are used for storing values, like text strings,
numbers or arrays.
 When a variable is declared, it can be used over and over
again in your script.
 All variables in PHP start with a $ sign symbol.
 The correct way of declaring a variable in PHP:
 $var_name = value;
 Let's try creating a variable containing a string, and a variable
containing a number:
<?php
$txt="HelloWorld!”;
$x=16;
?>
Advanced Web Design and Development
3/26/2025 5
By Rutarindwa J.P
The Concatenation Operator
 There is only one string operator in PHP. The
concatenation operator (.) is used to put two string values
together.
 To concatenate two string variables together, use the
concatenation operator:
<?php
$txt1="Hello World!";
$txt2="What a nice day!";
echo $txt1 . $txt2;
?>
•
Advanced Web Design and Development
3/26/2025 6
By Rutarindwa J.P
The strlen() function
The strlen() function is used to return the length of a
string.
Let's find the length of a string:
<?php
echo strlen("Hello world!");
?>
The output of the code above will be: 12
 The length of a string is often used in loops or other
functions, when it is important to know when the
string ends. (i.e. in a loop, we would want to stop the
loop after the last character in the string).

Advanced Web Design and Development


3/26/2025 7
By Rutarindwa J.P
The strpos() function
 The strpos() function is used to search for character within a
string.
 If a match is found, this function will return the position of the
first match. If no match is found, it will return FALSE.
 Let's see if we can find the string "world" in our string:
<?php
echo strpos("Hello world!","world");
?>
 The output of the code above will be: 6
 The position of the string "world" in our string is position 6.
 The reason that it is 6 (and not 7), is that the first position in
the string is 0, and not 1.

Advanced Web Design and Development


3/26/2025 8
By Rutarindwa J.P
Conditional Statements in PHP
1. The if...else Statement
Use the if....else statement to execute some code if a condition is
true and another code if a condition is false.
Example:
The following example will output "Have a nice weekend!" if the
current day is Friday, otherwise it will output "Have a nice day!":
<html>
<body>
<?php
$d=date("D");
if ($d=="Fri"){
echo "Have a nice weekend!";
}else{
echo "Have a nice day!";
}
?> </body> </html> Advanced Web Design and Development
3/26/2025 9
By Rutarindwa J.P
Conditional Statements in PHP
2. The if...elseif....else Statement
• The following example will output "Have a nice weekend!" if the current
day is Friday, and "Have a nice Sunday!" if the current day is Sunday.
Otherwise it will output "Have a nice day!":
<html>
<body>
<?php
$d=date("D");
if ($d=="Fri")
echo "Have a nice weekend!";
elseif ($d=="Sun")
echo "Have a nice Sunday!";
else
echo "Have a nice day!";
?>
</body>
</html>

Advanced Web Design and Development


3/26/2025 10
By Rutarindwa J.P
Conditional Statements in PHP
3. The PHP Switch Statement
• Use the switch statement to select one of many blocks of code to be
executed.
<html>
<body>
<?php
switch ($x)
{
case 1:
echo "Number 1"; break;
case 2:
echo "Number 2"; break;
case 3:
echo "Number 3"; break;
default:
echo "No number between 1 and 3";
}
?></body></html>
Advanced Web Design and Development
3/26/2025 11
By Rutarindwa J.P
PHP Loops
4. The while Loop
 The while loop executes a block of code while a condition is true.
 The example below defines a loop that starts with i=1. The loop will
continue to run as long as i is less than, or equal to 5. i will increase by 1
each time the loop runs:
<html>
<body>
<?php
$i=1;
while($i<=8)
{
echo "The number is " . $i . "<br />";
$i++;
}
?>
</body>
</html>

Advanced Web Design and Development


3/26/2025 12
By Rutarindwa J.P
PHP Loops
5.The do...while Statement
• The do...while statement will always execute the block of code once, it will
then check the condition, and repeat the loop while the condition is true.
• The example below defines a loop that starts with i=1. It will then
increment i with 1, and write some output. Then the condition is checked,
and the loop will continue to run as long as i is less than, or equal to 5:
<html>
<body>
<?php
$i=1;
do
{
$i++;
echo "The number is " . $i . "<br />";
}
while ($i<=5); ?>
</body> </html>

Advanced Web Design and Development


3/26/2025 13
By Rutarindwa J.P
PHP Loops
 6. The for Loop
 The for loop is used when you know in advance how many times the
script should run.
 The example below defines a loop that starts with i=1. The loop will
continue to run as long as i is less than, or equal to 5. i will increase by
1 each time the loop runs:
<html>
<body>
<?php
for ($i=1; $i<=5; $i++)
{
echo "The number is " . $i . "<br />";
}
?>
</body>
</html>

Advanced Web Design and Development


3/26/2025 14
By Rutarindwa J.P
PHP Loops
• The foreach Loop
• The foreach loop is used to loop through arrays.
• Syntax:
• foreach ($array as $value)
• {
• code to be executed;
• }
• For every loop iteration, the value of the current array element is assigned
to $value (and the array pointer is moved by one).
• so on the next loop iteration, you'll be looking at the next array value.
•

Advanced Web Design and Development


3/26/2025 15
By Rutarindwa J.P
PHP Loops
• The following example demonstrates a loop that will print
the values of the given array:
<html>
<body>
<?php $x=array("one","two","three");
foreach ($x as $value)
{
echo $value . "<br />";
}
?>
</body>
</html>
Advanced Web Design and Development
3/26/2025 16
By Rutarindwa J.P
PHP Forms and User Input
 The most important thing to notice when dealing with HTML forms and
PHP is that any form element in an HTML page will automatically be
available to your PHP scripts.
 The PHP $_GET and $_POST variables are used to retrieve information
from forms, like user input.
 The example below contains an HTML form with two input fields and a
submit button:
<html>
<body>
<form action="welcome.php" method="post">
Name: <input type="text" name="name"><br>
E-mail: <input type="text" name="email"><br>
<input type="submit">
</form>
</body></html>
Advanced Web Design and Development
3/26/2025 17
By Rutarindwa J.P
PHP Forms and User Input

 When a user fills out the form above and click on the
submit button, the form data is sent to a PHP file, called
"welcome.php":
 "welcome.php" looks like this:
<html>
<body>
Name: <?php echo $_POST["name"]; ?><br>
Email address: <?php echo $_POST["email"]; ?>
</body>
</html>

Advanced Web Design and Development


3/26/2025 18
By Rutarindwa J.P
The $_GET Function
 The built-in $_GET function is used to collect values from a form sent
with method="get".
 Information sent from a form with the GET method is visible to everyone
(it will be displayed in the browser's address bar) and has limits on the
amount of information to send (max. 100 characters).
 Example:
<html>
<body>
<form action="welcome.php" method="get">
Name: <input type="text" name="fname" />
Age: <input type="text" name="age" />
<input type="submit" />
</form>
</body>
</html>
 When the user clicks the "Submit" button, the URL sent to the server could
look something like this:
 http://localhost/welcome.php?fname=turinabo+sam&age=21
Advanced Web Design and Development
3/26/2025 19
By Rutarindwa J.P
The $_GET Function
 The "welcome.php" file can now use the $_GET function to collect
form data (the names of the form fields will automatically be the
keys in the $_GET array):

Names: <?php echo $_GET["fname"]; ?>.<br />


Age: <?php echo $_GET["age"]; ?> years old!

 When using method="get" in HTML forms, all variable names and


values are displayed in the URL.
 Note: This method should not be used when sending passwords or
other sensitive information!
 However, because the variables are displayed in the URL, it is
possible to bookmark the page. This can be useful in some cases.
 Note: The get method is not suitable for large variable values; the
value cannot exceed 100 characters.

Advanced Web Design and Development


3/26/2025 20
By Rutarindwa J.P
The $_POST Function
 The built-in $_POST function is used to collect values from a form sent
with method="post".
 Information sent from a form with the POST method is invisible to others
and has no limits on the amount of information to send.

 Example:
<html>
<body>
<form action="welcome.php" method="post">
Name: <input type="text" name="fname" />
Age: <input type="text" name="age" />
<input type="submit" />
</form>
</body>
</html>
 When the user clicks the "Submit" button, the URL will look like this:
 http://localhost/welcome.php

Advanced Web Design and Development


3/26/2025 21
By Rutarindwa J.P
The $_POST Function
 The "welcome.php" file can now use the $_POST
function to collect form data (the names of the form fields
will automatically be the keys in the $_POST array):

Welcome <?php echo $_POST["fname"]; ?>!<br />


You are <?php echo $_POST["age"]; ?> years old.

 Information sent from a form with the POST method is


invisible to others and has no limits on the amount of
information to send.
 However, because the variables are not displayed in the
URL, it is not possible to bookmark the page.

Advanced Web Design and Development


3/26/2025 22
By Rutarindwa J.P
The PHP $_REQUEST Function
 The PHP built-in $_REQUEST function contains the
contents of both $_GET, $_POST, and $_COOKIE.
 The $_REQUEST function can be used to collect form
data sent with both the GET and POST methods.

 Example:
Welcome <?php echo $_REQUEST["fname"]; ?>!<br />

You are <?php echo $_REQUEST["age"]; ?> years old.

Advanced Web Design and Development


3/26/2025 23
By Rutarindwa J.P
PHP Include Files
 The include (or require) statement takes all the
text/code/markup that exists in the specified file and copies it
into the file that uses the include statement.
 Including files is very useful when you want to include the
same PHP, HTML, or text on multiple pages of a website.
 It is possible to insert the content of one PHP file into another
PHP file (before the server executes it), with the include or
require statement.

Advanced Web Design and Development


3/26/2025 24
By Rutarindwa J.P
PHP Include Files
 Example 1
 Assume we have a standard footer file called "footer.php", that
looks like this:
<?php
echo "<p>Copyright &copy; 1999-" . date("Y") . " W3Schools.com</p>";
?>
To include the footer file in a page, use the include statement:
<html>
<body>
<h1>Welcome to my home page!</h1>
<p>Some text.</p>
<p>Some more text.</p>
<?php include 'footer.php';
?>
</body>
</html>

Advanced Web Design and Development


3/26/2025 25
By Rutarindwa J.P
PHP Include Files
 Example 2
 Assume we have a standard menu file called "menu.php":
<?php
echo '<a href="/default.asp">Home</a> -
<a href="/html/default.asp">HTML Tutorial</a> -
<a href="/css/default.asp">CSS Tutorial</a> -
<a href="/js/default.asp">JavaScript Tutorial</a> -
<a href="default.asp">PHP Tutorial</a>';
?>

Advanced Web Design and Development


3/26/2025 26
By Rutarindwa J.P
PHP Include Files
• All pages in the Web site should use this menu file. Here
is how it can be done (we are using a <div> element so
that the menu easily can be styled with CSS later):
• <html>
<body>
<div class="menu">
<?php include 'menu.php';?>
</div>
<h1>Welcome to my home page!</h1>
<p>Some text.</p>
<p>Some more text.</p>
</body>
</html>

Advanced Web Design and Development


3/26/2025 27
By Rutarindwa J.P
PHP File Handling
• File handling is an important part of any web application. You
often need to open and process a file for different tasks.
• PHP has several functions for creating, reading, uploading, and
editing files.
• Note: When you are manipulating files you must be very
careful. You can do a lot of damage if you do something
wrong.
• Common errors are: editing the wrong file, filling a hard-drive
with garbage data, and deleting the content of a file by
accident.

Advanced Web Design and Development


3/26/2025 28
By Rutarindwa J.P
PHP Create File - fopen()

 The fopen() function is also used to create a file. Maybe a


little confusing, but in PHP, a file is created using the
same function used to open files.
 If you use fopen() on a file that does not exist, it will
create it, given that the file is opened for writing (w) or
appending (a).
 The example below creates a new file called "testfile.txt".
The file will be created in the same directory where the
PHP code resides:
 Example:
$myfile = fopen("testfile.txt", "w")

Advanced Web Design and Development


3/26/2025 29
By Rutarindwa J.P
PHP Write to File - fwrite()
• The fwrite() function is used to write to a file. The first parameter of
fwrite() contains the name of the file to write to and the second parameter is
the string to be written.
• The example below writes a couple of names into a new file called
"newfile.txt":
<?php
$myfile = fopen("newfile.txt", "w") or die("Unable to open file!");
if(myfile)
{
echo "file well created";
}
$txt = "nshimiyimana Eric\n";
fwrite($myfile, $txt);
$txt = "Gatete Bosco\n";
fwrite($myfile, $txt);
fclose($myfile);
?>

Advanced Web Design and Development


3/26/2025 30
By Rutarindwa J.P
PHP readfile() Function
 The readfile() function reads a file and writes it to the output
buffer.
<?php
echo readfile("newfile.txt");
?>
 If we open the "newfile.txt" file it would look like this:
nshimiyimana Eric
Gatete Bosco

Advanced Web Design and Development


3/26/2025 31
By Rutarindwa J.P
PHP Read Single Line - fgets()
• The fgets() function is used to read a single line from a file.
• The example below outputs the first line of the "newfile.txt"
file:
• <?php
$myfile = fopen("newfile.txt", "r") or die("Unable to open
file!");
echo fgets($myfile);
fclose($myfile);
?>

Advanced Web Design and Development


3/26/2025 32
By Rutarindwa J.P
PHP Check End-Of-File - feof()
 The feof() function checks if the "end-of-file" (EOF) has been reached.
 The feof() function is useful for looping through data of unknown length.
 The example below reads the "webdictionary.txt" file line by line, until
end-of-file is reached:
<?php
$myfile = fopen("newfile.txt", "r") or die("Unable to open file!");
// Output one line until end-of-file
while(!feof($myfile)) {
echo fgets($myfile) . "<br>";
}
fclose($myfile);
?>

Advanced Web Design and Development


3/26/2025 33
By Rutarindwa J.P
PHP Connect to the MySQL Server
 With PHP, you can connect to and manipulate databases. MySQL is
the most popular database system used with PHP.

 What is MySQL?
 MySQL is a database system used on the web
 MySQL is a database system that runs on a server
 MySQL is ideal for both small and large applications
 MySQL is very fast, reliable, and easy to use
 MySQL compiles on a number of platforms
 The data in MySQL is stored in tables. A table is a collection of
related data, and it consists of columns and rows.

• Databases are useful when storing information categorically.


• Use the PHP mysqli_connect() function to open a new connection
to the MySQL server. Before we can access data in a database, we
must open a connection to the MySQL server.

Advanced Web Design and Development


3/26/2025 34
By Rutarindwa J.P
Syntax:

 mysqli_connect(host,usernam
e,password,dbname); Parameter Description
In the following example we
store the connection in a variable
($con) for later use in the script: Optional. Either a host
host
<?php name or an IP address
// Create connection
$con=mysqli_connect("localhost
","root","infosy2013",""); Optional. The MySQL
username
// Check connection user name
if (mysqli_connect_errno()) {
echo "Failed to connect to Optional. The password
MySQL: " . password
to log in with
mysqli_connect_error();
}
?>
Optional. The default
dbname database to be used when
Advanced Web Design and Development
3/26/2025
By Rutarindwa J.P performing queries 35
Close a Connection
 The connection will be closed automatically when the script
ends. To close the connection before, use the mysqli_close()
function:
<?php
// Create connection
$con=mysqli_connect("localhost","root","infosy2013","");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " .
mysqli_connect_error();
} //Close a Connection
mysqli_close($con); ?>

Advanced Web Design and Development


3/26/2025 36
By Rutarindwa J.P
PHP Create Database
• The CREATE DATABASE statement is used to create a database in
MySQL. We must add the CREATE DATABASE statement to the
mysqli_query() function to execute the command.
• The following example creates a database named "auca":
<?php
$con=mysqli_connect("localhost","root","infosy2013");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Create database
$sql="CREATE DATABASE auca";
if (mysqli_query($con,$sql)) {
echo "Database auca created successfully";
} else {
echo "Error creating database: " . mysqli_error($con);
}
?>

Advanced Web Design and Development


3/26/2025 37
By Rutarindwa J.P
Create a Table
 The CREATE TABLE statement is used to create a table in MySQL.
The following example creates a table named "Persons", with three columns:
"FirstName", "LastName" and "Age":
<?php
$con=mysqli_connect("localhost","root","infosy2013","auca");
// 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);
}
?>
Advanced Web Design and Development
3/26/2025 38
By Rutarindwa J.P
Primary Keys and Auto Increment Fields
 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 because the database engine
requires a value to locate the record.
 The following example sets the PID field as the primary key field.
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:

• $sql = "CREATE TABLE Persons


(
ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID),
FirstName CHAR(15),
LastName CHAR(15),
Age INT
)";
Advanced Web Design and Development
3/26/2025 39
By Rutarindwa J.P
Primary Keys and Auto Increment Fields
• Example:  $sql="CREATE TABLE Employee(
 Employee_ID INT NOT NULL
• <?php AUTO_INCREMENT,
• $con=mysqli_connect("localhos  PRIMARY KEY(Employee_ID),
t","root","infosy2013","auca");  FirstName CHAR(30),LastName
• // Check connection CHAR(30),
• if (mysqli_connect_errno()) {  Age INT, Address CHAR(30),
• echo "Failed to connect to  Salary CHAR(30),
MySQL: " .  Position CHAR(30),
mysqli_connect_error();  Performance CHAR(30))";
• }  if (!mysqli_query($con,$sql)) {
 die('Error: ' .
mysqli_error($con));
 }
 mysqli_close($con);
 ?>

Advanced Web Design and Development


3/26/2025 40
By Rutarindwa J.P
Insert Data Into a Database Table
 The INSERT INTO statement is used to add new records to
a database table.
• Syntax:
• It is possible to write the INSERT INTO statement in two
forms. The first form doesn't specify the column names
where the data will be inserted, only their values:
INSERT INTO table_name
VALUES (value1, value2, value3,...)
 The second form specifies both the column names and the
values to be inserted:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
 To get PHP to execute the statements above we must use the
mysqli_query() function. This function is used to send a
query or command to a MySQL connection.

Advanced Web Design and Development


3/26/2025 41
By Rutarindwa J.P
Insert Data Into a Database Table
• Example:
• In the previous chapter we created a table named "Persons", with three
columns; "FirstName", "LastName" and "Age". We will use the same table
in this example. The following example adds two new records to the
"Persons" table:
<?php
$con=mysqli_connect("localhost","root","infosy2013","auca");
// 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 ('Petero', 'John',35)");
mysqli_query($con,"INSERT INTO Persons (FirstName, LastName,
Age)
VALUES ('Gabiro', 'Fred',33)");
mysqli_close($con);
?>
Advanced Web Design and Development
3/26/2025 42
By Rutarindwa J.P
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 "Persons" table.
 Here is the HTML form:
<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>
Advanced Web Design and Development
3/26/2025 43
By Rutarindwa J.P
Insert Data From a Form Into a Database

 When a user clicks the submit button in the HTML form,


in the previous example , the form data is sent to
"insert.php".
 The "insert.php" file connects to a database, and retrieves
the values from the form with the PHP $_POST variables.
 The mysqli_real_escape_string() function escapes special
characters in a string for security against SQL injection.
 Then, the mysqli_query() function executes the INSERT
INTO statement, and a new record will be added to the
"Persons" table.

Advanced Web Design and Development


3/26/2025 44
By Rutarindwa J.P
Insert Data From a Form Into a Database
 Here is the "insert.php" page:
<?php
$con=mysqli_connect("localhost","root","infosy2013","auca");
// 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);?>
Advanced Web Design and Development
3/26/2025 45
By Rutarindwa J.P
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
mysqli_query() function.
• This function is used to send a query or command to a MySQL
connection.

Advanced Web Design and Development


3/26/2025 46
By Rutarindwa J.P
Select Data From a Database Table
Example:
 The following example selects all the data stored in the "Persons"
table (The * character selects all the data in the table):
<?php
$con=mysqli_connect("localhost","root","infosy2013","auca");
// 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']." ".$row['Age'];
echo "<br>";
}
mysqli_close($con);
?>
Advanced Web Design and Development
3/26/2025 47
By Rutarindwa J.P
Display the Result in an HTML Table
 The following example selects the • echo "<table border='1'>
same data as the example above, • <tr>
but will display the data in an • <th>Firstname</th>
HTML table: • <th>Lastname</th>
• <th>Age</th>
• <?php • </tr>";
• $con=mysqli_connect("localhost", • while($row =
"root","infosy2013","auca"); mysqli_fetch_array($result)) {
• echo "<tr>";
• // Check connection
• echo "<td>" . $row['FirstName'] .
• if (mysqli_connect_errno()) { "</td>";
• echo "Failed to connect to • echo "<td>" . $row['LastName'] .
MySQL: " . "</td>";
mysqli_connect_error(); • echo "<td>" . $row['Age'] . "</td>";
• } • echo "</tr>";
• }
• $result =
mysqli_query($con,"SELECT * • echo "</table>";
FROM Persons"); • mysqli_close($con);
• ?>

Advanced Web Design and Development


3/26/2025 48
By Rutarindwa J.P
Update Data In a Database
 The UPDATE statement is used to update existing records in a
table.
 Syntax:
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column= some_value

 The WHERE clause specifies which record or records that


should be updated. If you omit the WHERE clause, all records
will be updated!.
 To get PHP to execute the statement above we must use the
mysqli_query() function. This function is used to send a query
or command to a MySQL connection.

Advanced Web Design and Development


3/26/2025 49
By Rutarindwa J.P
Update Data In a Database
• Example:
• The following example updates some data in the "Persons" table:
• <?php
• $con=mysqli_connect("localhost","root","infosy2013","auca");
• // Check connection
• if (mysqli_connect_errno()) {
• echo "Failed to connect to MySQL: " . mysqli_connect_error();
• }
• mysqli_query($con,"UPDATE Persons SET Age=50,
LastName='nshizirungu'
• WHERE FirstName='Mupenzi' ");
• mysqli_close($con);
• ?>

Advanced Web Design and Development


3/26/2025 50
By Rutarindwa J.P
The WHERE clause
 The WHERE clause is used to extract only those records that
fulfill a specified criterion.

 Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name operator value

 To get PHP to execute the statement above we must use the


mysqli_query() function.
 This function is used to send a query or command to a MySQL
connection.

Advanced Web Design and Development


3/26/2025 51
By Rutarindwa J.P
The WHERE clause
 Example:
 The following example selects all rows from the "Persons" table where
"FirstName='Peter'":
<?php
$con=mysqli_connect("localhost","root","infosy2013","auca");
// 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>";
}
?>
 The output of the code above will be: rutarindwa J.Pierre

Advanced Web Design and Development


3/26/2025 52
By Rutarindwa J.P
Sorting Data in Recordset
 The ORDER BY keyword is used to sort the data in a
recordset.
 The ORDER BY keyword sort the records in ascending order
by default.
 If you want to sort the records in a descending order, you can
use the DESC keyword.
 Syntax
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

Advanced Web Design and Development


3/26/2025 53
By Rutarindwa J.P
Sorting Data in Recordset
Example
The following example selects all the data stored in the "Persons" table, and sorts the
result by the "Age" column:
<?php
$con=mysqli_connect("localhost","root","infosy2013","auca");
// 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);
?>

Advanced Web Design and Development


3/26/2025 54
By Rutarindwa J.P
PHP Date and Time
 The PHP date() function formats a timestamp to a more
readable date and time.
 Syntax:
 date(format,timestamp)
 The required format parameter of the date() function specifies
how to format the date (or time).
 Here are some characters that are commonly used for dates:
 d - Represents the day of the month (01 to 31)
 m - Represents a month (01 to 12)
 Y - Represents a year (in four digits)
 l (lowercase 'L') - Represents the day of the week

Advanced Web Design and Development


3/26/2025 55
By Rutarindwa J.P
PHP Date and Time

The example below formats today's date in


three different ways:
• Example
• <?php
• echo "Today is " . date("Y/m/d") . "<br>";
• echo "Today is " . date("Y.m.d") . "<br>";
• echo "Today is " . date("Y-m-d") . "<br>";
• echo "Today is " . date("l");
• ?>
Advanced Web Design and Development
3/26/2025 56
By Rutarindwa J.P
Automatic Copyright Year
Use the date() function to automatically update
the copyright year on your website:
Example:
&copy; 2010-<?php echo date("Y")?>

Advanced Web Design and Development


3/26/2025 57
By Rutarindwa J.P
Get a Simple Time
 Here are some characters that is commonly used for times:
 h - 12-hour format of an hour with leading zeros (01 to 12)
 i - Minutes with leading zeros (00 to 59)
 s - Seconds with leading zeros (00 to 59)
 a - Lowercase Ante meridiem and Post meridiem (am or pm)

 The example below outputs the current time in the specified


format:
<?php
echo "The time is " . date("h:i:sa");
?>

Advanced Web Design and Development


3/26/2025 58
By Rutarindwa J.P

You might also like