Advice For Using G's Or Y's Datatable With Mysql?

[paulmo]

I am having difficulty implementing G OR Y's DataTable, populated with dynamic data retrieved from MySQL database (most examples are with static, in page table data, which is easy, but it's not what I'm doing). The docs, and lack of forum support for a beginner, have me frustrated. JSON and XMLHttpRequest are at the heart of what I'm trying to do. Need advice how to get started as Y's forum does not cater to beginners, and G's is all but un-visited. Here is the code I've got so far for Y's DataTable, if it helps. All .js configuration files are included in my page. Thanks in advance for guidance: ajax_user_posts.php proxy page:

<?php$sql = "SELECT name, message FROM user";include_once("dbconnect.php");$result = mysql_query($sql) or die(mysql_error()); $rows = array();while($r = mysql_fetch_assoc($result)) {	$rows[] = $r;}print json_encode($rows);?>

script in app page:

<div id="basic"></div><script type="text/javascript">YAHOO.util.Event.addListener(window, "load", function() {	YAHOO.example.Basic = function() {		var myColumnDefs = [		   {key:"name", sortable:true, resizeable:true},		   //{key:"created", formatter:YAHOO.widget.DataTable.formatDate, sortable:true, sortOptions:{defaultDir:YAHOO.widget.DataTable.CLASS_DESC},resizeable:true},		   {key:"message", sortable:true, resizeable:true}		];var myDataSource = new YAHOO.util.XHRDataSource("ajax_user_posts.php?", {	responseType: YAHOO.util.DataSource.TYPE_JSON,	responseSchema: {		resultsList: "my_db.user",		fields: ["name", "message"]	}});	   		var myDataTable = new YAHOO.widget.DataTable("basic",		myColumnDefs, myDataSource, {caption:"User Posts"});		//c.m. asyncRequest, JSON.parse the result, and loop through it using Lang.substitute to produce the HTML		var callbacks = {	// Successful XHR response handler	success : function (o) {		var messages = [];		// Use the JSON Utility to parse the data returned from the server		try {			messages = YAHOO.lang.JSON.parse(o.responseText);		}		catch (x) {			alert("JSON Parse failed!");			return;		}		// The returned data was parsed into an array of objects.		// Add a P element for each received message		for (var i = 0, len = messages.length; i < len; ++i) {			var m = messages[i];			var p = document.createElement('p');			var message_text = document.createTextNode(					m.name + ' writes "' + m.message + '"');			p.appendChild(message_text);			msg_section.appendChild(p);		}	},	...};// Make the call to the server for JSON dataYAHOO.util.Connect.asyncRequest('GET',"ajax_user_posts.php", callbacks);		return {			oDS: myDataSource,			oDT: myDataTable		};	}();});</script>

[justsomeguy]

You don't really have any debugging code. What happens when you run it? Do you get Javascript or PHP errors? Can you see the request go out with Firebug, and check the response? Add debugging statements so that you can figure out what the code is doing and what data it is using.

[paulmo]

What happens when I run it is that Datatable is rendered on page but "Data Error" appears where data should be. My MySQL query executes so I know that is not the problem. I can see script with Firebug but don't know about seeing request go out. Thanks for help.

You don't really have any debugging code. What happens when you run it? Do you get Javascript or PHP errors? Can you see the request go out with Firebug, and check the response? Add debugging statements so that you can figure out what the code is doing and what data it is using.

[justsomeguy]

On the Net tab you can look at the requests, you should be able to see the request go out to your PHP script and verify that it's returning the correct data and that the data is in the correct format.

[paulmo]

JSG, thanks for Firebug Net feature--cool. Unfortunately I am getting no info relating to the ajax_user_posts.php page, or any JavaScript or XMLHttpRequest, for that matter (only my index.php page, google analytics, sharethis).I did run my ajax_user_posts.php and it looks like I am getting JSON response:

[{"name":"Jo","message":"good\r\n"},{"name":"Jo","message":"good\r\n"},{"name":"Jo","message":"should\r\n"},{"name":"Jo","message":"great\r\n"},

(The \r\n characters are a mystery--perhaps you could shed light.)So it seems that the data is not interacting with the script.Thanks in advance for helping me out here. PM

[justsomeguy]

If you're not seeing the request go out for the page then that's probably a symptom of the problem. You said you were getting a data error, where does that show up? If it's showing a data error without even sending the request out then there might be a problem with the way you're sending the request. In this line:var myDataSource = new YAHOO.util.XHRDataSource("ajax_user_posts.php?", {Should the URL have the question mark at the end of it?

[paulmo]

Tried with/without ?; still doesn't work. Anything else questionable in my script? Thanks, I'm stumped.

[justsomeguy]

It's hard to say without being familiar with Yahoo's library. Is this part actually in the code or did you just add that:

	},	...};

If that's actually in the code, that's going to be an error. It looks like your callbacks object has a success function but not a failure function. There's probably a failure handler you can add to get information about the error.Other than that, you just need to add debugging code to figure out what this thing is doing. Make sure the various functions execute, add output to the YAHOO.example.Basic function, the success callback, etc. I don't know if you need to explicitly tell the data source to load the data or if it does that automatically. The call to send a request seems to be the one using the callbacks, not the data source load if that happens automatically. That callback will write all of the data from the server into a series of <p> elements, which doesn't sound like anything to do with a data table.

[paulmo]

Thanks I'll try without the ... part. I have copied a few parts of this from Yahoo library as there is not one doc that shows how to use their datatable with MySQL. The datatable did render previously, just with "Data Error" where data should have appeared.