Ajax with PHP & MYSQL Database :
PHP Ajax :
What is AJAX ?…..
AJAX stands for Asynchronous JavaScript and XML.
AJAX is a new technique for creating better, faster, and…
more interactive web applications with the help of XML, HTML, CSS and Java Script.
Conventional web application trasmit information to and from the sever using synchronous requests.
This means you fill out a form,
hit submit,and get directed to a new page with new information from the server.
With AJAX when submit is pressed, JavaScript will make a request to the server,
interpret the results and update the current screen. In the purest sense,
the user would never know that anything was even transmitted to the server.
FIRST CREATE TABLE details :
create table details
(id int,
name varchar(200),
city varchar(200));
Ajax example code for inserting record from form to MYSQL Database :
(1)first write code for insert.html file:
<html>
<head>
function ajax_post()
{
// Create our XMLHttpRequest object
var hr = new XMLHttpRequest();
// Create some variables we need to send to our PHP file
var url = “insert.php”;
var id = document.getElementById(“id”).value;
var nm = document.getElementById(“name”).value;
var cy = document.getElementById(“city”).value;
var vars = “id=”+id+”&name=”+nm+”&city=”+cy;
hr.open(“POST”, url, true);
// Set content type header information for sending url encoded variables in the request
hr.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
// Access the onreadystatechange event for the XMLHttpRequest object
hr.onreadystatechange = function() {
if(hr.readyState == 4 && hr.status == 200) {
var return_data = hr.responseText;
document.getElementById(“status”).innerHTML = return_data;
}
}
// Send the data to PHP now… and wait for response to update the status div
hr.send(vars); // Actually execute the request
document.getElementById(“status”).innerHTML = “processing…”;
}
</head>
<body>
<input type=”text” id=”id” name=”id”>
<input type=”text” id=”name” name=”name”>
<input type=”text” id=”city” name=”city”>
<input name=”myBtn” type=”submit” value=”Submit Data” onclick=”ajax_post();”>
</body>
</html>
(2)Then write code for insert.php :
<?php
$id=$_POST[‘id’];
$nm=$_POST[‘name’];
$cy=$_POST[‘city’];
$con=mysql_connect(‘localhost’,’root’,” );
mysql_select_db(‘needa’,$con);
$sql=”insert into details (id,name,city) values(‘$id’,’$nm’,’$cy’)”;
mysql_query($sql);
echo “record inserted successfully”;
?>
Ajax example code to update record from form to MYSQL Database :
(1) write code for update.html :
<html>
<head>
function ajax_post()
{
// Create our XMLHttpRequest object
var hr = new XMLHttpRequest();
// Create some variables we need to send to our PHP file
var url = “update.php”;
var id = document.getElementById(“id”).value;
var nm = document.getElementById(“name”).value;
var cy = document.getElementById(“city”).value;
var vars = “id=”+id+”&name=”+nm+”&city=”+cy;
hr.open(“POST”, url, true);
// Set content type header information for sending url encoded variables in the request
hr.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
// Access the onreadystatechange event for the XMLHttpRequest object
hr.onreadystatechange = function() {
if(hr.readyState == 4 && hr.status == 200) {
var return_data = hr.responseText;
document.getElementById(“status”).innerHTML = return_data;
}
}
// Send the data to PHP now… and wait for response to update the status div
hr.send(vars); // Actually execute the request
document.getElementById(“status”).innerHTML = “processing…”;
}
</head>
<body>
<input type=”text” id=”id” name=”id”>
<input type=”text” id=”name” name=”name”>
<input type=”text” id=”city” name=”city”>
<input name=”myBtn” type=”submit” value=”Submit Data” onclick=”ajax_post();”>
</body>
</html>
(2)write code for update.php :
<?php
$id=$_POST[‘id’];
$nm=$_POST[‘name’];
$cy=$_POST[‘city’];
$con=mysql_connect(‘localhost’,’root’,” );
mysql_select_db(‘needa’,$con);
$sql=”update details set name =’$nm’, city=’$cy’ where id=’$id'”;
mysql_query($sql);
echo “record updated successfully”;
?>
Ajax example code to search record from MYSQL Database :
(1)write code search.html :
<html>
<head>
function ajax_post()
{
// Create our XMLHttpRequest object
var hr = new XMLHttpRequest();
// Create some variables we need to send to our PHP file
var url = “search.php”;
var nm = document.getElementById(“name”).value;
var vars = “name=”+nm;
hr.open(“POST”, url, true);
// Set content type header information for sending url encoded variables in the request
hr.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
// Access the onreadystatechange event for the XMLHttpRequest object
hr.onreadystatechange = function() {
if(hr.readyState == 4 && hr.status == 200) {
var return_data = hr.responseText;
document.getElementById(“status”).innerHTML = return_data;
}
}
// Send the data to PHP now… and wait for response to update the status div
hr.send(vars); // Actually execute the request
document.getElementById(“status”).innerHTML = “processing…”;
}
</head>
<body>
<input type=”text” id=”name” name=”name”>
<input name=”myBtn” type=”submit” value=”Submit Data” onclick=”ajax_post();”>
</body>
</html>
(2)write code for search.php:
<?php
$nm=$_POST[‘name’];
$con=mysql_connect(‘localhost’,’root’,” );
mysql_select_db(‘needa’,$con);
$sql=”select * from details where name=’$nm'”;
$result=mysql_query($sql);
while($row=mysql_fetch_array($result))
{
echo $row[‘id’];
echo $row[‘name’];
echo $row[‘city’];
}
?>
Ajax example code to Delete record from MYSQL Database :
(1)write code for delete.html :
<html>
<head>
function ajax_post()
{
// Create our XMLHttpRequest object
var hr = new XMLHttpRequest();
// Create some variables we need to send to our PHP file
var url = “delete.php”;
var id = document.getElementById(“id”).value;
var vars = “id=”+id;
hr.open(“POST”, url, true);
// Set content type header information for sending url encoded variables in the request
hr.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
// Access the onreadystatechange event for the XMLHttpRequest object
hr.onreadystatechange = function() {
if(hr.readyState == 4 && hr.status == 200) {
var return_data = hr.responseText;
document.getElementById(“status”).innerHTML = return_data;
}
}
// Send the data to PHP now… and wait for response to update the status div
hr.send(vars); // Actually execute the request
document.getElementById(“status”).innerHTML = “processing…”;
}
</head>
<body>
<input type=”text” id=”id” name=”id”>
<input name=”myBtn” type=”submit” value=”Submit Data” onclick=”ajax_post();”>
</body>
</html>
(2) write code for delete.php :
<?php
$id=$_POST[‘id’];
$con=mysql_connect(‘localhost’,’root’,” );
mysql_select_db(‘needa’,$con);
$sql=”delete from details where id=’$id'”;
mysql_query($sql);
echo “record deleted successfully”;
?>
Script For YES _NO _CONFIRMATION_ IN_ Ajax With PHP :
(1)FIRST WRITE javascript CODE in <head> section </head> :
<html>
<head>
function doDelete(id){
if(confirm(“Do you want to delete the record?”)){
$.ajax({
url:’delete.php’,
type:’post’,
data:’id=’+id,
success:function(msg){
alert(msg);
window.location.href=’pagination2.php’;
}
});
}
}
</head>
<!—Second use following line where you want to call doDelete() method in <body> section </body>—>
<body>
<?php
include(“connect.php”);
$per_page = 8;
$pages_query = mysql_query(“SELECT COUNT(‘id’) FROM notes order by coursename,semester ASC”);
$pages = ceil(mysql_result($pages_query, 0) / $per_page);
$page = (isset($_GET[‘page’])) ? (int)$_GET[‘page’] : 1;
$start = ($page – 1) * $per_page;
$query = mysql_query(“SELECT * FROM notes LIMIT $start, $per_page”);
while($row = mysql_fetch_assoc($query))
{
echo “record one”;
?>
<a class=”click-more” href=”#” onClick=“doDelete(‘<?php echo $row[‘id’]; ?>’);”>read more</a>
<?php
$prev = $page – 1;
$next = $page + 1;
if(!($page<=1)){
echo “<a href=’pagination1.php?page=$prev’><button>Prev</button></a> “;
}
if($pages>=1 && $page<=$pages){
for($x=1;$x<=$pages;$x++){
echo ($x == $page) ? ‘<strong><a href=”?page=’.$x.'”><button>’.$x.'</button></a></strong> ‘ : ‘<a href=”?page=’.$x.'”><button>’.$x.'</button></a> ‘;
}
}
if(!($page>=$pages)){
echo “<a href=’pagination1.php?page=$next’><button>Next>></button></a>”;
}
?>
</body>
</html>
(2)write code for “delete.php” file:
<?php
$id=$_POST[‘id’];
include(“connect.php”);
$sql=”delete from notes where id=’$id'”;
mysql_query($sql) or die(mysql_error());
echo “Record deleted successfully”;
echo ‘<br></br>’;
echo ‘<a href=”pagination1.php”><button>back to previous page</button></a>’;
?>
============================================================================================================================================================================================================
Now see below example for dynamic drop down & Onchange & Onclick event with Ajax:
First of all Write code for filterdownload.php :
function ajax_post(){
// Create our XMLHttpRequest object
var hr = new XMLHttpRequest();
// Create some variables we need to send to our PHP file
var url = “displaynotes1previous.php”;
var fn = document.getElementById(“coursename”).value;
var en = document.getElementById(“semseter”).value;
var vars = “coursename=”+fn+”&semseter=”+en;
hr.open(“POST”, url, true);
// Set content type header information for sending url encoded variables in the request
hr.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
// Access the onreadystatechange event for the XMLHttpRequest object
hr.onreadystatechange = function() {
if(hr.readyState == 4 && hr.status == 200) {
var return_data = hr.responseText;
document.getElementById(“status”).innerHTML = return_data;
}
}
// Send the data to PHP now… and wait for response to update the status div
hr.send(vars); // Actually execute the request
document.getElementById(“status”).innerHTML = “processing…”;
}
function ajax_disp(){
// Create our XMLHttpRequest object
var hr = new XMLHttpRequest();
// Create some variables we need to send to our PHP file
var url = “displaynotes1.php”;
var fn = document.getElementById(“coursename”).value;
var en = document.getElementById(“semseter”).value;
var pn = document.getElementById(“subject”).value;
var vars = “coursename=”+fn+”&semseter=”+en+”&subject=”+pn;
hr.open(“POST”, url, true);
// Set content type header information for sending url encoded variables in the request
hr.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
// Access the onreadystatechange event for the XMLHttpRequest object
hr.onreadystatechange = function() {
if(hr.readyState == 4 && hr.status == 200) {
var return_data = hr.responseText;
document.getElementById(“status”).innerHTML = return_data;
}
}
// Send the data to PHP now… and wait for response to update the status div
hr.send(vars); // Actually execute the request
document.getElementById(“status”).innerHTML = “processing…”;
}
<span style=”color:#000;”>Select Course Name : -</span>
<select name=coursename id=”coursename” >
<?php
$con = mysql_connect(‘localhost’, ‘root’, ”);
mysql_select_db(“itexam”, $con);
$sql=”select * from subject group by coursename “;
$result=mysql_query($sql) or die(mysql_error());
while ($row=mysql_fetch_array($result))
{
echo ‘<option value=”‘.$row[‘coursename’].'”>’;
echo $row[‘coursename’];
echo ‘<option>’;
}
?>
</select>
<span style=”color:#000;”> Select Semester : -</span>
<select name=semseter id=”semseter” onChange=”ajax_post();”>
<?php
$con=mysql_connect(“localhost”,”root”,””);
mysql_select_db(“itexam”,$con);
$sql=”select * from subject group by semseter “;
$result=mysql_query($sql) or die(mysql_error());
while ($row=mysql_fetch_array($result))
{
echo ‘<option value=”‘.$row[‘semseter’].'”>’;
echo $row[‘semseter’];
echo ‘<option>’;
}
?>
</select>
<!——>
<br /><br />
<br /><br />
<input align=”middle” name=”myBtn” type=”submit” value=”Submit Data” onclick=”ajax_disp();”>
<br />
<?php
ob_flush();
?>
<br /><br />
</div>
</center>
(2)now write code for displaynotes1previous.php:
<html>
<head>
</head>
<body>
<?php
$coursename=$_POST[‘coursename’];
//echo $coursename;
$semseter=$_POST[‘semseter’];
//echo $semseter;
?>
Subject <select name=”subject” id=”subject”>
<?php
$con = mysql_connect(‘localhost’, ‘root’, ”);
mysql_select_db(“itexam”, $con);
$sql=”select subject from subject where coursename=’$coursename’ AND semseter=’$semseter'”;
$result=mysql_query($sql) or die(mysql_error());
while ($row=mysql_fetch_array($result))
{
echo ‘<option value=”‘.$row[‘subject’].'”>’;
echo $row[‘subject’];
echo ‘<option>’;
}
?>
</select>
</td></tr>
<tr>
<td>
</tr>
</body>
</html>
(3)now write code for displaynotes1.php:
<?php
ob_start();
?>
<?php
$coursename=$_POST[‘coursename’];
//echo $coursename;
$semseter=$_POST[‘semseter’];
//echo $semseter;
?>
Subject <select name=”subject” id=”subject”>
<?php
$con=mysql_connect(“localhost”,”root”,””);
mysql_select_db(“itexam”,$con);
$sql=”select subject from subject where coursename=’$coursename’ AND semseter=’$semseter'”;
$result=mysql_query($sql) or die(mysql_error());
while ($row=mysql_fetch_array($result))
{
echo ‘<option value=”‘.$row[‘subject’].'”>’;
echo $row[‘subject’];
echo ‘<option>’;
}
?>
</select>
<?php
$coursename=$_POST[‘coursename’];
$semester=$_POST[‘semseter’];
$subject=$_POST[‘subject’];
echo ‘<h3>’;
echo ‘<table border=0>’;
echo ‘<tr> <td>’;
echo “CourseName =”.$coursename;
echo ‘</td> <td> </td>’;
echo ‘<td>’;
echo “Semester =”.$semester;
echo ‘</td></tr>’;
echo ‘</table>’;
echo ‘</h3>’;
//$con=mysql_connect(“localhost”,”root”,””);
$con=mysql_connect(“localhost”,”root”,””);
mysql_select_db(“itexam”,$con);
//mysql_select_db(“itexam”,$con);
$sql=”select * from notes where coursename=’$coursename’ AND semester=’$semester’ AND subject=’$subject’ order by subject”;
$result=mysql_query($sql) or die(mysql_error());
echo ‘<h4>’;
echo ‘<table border=0>’;
echo ‘<tr><td><h2>Subject Name </h2></td><td> </td><td><h2>Unit </h2></td><td> </td> <td> <h2>Download Link</h2></td></tr>’;
while($row=mysql_fetch_array($result))
{
echo ‘ <tr><td>”‘.$row[‘subject’].'” </td><td> </td><td>”‘.$row[‘unit’].'” </td><td> </td>
<td>
<button ><a href=../proudct_images/’.$row[‘file’].’>
Download file</a></button> </td></tr>’;
}
echo ‘</table>’;
?>
<?php
ob_flush();
?>
======================================================================================================
JQUERY WITH AJAX & MYSQL
======================================================================================================
HTML File – refreshform.html
- Consists of form with id = “form”.
<!DOCTYPE html>
<html>
<head>
<title>Submit Form Without Refreshing Page</title>
<script src=”http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js“></script>
<link href=”css/refreshform.css“ rel=”stylesheet“>
<script src=”js/refreshform.js“></script>
</head>
<body>
<div id=”mainform“>
<h2>Submit Form Without Refreshing Page</h2>
<!– Required Div Starts Here –>
<form id=”form“ name=”form“>
<h3>Fill Your Information!</h3>
<label>Name:</label>
<input id=”name“ placeholder=”Your Name“ type=”text“>
<label>Email:</label>
<input id=”email“ placeholder=”Your Email“ type=”text“>
<label>Contact No.</label>
<input id=”contact“ placeholder=”Your Mobile No.“ type=”text“>
<label>Gender:</label>
<input name=”gender“ type=”radio“ value=”male“>Male
<input name=”gender“ type=”radio“ value=”female“>Female
<label>Message:</label>
<textarea id=”msg“ placeholder=”Your message..“>
</textarea>
<input id=”submit“ type=”button“ value=”Submit“>
</form>
</div>
</body>
</html>
jQuery File – refreshform.js
- Sends request to php script with form details. Return notification on successful data submission.
$(document).ready(function() {
$(“#submit”).click(function() {
var name = $(“#name”).val();
var email = $(“#email”).val();
var contact = $(“#contact”).val();
var gender = $(“input[type=radio]:checked”).val();
var msg = $(“#msg”).val();
if (name == ” || email == ” || contact == ” || gender == ” || msg == ”) {
alert(“Insertion Failed Some Fields are Blank….!!”);
} else {
// Returns successful data submission message when the entered information is stored in database.
$.post(“refreshform.php”, {
name1: name,
email1: email,
contact1: contact,
gender1: gender,
msg1: msg
}, function(data) {
alert(data);
$(‘#form’)[0].reset(); // To reset form fields
});
}
});
});
My-SQL Code
- My-SQL command for creation of database ‘mydba’ and table ‘form_elements’.
CREATE DATABASE mydba;
CREATE TABLE form_element (
id int(25) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
email varchar(255) NOT NULL,
contact int(25) NOT NULL,
gender varchar(255) NOT NULL,
message varchar(255) NOT NULL,
PRIMARY KEY (id)
)
PHP File – refreshform.php
- Insert form information into database.
<?php
// Establishing connection with server by passing “server_name”, “user_id”, “password”.
$connection = mysql_connect(“localhost”, “root”, “”);
// Selecting Database by passing “database_name” and above connection variable.
$db = mysql_select_db(“mydba”, $connection);
$name2=$_POST[‘name1’]; // Fetching Values from URL
$email2=$_POST[’email1′];
$contact2=$_POST[‘contact1’];
$gender2=$_POST[‘gender1’];
$msg2=$_POST[‘msg1’];
$query = mysql_query(“insert into form_element(name, email, contact, gender, message) values (‘$name2′,’$email2′,’$contact2′,’$gender2′,’$msg2’)”); //Insert query
if($query){
echo “Data Submitted succesfully”;
}
mysql_close($connection); // Connection Closed.
?>
Css File – refreshform.css
- Includes basic styling of form.
@import “http://fonts.googleapis.com/css?family=Fauna+One|Muli”;
#form{
background-color:#556b2f;
color:#D5FFFA;
border-radius:5px;
border:3px solid #d3cd3d;
padding:4px 30px;
font-weight:700;
width:350px;
font-size:12px;
float:left;
height:auto;
margin-left:35px
}
label{
font-size:15px
}
h3{
text-align:center;
font-size:21px
}
div#mainform{
width:960px;
margin:50px auto;
font-family:‘Fauna One’,serif
}
input[type=text]{
width:100%;
height:40px;
margin-top:10px;
border:none;
border-radius:3px;
padding:5px
}
textarea{
width:100%;
height:60px;
margin-top:10px;
border:none;
border-radius:3px;
padding:5px;
resize:none
}
input[type=radio]{
margin:10px 5px 5px
}
input[type=button]{
width:100%;
height:40px;
margin:35px 0 30px;
background-color:#f4a460;
border:1px solid #fff;
border-radius:3px;
font-family:‘Fauna One’,serif;
font-weight:700;
font-size:18px
}
=====================================================================
same example in different way:
=====================================================================
HTML File: ajaxsubmit.html
Here, we create our form
<!DOCTYPE html>
<html>
<head>
<title>Submit Form Using AJAX and jQuery</title>
http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js
<link href=”style.css” rel=”stylesheet”>
</head>
<body>
Submit Form Using AJAX and jQuery
Fill Your Information !
Name :
Email :
Password :
Contact No :
</div>
</div>
</body>
</html>
PHP File: ajaxsubmit.php
<?php
$connection = mysql_connect(“localhost”, “root”, “”); // Establishing Connection with Server..
$db = mysql_select_db(“mydba”, $connection); // Selecting Database
//Fetching Values from URL
$name2=$_POST[‘name1′];
$email2=$_POST[’email1′];
$password2=$_POST[‘password1’];
$contact2=$_POST[‘contact1’];
//Insert query
$query = mysql_query(“insert into form_element(name, email, password, contact) values (‘$name2’, ‘$email2’, ‘$password2′,’$contact2’)”);
echo “Form Submitted Succesfully”;
mysql_close($connection); // Connection Closed
?>
jQuery File: script.js
jQuery file consist of ajax functionality.
$(document).ready(function(){
$(“#submit”).click(function(){
var name = $(“#name”).val();
var email = $(“#email”).val();
var password = $(“#password”).val();
var contact = $(“#contact”).val();
// Returns successful data submission message when the entered information is stored in database.
var dataString = ‘name1=’+ name + ‘&email1=’+ email + ‘&password1=’+ password + ‘&contact1=’+ contact;
if(name==”||email==”||password==”||contact==”)
{
alert(“Please Fill All Fields”);
}
else
{
// AJAX Code To Submit Form.
$.ajax({
type: “POST”,
url: “ajaxsubmit.php”,
data: dataString,
cache: false,
success: function(result){
alert(result);
}
});
}
return false;
});
});
MY-SQL Code Segment:
Here is the My-SQL code for creating database and table.
CREATE DATABASE mydba;
CREATE TABLE form_element(
id int(10) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
email varchar(255) NOT NULL,
password varchar(255) NOT NULL,
contact varchar(255) NOT NULL,
PRIMARY KEY (id)
)
CSS File: style.css
@import “http://fonts.googleapis.com/css?family=Fauna+One|Muli”;
#form {
background-color:#fff;
color:#123456;
box-shadow:0 1px 1px 1px #123456;
font-weight:400;
width:350px;
margin:50px 250px 0 35px;
float:left;
height:500px
}
#form div {
padding:10px 0 0 30px
}
h3 {
margin-top:0;
color:#fff;
background-color:#3C599B;
text-align:center;
width:100%;
height:50px;
padding-top:30px
}
#mainform {
width:960px;
margin:50px auto;
padding-top:20px;
font-family:‘Fauna One’,serif
}
input {
width:90%;
height:30px;
margin-top:10px;
border-radius:3px;
padding:2px;
box-shadow:0 1px 1px 0 #123456
}
input[type=button] {
background-color:#3C599B;
border:1px solid #fff;
font-family:‘Fauna One’,serif;
font-weight:700;
font-size:18px;
color:#fff
}
ouput:
