How to use JSON, jQuery and mySQL in PHP
Today, I dabbled for the first time with PHP, JSON, jQuery and mySQL.
I am writing this post for my own reference as well as hoping that it will be useful for others who are trying to do something similar as I was.
If you are too lazy to try out the code yourself, you can access this page to see a demo of what this code does.
The code snippets provided below will help you to accomplish these tasks:
Useful links if you want to learn PHP, JSON, jQuery or mySQL
PHP with mySQL
JSON
jQuery
If you are interested to know where I host my PHP scripts, you can sign up an account here . I have just started using it, but I find Hostgator to be really reliable and the support is fantastic because you can get instant help with their online chat facility. Speak to me if you want to know more about hosting and getting your own domain.
I am writing this post for my own reference as well as hoping that it will be useful for others who are trying to do something similar as I was.
If you are too lazy to try out the code yourself, you can access this page to see a demo of what this code does.
The code snippets provided below will help you to accomplish these tasks:
- Fetch data from a mySQL database and convert it to JSON data (demo_mysqltojson.php)
- Invoke an AJAX call to the PHP script above upon a button click using jQuery that returns JSON data, read and convert the JSON data to array-like records that can be iterated and used. (demo_showjsondata.php)
- Create a JSON array and send that JSON array via an AJAX call to a PHP script (demo_jsontomysql.php) that will add it to a mySQL database
demo_mysqltojson.php
<?
$con = mysql_connect("localhost","demouser","demopassword");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("demodatabase", $con);
$query = mysql_query("SELECT * FROM users");
$items = array();
while($row = mysql_fetch_array($query)){
$items[] = array('userid' => $row['userid'], 'username' => $row['username']);
}
echo json_encode(array('users'=>$items));
?>
demo_showjsondata.php
<html>
<head>
<!-- Code written by Dora Chua http://www.dorachua.com -->
<title>Demo json</title>
<script src="http://code.jquery.com/jquery-latest.js" type="text/javascript"></script>
</head>
<body>
<button id="loaddata">Refresh Data</button>
<div id="data"></div>
User ID:<input type="text" id="txtuserid"><br/>
User Name:<input type="text" id="txtusername"><br/>
<button id="btnAddUser">Add New User Record</button>
<div id="divMsg"></div>
<script>
$(document).ready(function(){
});
$("button#loaddata").click(function(){
$("div#data").html("");
$.getJSON(
"demo_mysqltojson.php",
function(jsondata){
$.each(jsondata.users, function(i,record){
var info = "<hr>" + record.userid + "," + record.username+ "<hr/>";
$(info).appendTo("div#data");
}); //function irecord
});// function jsondata
});//function click
$("button#btnAddUser").click(function(){
var jsonObj = { "users": [ { "userid" : 0, "username" : "Undefined" }] };
jsonObj.users[0].userid = document.getElementById("txtuserid").value;
jsonObj.users[0].username = document.getElementById("txtusername").value;
var dataString = JSON.stringify(jsonObj);
$.post('demo_jsontomysql.php', {data: dataString}, showResult, "text");
});
function showResult(res){
$("div#divMsg").html(res);
}//end showResult
</script>
</body>
</html>
demo_jsondatatomysql.php
<?
header("Content-type: text/plain");
$res = json_decode(stripslashes($_POST['data']), true);
$userid = $res['users'][0]['userid'];
$username = $res['users'][0]['username'];
$con = mysql_connect("localhost","demodatabase","demopassword");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("demodatabase", $con);
$sqlquery = "INSERT INTO users (userid,username) VALUES (" . $userid . ",'" . $username . "')";
$query = mysql_query($sqlquery);
echo "Record added! Click Refresh to see the latest records";
?>
Useful links if you want to learn PHP, JSON, jQuery or mySQL
PHP with mySQL
JSON
jQuery
If you are interested to know where I host my PHP scripts, you can sign up an account here . I have just started using it, but I find Hostgator to be really reliable and the support is fantastic because you can get instant help with their online chat facility. Speak to me if you want to know more about hosting and getting your own domain.
Comments
Post a Comment