Insert values from database into xml table and output them with ajax to html
I have a search box where the user can enter a name and display "firstname", "username", "lastname", "email", "accountnumber"
. So far, I have managed to get data from the database, create an xml structure (this was one of the requirements at school). The question is, how can I cast the values that come from the search box into an xml table and then output the result to an HTML table?
Code for the database (file called ajax-search.php): (I know I am using mysql and I will fix it later)
<?php
header("Content-type: text/xml");
//Create Database connection
$db = mysql_connect("127.0.0.1","root","");
if (!$db) {
die('Could not connect to db: ' . mysql_error());
}
//Select the Database
mysql_select_db("bank",$db);
$sSearchFor = $_GET['sSearchFor'];
$sql = "SELECT * FROM customers WHERE name LIKE '%$sSearchFor%'";
$result = mysql_query($sql, $db) or die(mysql_error());
//Create SimpleXMLElement object
$xml = new SimpleXMLElement('<xml/>');
//Add each column value a node of the XML object
while($row = mysql_fetch_assoc($result)) {
$mydata = $xml->addChild('mydata');
$mydata->addChild('Id',$row['id']);
$mydata->addChild('Name',$row['name']);
$mydata->addChild('user_name',$row['user_name']);
$mydata->addChild('last_name',$row['last_name']);
$mydata->addChild('email',$row['email']);
$mydata->addChild('account_number',$row['account_number']);
}
//Create the XML file
$fp = fopen("employeeData.xml","a+");
//$fp = fopen("php://output","a+");
//Write the XML nodes
fwrite($fp,$xml->asXML()."\r\n" );
//Close the database connection
fclose($fp);
mysql_close($db);
?>
Code for xml, (the file is called xmltable.xml):
<?xml version="1.0" encoding="utf-8"?>
<searchresults>
<name>test</name>
<username>test</username>
<lastname>test</lastname>
<email>test.test@gmail.com</email>
<accountnumber>93207802685726</accountnumber>
</searchresults>
And the last script for ajax is on the index page:
$("#btnSearch").click(function () {
var sSearchFor = $("#txtSearch").val();
var searchLink = "ajax-search.php?sSearchFor=" + sSearchFor;
$.ajax({
type: "GET",
url: "xmltable.xml",
cache: false,
dataType: "xml",
success: function (xml) {
$(xml).find('searchresults').each(function () {
$(this).find("name").each(function () {
var name = $(this).text();
alert(name);
});
});
}
});
});
I appreciate all the help as I am really getting lost right now.
source to share
Side by side:
You forgot to add searchLink to your url!
$("#btnSearch").click(function () {
var searchLink = "ajax-search.php";
$.ajax({
type: "POST",
url: searchLink,
data: {sSearchFor : $("#txtSearch").val() },
cache: false,
dataType: "json",
success: function (xml) {
$(xml).find('searchresults').find('result').each(function () {
var name = $(this).find("name").text();
alert(name);
});
}
});
});
Server side:
Use this in your .PHP file. I have commented out the lines that are about saving files:
<?php
header("Content-type: text/xml");
//Create Database connection
$db = mysql_connect("127.0.0.1","root","");
if (!$db) {
die('Could not connect to db: ' . mysql_error());
}
//Select the Database
mysql_select_db("bank",$db);
if(isset($_POST['sSearchFor']))
$sSearchFor = $_POST['sSearchFor'];
else
$sSearchFor = "";
$sql = "SELECT * FROM customers WHERE name LIKE '%$sSearchFor%'";
$result = mysql_query($sql, $db) or die(mysql_error());
//Create SimpleXMLElement object
$xml = new SimpleXMLElement('searchresults');
//Add each column value a node of the XML object
while($row = mysql_fetch_assoc($result)) {
$result= $xml->addChild('result');
$result->addChild('id',$row['id']);
$result->addChild('name',$row['name']);
$result->addChild('username',$row['user_name']);
$result->addChild('lastname',$row['last_name']);
$result->addChild('email',$row['email']);
$result->addChild('accountnumber',$row['account_number']);
}
// You can close BD now
mysql_close($db);
//Create the XML file
//$fp = fopen("employeeData.xml","a+");
//$fp = fopen("php://output","a+");
//Write the XML nodes
//fwrite($fp,$xml->asXML()."\r\n" );
//Close file
//fclose($fp);
echo $xml->asXML();
?>
Hope this helps and good luck!
source to share