PHP CRUD with MySQL

Home » PHP & MySQL » PHP » PHP CRUD with MySQL
10 Oct, 2018 Categories: PHP


Database

Create a new MySQL database named demo and execute the SQL code below:

-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Apr 30, 2015 at 09:23 PM
-- Server version: 5.6.17
-- PHP Version: 5.5.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `demo`
--

-- --------------------------------------------------------

--
-- Table structure for table `product`
--

CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `price` decimal(10,0) NOT NULL,
  `quantity` int(11) NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`id`, `name`, `price`, `quantity`, `description`) VALUES
(1, 'Name 1', '1000', 2, 'good'),
(2, 'Name 2', '2000', 3, 'good'),
(3, 'Name 3', '3000', 4, 'good');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;




Database Connection

Create PHP file named connect.php. Use mysqli_connect method connect to demo database with default account:


Username: root
Password:

<?php
	$con = mysqli_connect('localhost', 'root', '', 'demo');
?>

List All Products

Create PHP file named index.php. This file will list all products from the product table and allow to delete the selected product

<?php require 'connect.php'; ?>
<?php
if(isset($_GET['action'])){
	$id = $_GET['id'];
	mysqli_query($con, 'delete from product where id='.$id);
}
?>
<a href="add.php">Add new product</a>
<br>
<table cellpadding="2" cellspacing="2" border="1">
	<tr>
		<th>Id</th>
		<th>Name</th>
		<th>Price</th>
		<th>Option</th>
	</tr>
	<?php
	$result = mysqli_query($con, 'select * from product');
	while($product = mysqli_fetch_object($result)){
		?>
	<tr>
		<td><?php echo $product->id; ?></td>
		<td><?php echo $product->name; ?></td>
		<td><?php echo $product->price; ?></td>
		<td align="center"><a
			href="index.php?id=<?php echo $product->id; ?>&action=delete"
			onclick="return confirm('Are you sure?')">Delete</a> |
			<a href="edit.php?id=<?php echo $product->id; ?>">Edit</a>
		</td>
	</tr>
	<?php } ?>
</table>

Add New Products

Create new php file named add.php. This file use create forms for users to add new products

<?php
require 'connect.php';
if(isset($_POST['submitSave'])){
	$name = $_POST['name'];
	$price = $_POST['price'];
	$quantity = $_POST['quantity'];
	$description = $_POST['description'];
	mysqli_query($con, 'insert into product(name, price, quantity, description) values("'.$name.'",'.$price.','.$quantity.',"'.$description.'")');
	header('Location: index.php');
}
?>
<form method="post">
<table cellpadding="2" cellspacing="2">
	<tr>
		<td>Name</td>
		<td><input type="text" name="name"></td>
	</tr>
	<tr>
		<td>Price</td>
		<td><input type="text" name="price"></td>
	</tr>
	<tr>
		<td>Quantity</td>
		<td><input type="text" name="quantity"></td>
	</tr>
	<tr>
		<td>Description</td>
		<td><textarea rows="5" cols="20" name="description"></textarea></td>
	</tr>
	<tr>
		<td>&nbsp;</td>
		<td><input type="submit" name="submitSave" value="Save"></td>
	</tr>
</table>
</form>




Product Updates

Create new PHP file named add.php. This file use update product information

<?php
require 'connect.php';
if(isset($_GET['id'])){
	$result = mysqli_query($con, 'select * from product where id='.$_GET['id']);
	$product = mysqli_fetch_object($result);
}
if(isset($_POST['submitSave'])){
	$id = $_POST['id'];
	$name = $_POST['name'];
	$price = $_POST['price'];
	$quantity = $_POST['quantity'];
	$description = $_POST['description'];
	mysqli_query($con, 'update product set name="'.$name.'", price='.$price.', quantity='.$quantity.', description="'.$description.'" where id='.$id);
	header('Location: index.php');
}
?>
<form method="post">
<table cellpadding="2" cellspacing="2">
	<tr>
		<td>Id</td>
		<td><?php echo $product->id; ?> <input type="hidden" name="id"
			value="<?php echo $product->id; ?>"></td>
	</tr>
	<tr>
		<td>Name</td>
		<td><input type="text" name="name"
			value="<?php echo $product->name; ?>"></td>
	</tr>
	<tr>
		<td>Price</td>
		<td><input type="text" name="price"
			value="<?php echo $product->price; ?>"></td>
	</tr>
	<tr>
		<td>Quantity</td>
		<td><input type="text" name="quantity"
			value="<?php echo $product->quantity; ?>"></td>
	</tr>
	<tr>
		<td>Description</td>
		<td><textarea rows="5" cols="20" name="description"><?php echo $product->description; ?></textarea></td>
	</tr>
	<tr>
		<td>&nbsp;</td>
		<td><input type="submit" name="submitSave" value="Save"></td>
	</tr>
</table>
</form>




Demo

Product List Page

Add New Product Page

Edit Product Page

References

I recommend you refer to the books below to learn more about the knowledge in this article:


Tags

Related Posts

Top