1 min read
Php Mysqli Crude Operations
Crude operations include Read ,Write ,Update and Delete.
In this tutorial you will learn how to write mysqli statements to carry out crude operations on a database.
First create a database and name it crude or with your preffered name:
Create a table users using below sql:
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`firstname` varchar(15) NOT NULL,
`secondname` varchar(15) NOT NULL,
`phone` varchar(13) NOT NULL,
`email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
Create a folder crude in your localhost,inside it create another folder config to store db connection file.
In config folder create a file dbconnection.php and paste in the following code:
<?php
$host = 'localhost';
$user = 'root';
$pass = '';
$db = 'crud';
$con = mysqli_connect($host,$user,$pass,$db) or die('Unable to Connect');
?>
In crude folder create the following files and paste in the code below them respectively:
insert.php
<?php
include_once('config/dbconnect.php');
$sql = "INSERT INTO users (firstname,secondname,phone,email) VALUE 'Joseph','Ongoma','07979529','ogm@mail.com'";
mysqli_query($con,$sql);
?>
read.php
<?php
include_once('config/dbconnect.php');
$sql = "SELECT * FROM users ORDER BY id DESC";
$res = mysqli_query($con,$sql);
while ($rows = mysqli_fetch_assoc($res)) {
echo($rows['id']);
}
?>
update.php
<?php
include_once('config/dbconnect.php');
$sql = "UPDATE users SET firstname = '',secondname = '',phone = '',email = '' WHERE id = 2";
mysqli_query($con,$sql);
?>
delete.php
<?php
include_once('config/dbconnect.php');
$sql = "DELETE FROM users WHERE id = 2";
mysqli_query($con,$sql);
?>
Run the files in your browser to test the code,happy coding:)