What you should already know
- You have basic knowledge about HTML
- You have basic knowledge about PHP
- You have basic knowledge about jQuery
- You have basic knowledge about Mysql
- you have to install or host the jQuery file, you can also user the jQuery CDN (Content delivery Network) if you don\’t want to install or host the jQuery file.
- To practice this tutorial you should have a webserver, text editor and browser. I this tutorial I use XAMPP for webserver, sublime 3 for text editor and Firefox for browser.
Here we are using three file for Insert data in MySql database using Ajax.
Tutorial Content
I am going to tell you in this tutorial How to insert data to MySql from PHP using jQuery AJAX.
Usually, after you click the Submit button on the form, refresh the browser. AJAX can send to a web server without refresh the browser. Even Ajax can access the database in one event. For more information, see the following steps:
The first of all create your database name in phpmyadmin Using XAMPP webserver.
create a database called testing. In the testing database create a table called users.
The users table will take following fields.
Creating the Database Table
The first step in this tutorial is the creation of a MySQL database. you can create tables by running SQL queries. Create a table ‘users’ in database using the following SQL query.
-- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` int(50) NOT NULL PRIMARY KEY AUTO INCREAMENT, `name` varchar(50) NOT NULL, `email` varchar(50) NOT NULL, `password` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- On your XAMPP webserver and create a new folder in XAMPP/htdocs/projectfoldername if you use XAMPP. if you use Linux create it at var/www/.
- Open your text editor software in my case my use sublime 3, Create a project folder inside project folder create a file index.php and save it your project folder.
Create MySql Connection in PHP
For importing and exporting database in MySql will make a separate file ‘database.php’. Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:
database.php
<?php
// create database connectivity
$servername = "localhost";
$username = "root";
$password = "";
$database = "testing";
// Create connection
$con = new mysqli($servername, $username, $password, $database);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
?>- Server name is your web server name if your are using XAMPP than your server name is localhost.
- username is root, password is blank because in my case I have no password and database name is testing.
- If connection is successfully than insert the data into the database table called users.
- Include your database.php connection file in action.php page.
Creating users Form
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to insert data to MySql from PHP using jQuery AJAX</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<h2 style="text-align: center;">How to insert data to MySql from PHP using jQuery AJAX</h2>
<form class="form-horizontal" id="userForm">
<div class="form-group">
<label class="control-label col-sm-2" for="name">Name:</label>
<div class="col-sm-10">
<input type="text" name="name" class="form-control" id="name" placeholder="Enter Name">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="email">Email:</label>
<div class="col-sm-10">
<input type="email" class="form-control" id="email" placeholder="Enter email" name="email">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="pwd">Password:</label>
<div class="col-sm-10">
<input type="password" class="form-control" id="pwd" placeholder="Enter password" name="pwd">
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<button type="submit" class="btn btn-primary" id="submit">Submit</button>
</div>
</div>
</form>
</div>
</body>
</html>
Adding jQuery CDN
- As we know, we need to install or host jQuery to use it. Or we can add jQuery CDN if we don\’t want to install or host it. We also have many CDN options available, either Google CDN or Microsoft CDN. For more information, visit the official JQuery website. I used Google CDN in this tutorial. Add the CDN to the before </body> element.
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
To write the jQuery Script we should open <script> tag. You can put it in after </head> element.
<script type="text/javascript">
$(document).ready(function(){
$("#submit").click(function(e){
e.preventDefault();
var name = $("#name").val();
var email = $("#email").val();
var pwd = $("#pwd").val();
if(name !=="" && email !=="" && pwd !==""){
$.ajax({
url : "action.php",
type: "POST",
cache: false,
data : {name:name,email:email,password:pwd},
success:function(data){
alert("Data inserted successfully");
$("#userForm")[0].reset();
},
});
}else{
alert("All fields are required");
}
});
});
</script> Index.php file work in finish now working on action.php file first of all create database connectivity for insert data into the database.
action.php
<?php
// include database connectivity
include_once('database.php');
// insert data using php mysqli
$name = strip_tags(trim($_POST["name"]));
$name = str_replace(array("r","n"),array(" "," "), $name);
$email = filter_var(trim($_POST["email"]), FILTER_SANITIZE_EMAIL);
$password = trim(md5($_POST['password']));
$query = "INSERT INTO users(name, email ,password) VALUES('$name','$email','$password')";
if ($con->query($query)===true) {
return true;
}else{
return false;
}
?>You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions / problems about this tutorial, please comment on the form below.😊
