In this post I’m going to show you how to insert records into MySQL database using jQuery Ajax and PHP, also going to show how to show records from a MySQL table.
This code is extremely easy and without using any regular HTML form and page refresh.
Better see the Demo first by clicking the Demo link at the bottom of the page. Also, you can download the script and use it in your project!
Table Structure:
CREATE TABLE IF NOT EXISTS `mytable` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `email` varchar(150) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
HTML and jQuery part (index.html):
<!DOCTYPE html>
<html>
<head>
<title>Insert and Show Records using jQuery Ajax and PHP</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script>
$(function(){
//insert record
$('#insert').click(function(){
var jname = $('#fname').val();
var jemail = $('#femail').val();
//syntax - $.post('filename', {data}, function(response){});
$.post('data.php',{action: "insert", name:jname, email:jemail},function(res){
$('#result').html(res);
});
});
//show records
$('#show').click(function(){
$.post('data.php',{action: "show"},function(res){
$('#result').html(res);
});
});
});
</script>
</head>
<body>
<h2>Insert Records into MySQL using Ajax and PHP Tutorial - <a href="https://blog.theonlytutorials.com">blog.theonlytutorials.com</a></h2>
Name: <input type="text" id="fname" />
Email: <input type="text" id="femail" />
<button id="insert">Insert</button>
<h2>Show Last 10 Records</h2>
<button id="show">Show</button>
<p>Result:</p>
<div id="result"></div>
</body>
</html>
PHP part (data.php):
<?php
$mysqli = new mysqli("localhost","dbname","dbpass","tablename");
//if insert key is pressed then do insertion
if($_POST['action'] == 'insert'){
$name = mysqli_real_escape_string($mysqli, $_POST['name']);
$email = mysqli_real_escape_string($mysqli, $_POST['email']);
if ( $name != '' && $email != '' ) {
$sql = "insert into demo_mytable (name, email) values ('$name', '$email')";
$query = $mysqli -> query($sql);
if($query){
echo "Record Inserted.";
}else {
echo "Something Wrong!";
}
} else {
echo "Please enter name & email";
}
}
//if insert key is pressed show records
if($_POST['action'] == 'show'){
$sql = "select * from demo_mytable order by id desc limit 10";
$query = $mysqli -> query($sql);
echo "<table border='1'>";
while($row = $query -> fetch_assoc()){
echo "<tr><td>$row[id]</td><td>$row[name]</td><td>$row[email]</td></tr>";
}
echo "</table>";
}
?>
Demo & Download links:
thanks for the code. can you have an example that will serialize a whole form so that no need to code each field in the form?
Might want to add “mysql_close();” (or even “mysql_close($con);” to be more specific) at line 31 to prevent multiple connections remaining idle and slowing down the server if you get a large spike of traffic.
As per this documentation. This is usually not necessary, connection will be closed automatically at the end of the script execution. https://www.php.net/manual/en/function.mysql-close.php
Thanks ….Nice explanation..
Hello,
Nice code, just what i was looking for 🙂
Just an issue here, i’m working with Coldfusion framework, so i have adapted the code. When i click on insrt button, the page data.cfm is displayed, it didn’t stay on my index.cfm page.
Any idea ?
Have you tried cflocation tag?
Nice example, I like it. My problem is that I wish to insert a record from my computer to remote MySQL DB/table; I’m owner of Db and I owe all necessary credentials but it seems that JSONP isn’t so smart and mighty as many people likes to mention in their articles. Do you have solution for that? Thanks.