MySQL CRUD using PHP

Connect to the database.

<?php
$con = mysqli_connect("localhost", "root", "", "");
if (mysqli_connect_errno())
{
	echo "Failed to connect to MySQL: " . mysqli_connect_error()."<br/>";
}
else
{
	echo "Connected to database successfully!<br/>";
}

Create database “mystore”.

<?php
$con = mysqli_connect("localhost", "root", "", "");
$createDB = "CREATE DATABASE mystore";
if (mysqli_query($con,$createDB))
{
	echo "Database mystore created successfully!<br/>";
}
else
{
	echo "Error creating database: " . mysqli_error($con)."<br/>";
}
mysqli_close($con);

Select the database “mystore”.

<?php
$con = mysqli_connect("localhost", "root", "", "");
mysqli_select_db($con, "mystore");
if ($result = mysqli_query($con, "SELECT DATABASE()")) {
	$row = mysqli_fetch_row($result);
    echo "Default database is ".$row[0]."<br/>";
    mysqli_free_result($result);
}
mysqli_close($con);

Create query to the table customers in the databse “mystore”.

<?php
$con = mysqli_connect("localhost", "root", "", "");
$createCustomersTable = 
"CREATE TABLE customers 
(
PID INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(PID),
FirstName CHAR(15),
LastName CHAR(15),
Email CHAR(50),
Age INT,
created_at datetime
)";
if (mysqli_query($con,$createCustomersTable))
{
	echo "Table customers created successfully!<br/>";
}
else
{
	echo "Error creating table: " . mysqli_error($con)."<br/>";
}
mysqli_close($con);

Insert query to add customers.

<?php
$con = mysqli_connect("localhost", "root", "", "");
$insertCustomers="INSERT INTO customers (`FirstName`, `LastName`, `Email`, `Age`,`created_at`) VALUES 
('Larry', 'Page', 'larry.page@gmail.com', '22',NOW()),
('Steve', 'Jobs', 's.j@apple.com', '33',NOW()),
('Bill', 'Gates', 'b.g@ms.com', '35',NOW())";
if (mysqli_query($con,$insertCustomers))
{
	echo "Customers added successfully!<br/>";
}
else
{
	echo "Error adding customers: " . mysqli_error($con)."<br/>";
}
mysqli_close($con);

Select query to select customers and loop through each customer.

<?php
$con = mysqli_connect("localhost", "root", "", "");
$selectCustomers = "select * from customers";
$customers = mysqli_query($con,$selectCustomers);
while($row = mysqli_fetch_array($customers))
{
    echo $row['PID']."|".$row['FirstName']."|".$row['LastName']."|".$row['Email']."|".$row['Age']."|".$row['created_at']."<br/>";
}
mysqli_close($con);

Update query to update customer email.

<?php
$con = mysqli_connect("localhost", "root", "", "");
$updateCustomers = "update customers set Email='steve.jobs@apple.com' where PID=2";
if(mysqli_query($con,$updateCustomers))
{
	echo "customer updated successfully!<br/>";
}
else
{
	echo "Error updating customers ".mysqli_error($con)."<br/>";
}
mysqli_close($con);

Delete query to delete a customer in the customers table.

<?php
$con = mysqli_connect("localhost", "root", "", "");
$deleteCustomer="delete from customers where PID=3";
if(mysqli_query($con,$deleteCustomer))
{
	echo "customer with PID=3 deleted successfully!<br/>";
}
else
{
	echo "Error deleting customer with PID=3 ".mysqli_error($con)."<br/>";
}
mysqli_close($con);

Search within Codexpedia

Custom Search

Search the entire web

Custom Search