Getting Database Values into HTML table? - AJAX/Javascript

  • (2 Pages)
  • +
  • 1
  • 2

22 Replies - 1830 Views - Last Post: 05 April 2013 - 02:28 PM

#1 JJ1717  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 13

Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 06:46 AM

Hello,
New here so apologies if I commit any rule breaks regarding posting.

I have been attempting a task with basic javascript functions with a HTML page that is supposed to fetch values from a already created database and input these values into the table on the page.

Please see this link as an example: http://www.eng.nene..../Ajax/Ajax.html

As you can see there is a table with headers. When the 'Load Database' button is clicked the function is supposed to load the database values into the correct cells. However this does not happen. There is an alert which displays correct information from the database but the values do not get entered into the table? I need to know why this is the case as I have no idea where to go to fix this.

Edit: The alert was displaying my database values but now it is not. No errors displaying in the console.


I am new to Javascript and Ajax coding so I apologise in advance.


Below is the Javascript Functions.

<script language="Javascript">

var xmlHttpReq = false;
var xmlHttpReq2 = false;
var xmlHttpReq3 = false;

function appendRecord (id, carname, fueltype, transmission, enginesize, doors, total, available)
{
	//rowcount ++;
	
	//if (firstname == "") return;
	
	mytable = document.getElementById ("DBTable");
	
	mycurrent_row = document.createElement ("tr");
	mycurrent_row.setAttribute ("id", "DB"+id);
	
	mycurrent_cell = document.createElement ("td");
	currenttext = document.createTextNode (id);
	mycurrent_cell.appendChild (currenttext);
	mycurrent_row.appendChild (mycurrent_cell);

	mycurrent_cell = document.createElement ("td");
	currenttext = document.createTextNode (carname);
	mycurrent_cell.appendChild (currenttext);
	mycurrent_row.appendChild (mycurrent_cell);
	
	mycurrent_cell = document.createElement ("td");
	currenttext = document.createTextNode (fueltype);
	mycurrent_cell.appendChild (currenttext);
	mycurrent_row.appendChild (mycurrent_cell);
	
	mycurrent_cell = document.createElement ("td");
	currenttext = document.createTextNode (transmission);
	mycurrent_cell.appendChild (currenttext);
	mycurrent_row.appendChild (mycurrent_cell);
	
	mycurrent_cell = document.createElement ("td");
	currenttext = document.createTextNode (enginesize);
	mycurrent_cell.appendChild (currenttext);
	mycurrent_row.appendChild (mycurrent_cell);
	
	mycurrent_cell = document.createElement ("td");
	currenttext = document.createTextNode (doors);
	mycurrent_cell.appendChild (currenttext);
	mycurrent_row.appendChild (mycurrent_cell);
	
	mycurrent_cell = document.createElement ("td");
	currenttext = document.createTextNode (total);
	mycurrent_cell.appendChild (currenttext);
	mycurrent_row.appendChild (mycurrent_cell);
	
	mycurrent_cell = document.createElement ("td");
	currenttext = document.createTextNode (available);
	mycurrent_cell.appendChild (currenttext);
	mycurrent_row.appendChild (mycurrent_cell);
	
	mycurrent_cell = document.createElement ("td");
	mycurrent_input = document.createElement ("input");
	mycurrent_input.setAttribute ("type", "button");
	mycurrent_input.setAttribute ("value", "modify");
	mycurrent_input.setAttribute ("onclick", "modifyOrUpdateRecord (" + id + ", this)");
	mycurrent_cell.appendChild (mycurrent_input);
	mycurrent_row.appendChild (mycurrent_cell);
	
	mycurrent_cell = document.createElement ("td");
	mycurrent_input = document.createElement ("input");
	mycurrent_input.setAttribute ("type", "button");
	mycurrent_input.setAttribute ("value", "delete");
	mycurrent_input.setAttribute ("onclick", "deleteRecord (" + id + ")");
	mycurrent_cell.appendChild (mycurrent_input);
	mycurrent_row.appendChild (mycurrent_cell);
	
	mytable.appendChild (mycurrent_row);
}


function loadDatabaseRecordsCallback () 
{
	if (xmlHttpReq.readyState == 4) 
	{            
        alert ("From Server (Load Records):load.php" + xmlHttpReq.responseText);
        
		var record = xmlHttpReq.responseXML.getElementsByTagName('record');
		var s = "";
		for (var i = 0; i < record.length; i ++)
		{
			var rec = record[i];
            	
			var id = rec.getElementsByTagName("ID")[0].firstChild.data;
			var carname = rec.getElementsByTagName("CARNAME")[0].firstChild.data;
			var fueltype = rec.getElementsByTagName("FUELTYPE")[0].firstChild.data;
			var transmission = rec.getElementsByTagName("TRANSMISSION")[0].firstChild.data;
			var enginesize = rec.getElementsByTagName("ENGINESIZE")[0].firstChild.data;
			var doors = rec.getElementsByTagName("DOORS")[0].firstChild.data;
			var total = rec.getElementsByTagName("TOTAL")[0].firstChild.data;
			var available = rec.getElementsByTagName("AVAILBLE")[0].firstChild.data;
			appendRecord (id, carname, fueltype, transmission, enginesize, doors, total, available);
		}
	}
}

function loadDatabaseRecords () 
{
    // Mozilla/Safari
    if (window.XMLHttpRequest) 
    {
        xmlHttpReq = new XMLHttpRequest();
    }
    // IE
    else if (window.ActiveXObject) 
    {
        xmlHttpReq = new ActiveXObject("Microsoft.XMLHTTP");
    }
    
    alert ("To Server (Load Records):load.php");
    
    xmlHttpReq.open('GET', "load.php", true);
    xmlHttpReq.onreadystatechange = loadDatabaseRecordsCallback; 
    xmlHttpReq.send(null);
}




The loaddatabaserecords function calls the load.php file which is below.
The php file is to fetch data from my pre-created database.

<?php
  $link = mysql_connect ("194.81.104.22", "s10406206", "test");
  mysql_select_db ("db10406206");

  $query = "SELECT * from XYZ";
  $result = mysql_query ($query);
  
  header ("Content-Type: application/xml");
  print ("<?xml version=\"1.0\" ?>");
  print "\n<database>\n";
  for ($i = 0; $i < mysql_num_rows ($result); $i ++)
  {        
    $row = mysql_fetch_object ($result);

    print " <record>\n";
    print "  <id>$row->ID</id>\n";
    print "  <carname>$row->CARNAME</carname>\n";
    print "  <fueltype>$row->FUELTYPE</fueltype>\n";
    print "  <transmission>$row->TRANSMISSION</transmission>\n";
    print "  <enginesize>$row->ENGINESIZE</enginesize>\n";
	print "  <doors>$row->DOORS</doors>\n";
    print "  <total>$row->TOTAL</total>\n";
    print "  <available>$row->AVAILABLE</available>\n";
    print " </record>\n";
  }
  print "</database>\n";
  mysql_close ($link);
?>



Finally this is the html table that is present on the main page. (see Link at top of post).

<form name="f1">
    <input value="Load Database" type="button" onclick='Javascript:loadDatabaseRecords()'></p>
  </form>
  <table id="DBTable"  border="2">
    <tr>
      <td width="20">ID</td>
      <td width="100">Car Name</td>
      <td width="100">Fuel Type</td>
      <td width="100">Transmission</td>
      <td width="80">Engine size</td>
	  <td width="20">Doors</td>
	  <td width="20">Total</td>
      <td width="20">Available</td>
     </tr>
     <form name="myform">
     <tr>
      <td><input type="text" name="id"></td>
      <td><input type="text" name="carname"></td>
      <td><input type="text" name="fueltype"></td>
      <td><input type="text" name="transmission"></td>
	  <td><input type="text" name="enginesize"></td>
      <td><input type="text" name="doors"></td>
      <td><input type="text" name="total"></td>
	  <td><input type="text" name="available"></td>
      <td colspan="2"><input type="button" value="add" onclick="Javascript:addNewRecord()"></td>
	  <td colspan="2"><input type="checkbox" value="update" onclick="Javascript:updateRecord()"></td>
	  <td colspan="2"><input type="checkbox" value="delete" onclick="Javascript:deleteRecord()"></td>
     </tr>
     </form>
  </table>




So, I just want to know what's going wrong. I have spent so many hours researching how to get my database values into the html table. The only responses I have had so far is to display the data in other ways etc. However, I need to display the values in the table as there is other functions that will use the information.

Any help would be greatly appreciated.

Note, I am not expecting anyone to do the work for me. I just lack the Javascript knowledge to spot an error with in the system.

Going through console information and testing there are no errors in the system. There must be a code problem if anyone can point me in the right direction?

Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Getting Database Values into HTML table? - AJAX/Javascript

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2884
  • View blog
  • Posts: 9,568
  • Joined: 12-December 12

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 07:53 AM

Well the first thing I would do would be to fix the HTML; you cannot embed a form within a table like that?! The only elements within a table must be table elements (TR, TBODY, ETC.). Use the form as a wrapper for the table.

You don't appear to have a valid DOCTYPE declaration and language="Javascript" is obsolete - use type="text/javascript" (or it can be omitted).

onclick='Javascript:loadDatabaseRecords()'

'Javascript:' is also obsolete.

I know these won't solve your issue straight-away, but the first, the HTML issue, I would definitely fix.

This post has been edited by andrewsw: 05 April 2013 - 07:53 AM

Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2884
  • View blog
  • Posts: 9,568
  • Joined: 12-December 12

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 08:06 AM

You've misspelt AVAILABLE and I suspect that your PHP expressions require curly brackets:

print "  <id>{$row->ID}</id>\n";

although I haven't checked whether this is the case.

for ($i = 0; $i < mysql_num_rows ($result); $i ++)

I wouldn't check the number of rows here; check it once and store the value in a variable.

You are constantly placing an additional space between function-names and their following brackets and, for example, $i and ++. This is perhaps unlikely to cause problems, but I would not encourage this practice.
Was This Post Helpful? 0
  • +
  • -

#4 JJ1717  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 13

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 08:30 AM

Thank you for the replies so far.

I will fix the php that is mentioned.

What exactly is going wrong with the HTML table?

This is obviously poor teaching advice as this was presented to me like it is now...

When the alert was displaying my database values correctly there was no need for the curly brackets. I can implement them though if necessary.

Any idea's of how to solve this problem further?
Was This Post Helpful? 0
  • +
  • -

#5 JJ1717  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 13

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 08:51 AM

I do not know how to edit posts on here.

I have changed the table and wrapped the form around it.

I changed the spelling mistake in php AVAILABLE.

Changed Javascript define to type=

Problem so far still exists but this was to be expected.
Was This Post Helpful? 0
  • +
  • -

#6 JJ1717  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 13

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 09:07 AM

Continuing work on this and have found an error in the console,

Uncaught TypeError: Cannot call method 'getElementsByTagName' of null
loadDatabaseRecordsCallback

Ajax.html:87


Could this be an important issue?

Explanation of the error or why?


many thanks
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2884
  • View blog
  • Posts: 9,568
  • Joined: 12-December 12

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 09:36 AM

Which is line 87?

Your data comes back as responseText, not responseXML. This is probably due to incorrectly formed XML or an incorrect MIME type. Try adding the following before making your request:

xmlHttpReq.overrideMimeType('text/xml');


If you type 'your address/load.php' in the address bar you can see the data returned; use View Source to see the XML. It seems to have dropped the ?xml tag, but I'm not sure if browsers drop this(?) when displaying XML [I don't think they do?].

Use this as your first line:

<?xml version="1.0" encoding="UTF-8" ?>

This post has been edited by andrewsw: 05 April 2013 - 09:38 AM

Was This Post Helpful? 0
  • +
  • -

#8 JJ1717  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 13

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 09:50 AM

var record = xmlHttpReq.responseXML.getElementsByTagName('record');


Is line 87.


Where exactly should I put the?
xmlHttpReq.overrideMimeType('text/xml');
???


When the Load is clicked the alert does return the values now. It just doesn't enter the table cells.

I dropped the xml tag by mistake... it seemed to make a difference however as the database values appeared in the alert? confusing if I must say.

I put the xml tag in the 'first line' according to the source as you mentioned.

Updated link will be live now.

updating.....


<?xml version="1.0" encoding="UTF-8" ?>

Just brings up an error when added to the php file. no matter what line it is on?
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2884
  • View blog
  • Posts: 9,568
  • Joined: 12-December 12

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 09:54 AM

View PostJJ1717, on 05 April 2013 - 04:50 PM, said:

Where exactly should I put the?
xmlHttpReq.overrideMimeType('text/xml');
???

<?xml version="1.0" encoding="UTF-8" ?>

Just brings up an error when added to the php file. no matter what line it is on?

Erm, before the request, as I said. So, before the line:

xmlHttpReq.send(null);

"no matter what line it is on" ?? It needs to be the very first line of the XML file.
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2884
  • View blog
  • Posts: 9,568
  • Joined: 12-December 12

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 10:00 AM

Yes, you still receive that error message as you are attempting to read responseXML but, as I said, the data is in responseText, and responseXML is null.

You are putting a load of enter characters '\n' and spaces

 print " <record>\n";

in the XML. You can probably get away the the enter characters, but there shouldn't be these spaces between tags. There is no need for any of them. Spaces within code and XML are, or can be, problematic and you need to break this habit :)
Was This Post Helpful? 0
  • +
  • -

#11 JJ1717  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 13

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 10:01 AM

Apologies... seems I must of misread your last post.

Updates completed.

response:

Uncaught TypeError: Cannot read property 'firstChild' of undefined - line 94

Line 94 =
var id = rec.getElementsByTagName("ID")[0].firstChild.data;


Sorry, frustration has made my posting complacent.

thanks again for all the help.
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2884
  • View blog
  • Posts: 9,568
  • Joined: 12-December 12

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 10:01 AM

Anyway, because you will be processing the XML, not directly displaying it, all these spaces and enters are redundant.
Was This Post Helpful? 0
  • +
  • -

#13 JJ1717  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 13

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 10:05 AM

View Postandrewsw, on 05 April 2013 - 10:00 AM, said:

 print " <record>\n";

in the XML. You can probably get away the the enter characters, but there shouldn't be these spaces between tags. There is no need for any of them. Spaces within code and XML are, or can be, problematic and you need to break this habit :)/>


Advice noted. Removed as of now.
Was This Post Helpful? 0
  • +
  • -

#14 JJ1717  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 13

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 10:20 AM

Uncaught TypeError: Cannot read property 'firstChild' of undefined

This error repeats on to line 95 if line 94 is removed. I was checking to see if the 'ID' first child was the issue.

Beginning to assume that all of them are undefined.

 var record = xmlHttpReq.responseXML.getElementsByTagName('record');
		var s = "";
		for (var i = 0; i < record.length; i ++)
		{
			var rec = record[i];
            	
			var id = rec.getElementsByTagName("ID")[0].firstChild.data;
			var carname = rec.getElementsByTagName("CARNAME")[0].firstChild.data;
			var fueltype = rec.getElementsByTagName("FUELTYPE")[0].firstChild.data;
			var transmission = rec.getElementsByTagName("TRANSMISSION")[0].firstChild.data;
			var enginesize = rec.getElementsByTagName("ENGINESIZE")[0].firstChild.data;
			var doors = rec.getElementsByTagName("DOORS")[0].firstChild.data;
			var total = rec.getElementsByTagName("TOTAL")[0].firstChild.data;
			var available = rec.getElementsByTagName("AVAILABLE")[0].firstChild.data;
			appendRecord (id, carname, fueltype, transmission, enginesize, doors, total, available);
		}




Is it safe to say the problem is associated with the above code now? or is there a more complex problem?
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2884
  • View blog
  • Posts: 9,568
  • Joined: 12-December 12

Re: Getting Database Values into HTML table? - AJAX/Javascript

Posted 05 April 2013 - 11:02 AM

I don't know how recently you have updated your page, but I receive an error earlier than your recent post indicates, because the data is in responseText, not responseXML, which is null; you are attempting to construct the table-rows based on responseXML (as you should). I still suspect it is because the XML is malformed in some way, either because it is not using the correct MIME-type, or because the heading <?xml etc.. is not showing.

I would also try with:

header ("Content-Type: text/xml");

even though this method is deprecated and it should really be "application/xml".
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2