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….
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.
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 🙂
hi, I am ines … and I am using your code to solve my problem. but when I run my code, I have no result. please can you help me
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.
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 🙂
That was absolutely great!! thank you so much.
Cheers
super machan…. Thanks a lot ……machan…
excellent seriously.I am not that much familiar with j query Ajax
but this tutorial guide me in very good manner.
glad 2 know that it helped u! 🙂
Thanks dude, helped a lot
This was a wonderful mini-tutorial thanks! What about validating and security issues – right now anything can go into the db including rogue code
For validating, you can just check for blank fields or if that particular name or email exists, and if it exists you can return false in the insert.php and json_encode it! For how JSON works you can check it here: https://zwaroop.wordpress.com/2012/06/09/display-json-data-using-jqueryajax/
empty rows get inserted. dnt knw what mistake. i kept the same coding changed db name, table name and file alone…
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.
thank you bro… 🙂 awesome …. U reduced my work……….
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
I didn’t get any error but 0 values inserted
check whether your mysql hostname,u sername and password are correct!
Thanks!
I tried code own my cms it works.
i have some idea for add and fetch the data into using id key used for edit ,update and delete function in same form using ajax …. help me
ajax with php mysql
how to use ajax frame work for add, edit, fetch data and delete function in same form in php with mysql
use the same form instead of, change your code in the php(edit or delete or update) by passing the appropriate parameters via jquery
please sir i need some examples
how to ajax frame work for that function
Thank you buddy! Awesome tutorial.. 🙂
My pleasure rewedy! 🙂
please send me the tutorial
Hi! Great tutorial! But, what to do I have multiple input fields. Starting the second the tag #insert does not work anymore …
Could you elaborate it a little!
Hello !
I have two problems. First : When I write a firstname like “François” i have “françois”
Second : instead of to push to data, I wouldi prefer and input submit.. But the rows are automatically registred in a loop with click on submit. I don’t understand why.
Thank you
Eric
Rouen (France)
Sorry… you should read “instead of a href, I would prefer form and input submit”
Add a meta tag with http-equiv=”content-type” content=”text/html;charset=utf-8″
This may solve your issue!
gbu , man..
your tutorial is very easy to understand ….
wish you all the best…
thx brow…
yeah! 🙂
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.
Have you added an id attribute to each of your form elements.
its working cool man
@Md.Anisur Rahman: can u share ur html code!
it woul´d be just great to add a live demo of this 😦
Sure.
Will add a demo shortly.
this is working great for me. using it in a facebook app.
🙂
Please tell me, how file will be upload through this, and how file will be handled through this. ?
If u r looking 4 file upload via Ajax, then u need include this plugin http://malsup.com/jquery/form/
it is not possible to upload file in this “Insert data into MySQL using php, jquery, Ajax”?
i want both work in same plugin.
Yes u can.
Just include the jquery.form.js plugin and submit the form using:
$(“#your_form”).ajaxSubmit(function(data){
//Do Something
});
Thank you so much .. you save my time.. 🙂
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..
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.
I have no clue about ExtJS. But if you could state your problem detailed enough, I can try helping you.
Thanks
Thanks for replying.
very nice , working fine for me
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
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);
}
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
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
your blog isn’t allowing for actual html cod eto show up
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!
Swaroop…
I got it to work using your current function and just echo’ing the ful image source path from php.
Thanks,
Kurt
very nice code working well yah , gud job dud
excelent piece of code thanx a lot
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
I have moved my blog to http://swaroopsm.com/blog/. And I will be posting all my blog posts in here.
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
in above code “$(“#message”).html(data);” return value in
can we store it in variable ??
What exactly do you mean by storing in a variable? What data or information would you like to store in a variable?
thanks its working