Introduction
In PHP, data can be inserted into a MySQL database using an INSERT query. When database operations are used repeatedly, creating a custom function can make the code easier to manage and reuse. This article explains how to create a custom PHP function for inserting data into a MySQL table and how to use it with an HTML form to insert product details.
Prerequisites
Before proceeding with this example, the following basic requirements are needed:
- Basic knowledge of PHP
- Basic understanding of HTML forms
- Basic knowledge of MySQL
- PHP and MySQL environment
- A MySQL database
Implementation
Step 1: Create a Basic HTML Page with Form
Create a basic HTML form to enter the product category, product name, and product price.
<form method="POST" action="" id="addproduct-form"> <label for="hd-desc">Select Category</label> <select name="cat_id" id="cat-id"> <option value="" selected="">Select Category</option> <option value="8">mobile</option> <option value="9">Tv</option> <option value="10">XYZ</option> <option value="11">mobile2</option> <option value="12">Tv2</option> <option value="13">Tv 3</option> </select> <label for="hd-desc">Product Name</label> <input id="product-name" name="product_name" placeholder="Enter Category" value="" type="text"> <label for="hd-desc">Product Price</label> <input id="product-price" name="product_price" placeholder="Enter Price" value="" type="text"> <!-- <input type="submit" name="save"> --> <button type="submit" name="save" class="btn btn-success" value="save" id="add-save">Save</button> </form>
Step 2: Create PHP Connection String, Query Insert Function and Form Submit Action
Create the PHP database connection and custom insert function.
<?php
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PSSWD", "");
define("DB_NAME", "test");
$conn = mysql_connect(DB_HOST,DB_USER,DB_PSSWD);
mysql_select_db(DB_NAME, $conn);
function qry_insert($table, $data)
{
$fields = array_keys($data);
$values = array_map("mysql_real_escape_string", array_values($data));
return mysql_query("INSERT INTO $table(".implode(",", $fields).") VALUES ('".implode("','", $values)."');") or die(mysql_error());
}
if(isset($_POST['save']))
{
$data = array(
'cat_id' => $_POST['cat_id'],
'pname' => $_POST['product_name'],
'price' => $_POST['product_price']
);
$qry = qry_insert('product',$data);
}
?>
Step 3: Create the Product Table
Create a table named product using the following code.
CREATE TABLE `product` ( `pid` int(11) NOT NULL AUTO_INCREMENT, `pname` varchar(100) NOT NULL, `price` int(11) NOT NULL, `pimg` varchar(100) NOT NULL, `cat_id` int(11) NOT NULL, PRIMARY KEY (`pid`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
Step 4: Create the Final Code
If you following above step you get final code like this.
<?php
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PSSWD", "");
define("DB_NAME", "karthik");
$conn = mysql_connect(DB_HOST,DB_USER,DB_PSSWD);
mysql_select_db(DB_NAME, $conn);
function qry_insert($table, $data)
{
$fields = array_keys($data);
$values = array_map("mysql_real_escape_string", array_values($data));
return mysql_query("INSERT INTO $table(".implode(",", $fields).") VALUES ('".implode("','", $values)."');") or die(mysql_error());
}
if(isset($_POST['save']))
{
$data = array(
'cat_id' => $_POST['cat_id'],
'pname' => $_POST['product_name'],
'price' => $_POST['product_price']
);
$qry = qry_insert('product',$data);
}
?>
HTML
<!DOCTYPE html> <html> <head> <title>Insert function</title> </head> <body> <form method="POST" action="" id="addproduct-form"> <label for="hd-desc">Select Category</label> <select name="cat_id" id="cat-id"> <option value="" selected="">Select Category</option> <option value="8">mobile</option> <option value="9">Tv</option> <option value="10">XYZ</option> <option value="11">mobile2</option> <option value="12">Tv2</option> <option value="13">Tv 3</option> </select> <label for="hd-desc">Product Name</label> <input id="product-name" name="product_name" placeholder="Enter Category" value="" type="text"> <label for="hd-desc">Product Price</label> <input id="product-price" name="product_price" placeholder="Enter Price" value="" type="text"> <!-- <input type="submit" name="save"> --> <button type="submit" name="save" class="btn btn-success" value="save" id="add-save">Save</button> </form> </body> </html>
How It Works
The HTML form collects the category, product name, and product price.
When the Save button is clicked, the submitted values are stored in the $data array. The qry_insert() function receives the table name and data array and creates the INSERT query.
The data is then inserted into the product table using mysql_query().
Conclusion
In this example, a custom PHP function is used to insert product details into a MySQL database. The function accepts the table name and data as parameters, allowing the insert query to be created using the provided values.
Frequently Asked Questions
What is the purpose of the qry_insert() function?
The qry_insert() function is a custom function used to insert data into a database table.
Which table is used in this example?
The example uses the product table.
What product details are inserted?
The category ID, product name, and product price are inserted into the table.
How is the insert function called?
The function is called using:
$qry = qry_insert('product',$data);
Related Articles
How to Install Apache, PHP, and MySQL on Red Hat 7?
How to install MySQL 8.0 on CentOS 7
Talk to Our Technology Experts
Planning your AWS infrastructure or looking to improve an existing cloud environment? Our team can help with cloud architecture, infrastructure, DevOps, security, deployment, and ongoing optimization.