Retrieving Data from a MySQL Database using Ajax

I am using MOdX Evo and am using some PHP to retrieve sample data from a small contact database in the form of a list of twenty or so records. I want to add a ’Details" link to show additional information and have been recommended to use Ajax.

This I have done (code below) but regardless of which record the ’Details" link is clicked, only the first record’s details is shown under the first record and the page freezes until refreshed.

It would seem to be a simple thing but is proving to be a problem.

Anyone any ideas?

Martin

<?php
$output = ’’;//create variable for holding the output
$sql = $modx->db->query( ’SELECT town, ass_name, email, extra1, id FROM `publicity` WHERE region="durban" LIMIT 0, 1000’);//define the DB query, in this case the table is named ’tblprojects’, add WHERE AND etc. as needed
$resultArray = $modx->db->makeArray( $sql );//put it into an array
foreach($resultArray as $item)//go through each record in the array
{//start looping through the data array
$params[’region’]=$item[’region’];//set the placeholders, the $params sets the name of the placeholder, the $item is the column name from the DB table
$params[’town’]=$item[’town’];
$params[’ass_name’]=$item[’ass_name’];
$params[’email’]=$item[’email’];
$params[’website’]=$item[’website’];
$params[’extra1’]=$item[’extra1’];
$params[’id’]=$item[’id’];

$output.=$modx->parseChunk(’ProjectTpl’, $params, ’[+’, ’+]’);//define the chunk name and process the content, here it’s ProjectTpl which has the placeholders for the data
}//finish looping through the data array
return $output;//now return the output from the processed chunk
?>






<script type="text/javascript">
var url = "GetCustomerData.php?id="; // The server-side script
function handleHttpResponse() {
if (http.readyState == 4) {
if(http.status==200) {
var results=http.responseText;
document.getElementById(’divCustomerInfo’).innerHTML = results;
}
}
}

function requestCustomerInfo() {
var sId = document.getElementById("txtid").value;
http.open("GET", url + escape(sId), true);
http.onreadystatechange = handleHttpResponse;
http.send(null);
}
function getHTTPObject() {
var xmlhttp;

if(window.XMLHttpRequest){
xmlhttp = new XMLHttpRequest();
}
else if (window.ActiveXObject){
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
if (!xmlhttp){
xmlhttp=new ActiveXObject("Msxml2.XMLHTTP");
}

}
return xmlhttp;


}
var http = getHTTPObject(); // We create the HTTP Object
</script>



<table border="0" cellspacing="0" cellpadding="0" width="600" align="center" class="output">
<tbody>
<tr height="20" align="center">
<td width="350">[+ass_name+]</td>
<td>[+extra1+]</td>
</tr>
<tr height="20" align="center">
<td><a href="mailto:[+email+]">[+email+]</a></td>
<td><a href="http://www.[+website+]">[+website+]</a></td>
</tr>
<tr>
<td colspan="2">[+id+] </td>
</tbody>
</table>





<p>Enter customer ID number to retrieve information:</p>
<p>Customer ID: <input type="text" id="txtid" value="[+id+]" /></p>
<p><input type="button" value="Get Customer Info" onclick="requestCustomerInfo()" /></p>
<div id="divCustomerInfo"></div>

I don’t see where you create your $modx-object in your script.

I would create a snippet with your code.
Place its snippet-call on a modx-resource with blank template and call that resource to get your data.

Thanks Bruno.
I am using the code as a snippet call but outputting the results in the site template. However, the problem is AJAX which will show additional data but only for the first record. The problem seems to be an HTML problem.

Alternatively, I try to find a way of passing the id variable to another page to show the information. I am trying to avoid hard coding every ’Details’ page.

I thought this would be easy but apparently not!

If you have any ideas I would be interested . . .

Regards,

Martin

What do you get if you call your server side script directly from your browser?

Your onclick function needs to pass the ID of the desired item on to the AJAX requesting javascript, then it can be passed in the POST to the .php processor and used as the WHERE clause in the query.

Thanks Susan! Sounds like a workable solution but I have however no idea to do this!

Martin

Your tpl has to have a button or link for the user to select the item to get info for. The snippet that generates the form or links will have placeholders for the details displayed in that tpl. One of the placeholders should be the ID of the item. Use that placeholder in the parameter list of the onclick function.

An example of how this works is in the Jot/Notes snippet’s form tpl adaptation for embedding the file browser for uploading/selecting images:

And the BrowseServer javascript function accepts that as an argument and assigns it to a global variable; it could be passed as a parameter to an AJAX function just as well:

Anyone can help me with this?

http://modxcms.com/forums/index.php/topic,57044.new.html#new

kinda the same theme!

I am trying to generate a simple list/detail page combination from a MySQL table. The code below generates a simple ’list’ from a table including a link to the second piece of code that generates the ’detail’ page. The variable [id] is of course passed with the URL and conflicts with the ModX detail page [id]. How do I get around this without generating a static page?

Thanks,

Martin


List page:

<?php
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("kzn_weddings") or die(mysql_error());

$q=mysql_query("select * from publicity where region=’Midlands’ order by id ");
echo "<table>";
while($nt=mysql_fetch_array($q)){
echo "<tr><td><a href=details.php?id=$nt[id]>$nt[ass_name]</a></td><td>$nt[town]</td></tr>";
}
echo "</table>";
?>

Detail page:

<?php
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("kzn_weddings") or die(mysql_error());


$id=$_GET[’id’];
if(!is_numeric($id)){
echo "Data Error";
exit;
}

$q=mysql_query("select * from publicity where id=$id ");
$row=mysql_fetch_object($q);
echo mysql_error();
echo "<table>";
echo "
<tr><td><b>Name</b></td><td>$row->ass_name</td></tr>
<tr><td><b>Region</b></td><td>$row->region</td></tr>
<tr><td><b>Town</b></td><td>$row->town</td></tr>
<tr><td><b>Address</b></td><td>$row->extra1</td></tr>
<tr><td><b>Email</b></td><td>$row->email</td></tr>
";
echo "</table>";
?>