Scribd
Upload
23 views8 pages

Database Examples

Uploaded by

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

Database Examples

Uploaded by

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

We can create database in php using

 MySQLi (object-oriented)
 MySQLi (procedural)
 PDO(php data object)

A database consists of one or more tables.

You will need special CREATE privileges to create or to delete a MySQL


database.

Create table in php:

Step 1) create a database through script

<?php
if(isset($_POST)) {
$servername = "localhost";
$username = "root";
$password = "";
//$dbname = "clg";
$conn = new mysqli($servername, $username, $password);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "CREATE DATABASE clg";


if ($conn->query($sql) === TRUE) {
echo "Database created successfully";
} else {
echo "Error creating database: " . $conn->error;
}
$conn->close();
}
?>
Step 2) Create a table “staff”

<?php
{
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "clg";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "CREATE TABLE staff (


id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50)
)";
if ($conn->query($sql) === TRUE) {
echo "Table staff created successfully";
} else {
echo "Error creating table: " . $conn->error;
}
$conn->close();
}
?>

Note: Auto increment is used with the INT data type. The INT data type supports both
signed and unsigned values. Unsigned data types can only contain positive
numbers. As a best practice, it is recommended to define the unsigned constraint on
the auto increment primary key.

Output:
Step 3) insert values into table “staff”

<?php
{
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "clg";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO staff (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";

if ($conn->query($sql) === TRUE) {


echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
}
?>

Output:
Step4)

Select data from staff

<?php
{
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "clg";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM staff";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"].
"<br>";
}
} else {
echo "0 results";
}
$conn->close();
}
?>
Note: The fetch_assoc() / mysqli_fetch_assoc() function fetches a result row as
an associative array.

Output:

Step5) update data from staff table.

<?php
{
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "clg";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "UPDATE staff SET lastname='technology' WHERE id=1";

if ($conn->query($sql) === TRUE) {


echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}

$conn->close();
}
?>

Output:
Step 6) delete data from table.

<?php
{
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "clg";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "DELETE FROM staff WHERE id=1";


if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}

$conn->close();
}
?>

Output:

You might also like