Dec 27 2011

Insert data into MySQL using php, jquery, Ajax

This is a simple example for inserting data into a mysql database using php as the server-sided programming language.. This uses ajax via jquery for inserting the records… For those of you who do not know what jquery is – jquery is a javascript library with a lot of functions which lets u write less code, which is highly efficient .. You can download this latest version at the jquery.com website… So let’s get started…

  • Construct Database:
We Create a Database called zwaroop. Add a new table myTable with fields: ID as INT, Name as VARCHAR(50),    Email as VARCHAR(50).
For those who are using LAMP, WAMP, XAMPP etc.. this is straight forward… Go ahead to your web browser and navigate to http://localhost/phpmyadmin, which is a GUI that allows you to construct your database as quickly as possible.

So far we have.,

Database Name: zwaroop

Table Name: myTable

Field Names: ID, Name, Email

  • Create home.php file
This is our basic html page that the user will be viewing it…
<html>
<head>
<title>zwaroop's home</title></pre>
<!-- include jquery library file-->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
</head>
<body>
<label>ID: </label> <input id="ID" type="text" />
<label>Name: </label> <input id="Name" type="text" />
<label>E-Mail: </label> <input id="Email" type="text" />
<a id="insert" title="Insert Data" href="#">Push into mysql</a>
 <!-- For displaying a message -->

<div id="message"></div>
</body>
</html>
  • Create insert.php file:
Create a page called insert.php, this page include all the server-sided code for inserting data into our mysql database. Assume our front-end html page consists of 3 input boxes that takes the ID, Name and Email… So here we go,
<?php
//Configure and Connect to the Databse
 $con = mysql_connect("localhost","root","");
 if (!$con) {
 die('Could not connect: ' . mysql_error());
 }
 mysql_select_db("zwaroop", $con);
 //Pull data from home.php front-end page
 $id=$_POST['ID'];
 $name=$_POST['Name'];
 $email=$_POST['Email'];
 //Insert Data into mysql
$query=mysql_query("INSERT INTO myTable(ID,Name,Email) VALUES('$id','$name','$email')");
if($query){
echo "Data for $name inserted successfully!";
}
else{ echo "An error occurred!"; }
?>
That’s it, our php code for inserting data into mysql…
  • The Ajax/jquery stuff!!
Now finally, here we are the ajax thing…. we add this code in our home.php file itself, it’s also possible to add a new file, save it as .js and include it in our home.php file…
<html>
<head>
<title>zwaroop's home</title>
<!-- include jquery library file-->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<!-- The ajax/jquery stuff -->
<script type="text/javascript">

$(document).ready(function(){
//Get the input data using the post method when Push into mysql is clicked .. we pull it using the id fields of ID, Name and Email respectively...
$("#insert").click(function(){
//Get values of the input fields and store it into the variables.
var ID=$("#ID").val();
var Name=$("#Name").val();
var Email=$("#Email").val();

//use the $.post() method to call insert.php file.. this is the ajax request
$.post('insert.php', {ID: ID, Name: Name, Email: Email},
function(data){
$("#message").html(data);
$("#message").hide();
$("#message").fadeIn(1500); //Fade in the data given by the insert.php file
});
return false;
});
});
</script>
</head>
<body>
<label>ID: </label> <input id="ID" type="text" />
<label>Name: </label> <input id="Name" type="text" />
<label>E-Mail: </label> <input id="Email" type="text" />
<a id="insert" title="Insert Data" href="#">Push into mysql</a>
 <!-- For displaying a message -->

<div id="message"></div>
</body>
</html>
That’s it guys….. a simple example to insert data into mysql using php, ajax, jquery….

69 comments on “Insert data into MySQL using php, jquery, Ajax”

  1. Hello Brother,

    Your code not working. Please check the code if there is any error.

    I found one error in the line,

    INSERT INTO zwaroop(ID,Name,Email) VALUES(‘$id’,’$name’,’$email’)”

    Table name will be myTable you give zaroop which is database name.

    on the other hand when I click “Push into mysql” button its not working, I change the table name myTable but no data post to the insert.php page.

    Can you please check.

    Reply

  2. hey my apologies for the error… u need to change the table name in both:
    mysql_select_db(“myTable”, $con); and
    $query=mysql_query(“INSERT INTO myTable(ID,Name,Email) VALUES(‘$id’,’$name’,’$email’)”);

    Check whether that works and let me know.. Thanks 🙂

    Reply

  3. Hi,
    Its not working yet. My problem was that, when I click the “Push into mysql” link my given data can not post ot the insert.php file. I can not received data. I mistake some one for post data using jquery.

    I think your mysql_select_db(“myTable”, $con); will be mysql_select_db(“zwaroop”, $con); because of this function select database and your database name is zwaroop.

    Please check and update.

    Thanks.

    Reply

  4. i have done the necessary changes, tested the new code. The problem was on line 15 where the variable name was supposed to be Email rather than email. See whether this works now.
    Thanks 🙂

    Reply

  7. excellent seriously.I am not that much familiar with j query Ajax
    but this tutorial guide me in very good manner.

    Reply

  9. This was a wonderful mini-tutorial thanks! What about validating and security issues – right now anything can go into the db including rogue code

    Reply

    • Are you getting any kind of error message! Also I recommend you to kepp your javascript console open using firebug on firefox or in Google Chrome. See if your console is showing any messages.

      Reply

  13. I done same but empty rows get inserted. I dnt knw what mistake. i kept the same coding changed db name, table name etc.
    plzz help as soon as possible

    Reply

      • Thank you very much for your tutorial. Its working well. But I have one problem is that I can not using radio button or check box. I have to use two radio button into the form. If I select 1st radio button It will take debit amount and when select 2nd radio button it will take credit amount from the same text box. I have tried to pass radio button into my insert.php page but failed. Sir, Please help me.

  33. Great Work!!!!!, Dear this blog solved my problem that i was facing 5-7 days and was so much fed up to quit developing things.
    Thanks..

    Reply

  34. I am new to extjs . this is my code:
    Ext.define(‘MyApp.store.Countries’, {
    extend: ‘Ext.data.Store’,
    model: ‘MyApp.model.Country’,
    autoLoad: true,
    groupField: ‘myGroup’,

    proxy: {
    type: ‘ajax’,
    url: ‘Country’,
    reader: {
    type: ‘json’,
    totalProperty: ‘totalCount’,
    root: ‘countries’,
    successProperty: ‘success’
    }
    }
    });

    here,i just want to know how we store data in json .In array we write directly in store but how in json…
    Please provide me the details in step by step process.

    Reply

  36. Nice tutorial – I wanted to ask a few questions.

    In the line below I added an img src instead of “Push into mysql” that is assigned by the input fields already having data in the input field from a query call.

    your’s
    Push into mysql

    mine

    what I wanted to find out is…if I click the image can I have the php script return a img src based on the query instead of the message?

    so when I click the image above….. I have php do the query and change the image source

    Thanks

    Kurt

    Reply

    • You can ask php to return JSON, and then you can dynamically change the image.
      For Eg.:
      if($query){
      $data = array("success" => true, "image" => "awesome-image");
      echo json_encode($data);
      }
      else{ echo json_encode(array("success" => false)); }

      And in jquery, you can capture it via:
      if(data.success){
      $(this).attr('src', data.image);
      }

      Reply

  37. Swaroop….thanks for the response.

    what would the data function look like using your example?

    “function(data){
    $(“#message”).html(data);
    $(“#message”).hide();
    $(“#message”).fadeIn(1500); //Fade in the data given by the insert.php file
    });
    return false;
    });
    });”

    Thanks,

    Kurt

    Reply

  38. Here is how I am populating the page…each order is it’s own form. I cut everything down to make it simple but I have 53 fields actually getting data from the query per order, which is inserted into each form with table.

    Each $a is the row id from the mysql database

    The initial img scr $status is determined by the if statement to evaluate $e

    “while(list($a,$b,$c,$d,$e) = mysql_fetch_row($result)) {

    echo “”;
    echo “”;
    echo “Id”;
    echo “Year”;
    echo “First Name”;
    echo “Last Name”;
    echo “Actions”;
    echo “”;
    echo “”;
    echo “”;
    echo “”;
    echo “”;

    echo “”;
    if ($e == “C”)
    $status=”images/closebutton.jpg”;
    else
    $status=”images/openbutton.jpg”;

    echo ““;
    echo “”;
    echo “”;
    echo “”;
    echo “”;”

    so basically I want on start up of the page the img src to be what it is based on the mysql call…which I have working. I want to add the functionality that if the user clicks the image source (lets say for example that it is showing openbutton.jpg) that I do a simple sql to update the row id of the database field status_ with a “C” upon completion simple change the image source on the page to show closebutton.jpg

    Thanks,

    Kurt

    Reply

  40. You should consider changing the image using javascript for this kind of situation.
    Unless you want to change images dynamically, use PHP. But since you need to put a simple closebutton.jpg, you can use js to handle this!

    Reply

  41. Swaroop…

    I got it to work using your current function and just echo’ing the ful image source path from php.

    Thanks,

    Kurt

    Reply

  44. You really make it seem so easy with your presentation but I find this topic to be really something which I think I would never understand. It seems too complicated and extremely broad for me. I’m looking forward for your next post, I’ll try to get the hang of it! bkffaagdeffe

    Reply

  45. Nice read, I just passed this onto a friend who was doing some research on that. And he just bought me lunch since I found it for him smile So let me rephrase that Thank you for lunch! Whenever you have an efficient government you have a dictatorship. by Harry S Truman. ffeekkkcggce

    Reply

Leave a reply to anichandran Cancel reply