Scribd
Upload
15 views

7. PHP and MySQL

Uploaded by

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

7. PHP and MySQL

Uploaded by

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

PHP AND MYSQL

What is MySQLi? What does it do?

- MySQLi is an extension for PHP, often referred to as


MySQL improved. It was introduced in PHP 5.0, and will
be in every version following (until something better
comes along). It allows you to use all of the MySQL
database (version 4.1.3 or newer) servers features. It’s
object oriented interface, making it easier to use
- Support for prepared statements, helping secure you
code
- Support for multiple statements, allowing you to run
more than one query at a time.
- MySQLi not only has an object oriented interface but
also a procedural one. Making it even more widely
usable.
Connection to MySQL
<?php
$servername = "localhost";
$username = "username";
$password = “root";
$dbname = "myDB";
// Create connection
$conn=mysqli_connect($servername, $username, $password,
$dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
?>
Connection to MySQL

---------------OR -----------------

<?php
// Create connection
$conn = mysqli_connect("localhost", "username", "password",
"myDB"); // Check connection
if (!$conn)
{
die("Connection failed: " .
mysqli_connect_error());
}
?>
Connection to MySQL

Parameter Description
host / servername Specifies a host name or an IP address
username Specifies the MySQL username
password Specifies the MySQL password
dbname Specifies the default database to be used
The mysqli_connect() function opens a new
mysqli_connect connection to the
MySQL server.
The die() function prints a message and exits the
die()
current script.
The mysqli_connect_error() function returns the
mysqli_connect_error(
error description
)
from the last connection error, if any.
Close the Connection

The connection will be closed


automatically when the script ends. To close the
connection before, use mysqli_close(connection
name);

Example:
mysqli_close($conn);
Close the Connection
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn=mysqli_connect($servername, $username, $password,
$dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
mysqli_close($conn);
?>
Insert Data From a Form Into a Database

Let’s take a look on the following Database and


Table

Database Name : MyDbase


Table
FieldsName : Datatype
tblStudent
(size)
IDNumber Varchar(20) PRIMARY KEY
StudName Varchar(50) NOT NULL
Grade Int NOT NULL

CREATE TABLE tblStudent


( IDNumber varchar(20) NOT NULL,
StudName varchar(50) NOT NULL,
Grade int NOT NULL,
PRIMARY KEY (IDNumber)
);
Insert Data From a Form Into a Database

Let’s take a look on the following FORM


(insertstud.php)
<form action = "insertstud.php" method =
"POST">
IDNumber (required) <br>
<input type = "text" name =
"txtIDNumber" /> <br>
StudName (required) <br>
<input type = "text" name =
"txtStudName" /> <br>
Grade (required)<br>
<input type = "number" name = "txtGrade"
/> <br>
<p>
<input type = "submit" name="submit">
<input type = "reset" name="reset">
<p>
</form>
Insert Data From a Form Into a Database

Insert the following php scripts after the </form>


tag.
<?php
//DATABASE CONNECTION

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDbase";
// Create connection
$conn=mysqli_connect($servername, $username, $password,
$dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
Insert Data From a Form Into a Database
CONT……

if (isset($_REQUEST['submit']))
{
extract($_REQUEST);
$IDNumber=mysqli_real_escape_string($conn,
$_POST['txtIDNumber']);
$StudName=mysqli_real_escape_string($conn,
$_POST['txtStudName']);
$Grade = mysqli_real_escape_string($conn,$_POST['txtGrade']);

if (mysqli_query($conn, "insert into tblstudent set


IDNumber='$IDNumber', StudName='$StudName',
Grade='$Grade'"))

{ echo 'Student Successfully Inserted'; }


else
{ echo 'Cannot Insert Student'; }
}
mysqli_close($conn);
Insert Data From a Form Into a Database

mysqli_real_escape_string
- The mysqli_real_escape_string() function escapes special
characters in a string for use in an SQL statement.

Syntax:
mysqli_real_escape_string(connection,escapestring);

connection
- Specifies the MySQL connection to use

escapestring
- The string to be escaped. Characters encoded are NUL
(ASCII 0), \n, \r, \, ', ", and ControlZ.
Insert Data From a Form Into a Database

mysqli_query
The mysqli_query() function performs a query against the
database

Syntax:
mysqli_query(connection,query);

connection
- Specifies the MySQL connection to use

query
- Specifies the query string
Display the Result in an HTML Table
dislaystud.php

<?php
//DATABASE CONNECTION

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDbase";
// Create connection
$conn=mysqli_connect($servername, $username, $password,
$dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
Display the Result in an HTML Table
dislaystud.php cont…

$result = mysqli_query($conn, "SELECT * FROM tblstudent");

echo "<table border='1'>


<tr>
<th>IDNumber</th>
<th>StudName</th>
<th>Grade</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['IDNumber'] . "</td>";
echo "<td>" . $row['StudName'] . "</td>";
echo "<td>" . $row['Grade'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($conn);
?>
Deleting Data from a Database
deletestud.php cont…

<?php
//DATABASE CONNECTION

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDbase";
// Create connection
$conn=mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
Deleting Data from a Database
deletestud.php cont…

if (mysqli_query($conn, "DELETE FROM tblStudent WHERE IDNumber=


'TTR/01/01' "))
{
echo 'Student Successfully Deleted';
}

mysqli_close($conn);

?>
Updating Data from a Database
updatestud.php
cont…
<?php
//DATABASE CONNECTION

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDbase";
// Create connection
$conn=mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
Updating Data from a Database
updatestud.php
cont…

if (mysqli_query($conn, "UPDATE tblStudent SET StudName='Airwind'


WHERE IDNumber='TTR/09/1s0'"))

{
echo 'Student Successfully Updated';
}

mysqli_close($conn);

?>

You might also like