Help - Fetch Data from MySQL using PHP to populate Table

[SA Rob]

Hi Folks,I have a site which allows me to enter data and the form works wellWhat I would like to do is the following:I have a select box which already has the names of events loaded into it.I have the data in MySQLI have seen an example at this link which is similar to what I would like to do except that the table will have to have enough rows to populate the same information that was provided in the first link.My challenge again is that I can see the twq codes need to be linked somehow and do not know how to go about this.Additionally, the set of the table requires some attention and help.I look forward to any support that may be rendered here.RegardsRob

[birbal]

I am not sure about the problem that page elaborate how to use a ajax function to pull down data from a PHP page where php page is used to pull the data from database. It will do the job as you will told it. It will show all the rows as you want .. it wont care how much long it is and what data it holds.

[SA Rob]

I am not sure about the problem that page elaborate how to use a ajax function to pull down data from a PHP page where php page is used to pull the data from database. It will do the job as you will told it. It will show all the rows as you want .. it wont care how much long it is and what data it holds.

Thanks for the reply.My major problem is that I am not skilled in either AJAX or PHP and thus combining the to bits of code is a challenge.Secondly the code talks about $con . . . I have a query that will be along these lines . . .

$connect = mysql_connect("localhost", "****_r******", "r0********h") ordie ("Hey loser, check your server connection.");mysql_select_db("eghfya_Members"); // need to adjust to your connection file location$quey1 = "SELECT * FROM _Form_Nr_6 ORDER BY i_wish_to_play, my_handicap_is DESC";//$quey1 = "SELECT * FROM _Form_Nr_6 ORDER BY i_wish_to_play, $result = mysql_query($quey1) or die(mysql_error());

Therfore trying to adjust and fit will only make life difficult for me.Your help is appreciated.Regards - Rob

[birbal]

the $con in that page holds the mysql conection resource... as $connect did in your script. the idea is php will generate the page and populate with the data of database. i guess you have a working php page right? you have a populated table like structure in your page right? then you need to make a ajax function to request to that page. that ajax function will catch the page data in responseText (or responseXML) which are used to put again into the HTML DOM to change the page dynamicaly.if you look into that pages it will be more clearer its well elaborated there.

[baligena]

Just a piece of advice. Your better off using mysqli. Its more secure and simple once you study it. check out the same code below

<?php//mysqli,  steps: connection(new mysqli), prepare, execute, bind_results, fetch, close & close$mysqli = new mysqli("server","username","password","database");//connect to databaseif(mysqli_connect_errno()){	echo "Connection Failed: " . mysqli_connect_errno() . '<br /> ';	die();}//prepare statementif($stmt = $mysqli->prepare("SELECT * FROM table")or die('Problem with query <br /> ')){	$stmt->execute();	//bind variables to prepare statement	$stmt->bind_result($a,$b,$c);			//fetch values	while($stmt->fetch())	{		echo $a . $b . $c;	}	$stmt->close();}//close connection$mysqli->close();   //optinal parameter functionfunction optinalParam($p=null){	echo " <br /> I'm in a optinal parameter function "  . $p;}optinalParam('Im and optional parameter');?>

[SA Rob]

Just a piece of advice. Your better off using mysqli. Its more secure and simple once you study it. check out the same code below

<?php//mysqli,  steps: connection(new mysqli), prepare, execute, bind_results, fetch, close & close$mysqli = new mysqli("server","username","password","database");//connect to databaseif(mysqli_connect_errno()){	echo "Connection Failed: " . mysqli_connect_errno() . '<br /> ';	die();}//prepare statementif($stmt = $mysqli->prepare("SELECT * FROM table")or die('Problem with query <br /> ')){	$stmt->execute();	//bind variables to prepare statement	$stmt->bind_result($a,$b,$c);			//fetch values	while($stmt->fetch())	{		echo $a . $b . $c;	}	$stmt->close();}//close connection$mysqli->close();   //optinal parameter functionfunction optinalParam($p=null){	echo " <br /> I'm in a optinal parameter function "  . $p;}optinalParam('Im and optional parameter');?>

Hi,Thanks for the advice.My hosting company currently only offers MySQL and I think I can thus only use this option.I will query it with them to see if this be an option and then perhaps persue it as it is the improved version.

[dsonesuk]

I think what you are looking at is a dropdown list of players fullnames, on selecting the fullname, this would populate hidden or read only input text boxes, name, surname, and division, which is taken from a database which has this data. Then you add the score value and you submit to tournament scores database.In ajax setup, this would be triggered by onchange on the select dropdown listing, this will send this selected value to php file, which would match this to mysql database record and echo the results in input fields, which will then grapped by xmlhttp.responseText; and be displayed under select dropdown in a specific id reference div container .

[SA Rob]

I think what you are looking at is a dropdown list of players fullnames, on selecting the fullname, this would populate hidden or read only input text boxes, name, surname, and division, which is taken from a database which has this data. Then you add the score value and you submit to tournament scores database.In ajax setup, this would be triggered by onchange on the select dropdown listing, this will send this selected value to php file, which would match this to mysql database record and echo the results in input fields, which will then grapped by xmlhttp.responseText; and be displayed under select dropdown in a specific id reference div container .

Hi,Thanks for the reply.You are correct except that I willnot use the players name but th etounament name in the select box.Whenit is selected then th einformation entered will appear in a table for any person to veiw the results of each tournament , individually.It is getting that process to work that is the current challenge.RegardsRob

[dsonesuk]

All you have to do is create page that list tournament in select dropdown, which you have already done. Then create another page, that will list the tournament details you require by tournament name/date, in the layout you require, then it is just the matter of linking the two together, using ajax script.

[SA Rob]

All you have to do is create page that list tournament in select dropdown, which you have already done. Then create another page, that will list the tournament details you require by tournament name/date, in the layout you require, then it is just the matter of linking the two together, using ajax script.

Hi, You make it sound so easy. I appreciate the help.Regards Rob

[SA Rob]

Hi, You make it sound so easy. I appreciate the help.Regards Rob

Ok - so here my attempt at getting this to work.I have created two files and each cvan be veiwed at the following links:Select BoxThe tableThe select box seems to veiw ok but when selction is made nothing happens.If I try veiw the table then the page views blank. When I selct a tournament name then there is activity as the text that is there is over written by the blank page. It is just that the table is not appearing as intended.It would be greast if you could advise.The two files are posted below:SELECT BOX

<html><head><script type="text/javascript">function showUser(str){if (str=="")  {  document.getElementById("txtHint").innerHTML="";  return;  } if (window.XMLHttpRequest)  {// code for IE7+, Firefox, Chrome, Opera, Safari  xmlhttp=new XMLHttpRequest();  }else  {// code for IE6, IE5  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");  }xmlhttp.onreadystatechange=function()  {  if (xmlhttp.readyState==4 && xmlhttp.status==200)	{	document.getElementById("txtHint").innerHTML=xmlhttp.responseText;	}  }xmlhttp.open("GET","MySQLreturntable.php?q="+str,true);xmlhttp.send();}</script></head><body><form><select name="users" onchange="showUser(this.value)"><option value="">Select a tournament:</option><option value="1">Kempton Park Golf Club</option><option value="2">Kyalami Golf Club</option><option value="3">St.Francis Links</option><option value="4">Gardener Ross Golf Estate</option><option value="5">Rustenburg Golf Club</option><option value="6">Witbank Golf Course</option><option value="7">Ebotse Golf Estate</option><option value="8">Umhlali Golf Estate</option><option value="9">Durban Country Club</option><option value="10">Steenburg Golf Club</option><option value="11">Maccauvlei Golf Club</option><option value="12">Mt.Edgecombe Country Club</option><option value="13">Randpark Golf Club</option><option value="14">Oubaai Golf Club</option><option value="15">Zwartkop Country Club</option><option value="16">ERPM Golf Club</option><option value="17">Polokwane Golf Club</option><option value="18">Erivale</option><option value="19">Royal Cape Golf Club</option><option value="20">Paarl Golf Club</option><option value="21">Irene Country Club</option><option value="22">Stellenbosch Golf Club</option><option value="23">Mowbray Golf Club</option><option value="24">Bryanston Golf Club</option><option value="25">Schoeman Park Golf Club</option><option value="26">Nelspruit Golf Club</option><option value="27">The Hill</option><option value="28">Mossel Bay Golf Club</option><option value="29">Wanderers Golf Club</option><option value="30">Pecanwood Golf Club</option></select></form><br /><div id="txtHint"><b>The Results of the Winners will be listed here.</b></div></body></html>

THE TABLE PAGE

<?php$q=$_GET["q"];$con = mysql_connect("localhost", "*****_r*****h", "********") orif (!$con)  {die ("Hey loser, check your server connection.");}mysql_select_db("eghfya_Members", $con);$sql="SELECT * FROM _Form_Nr_6 WHERE id = '".$q."'";$result = mysql_query($sql);echo "<table border='1'><tr><th>NAME</th><th>SURNAME</th><th>DIVISION</th><th>SCORE</th></tr>";while($row = mysql_fetch_array($result))  {  echo "<tr>";  echo "<td>" . $row['champions_name'] . "</td>";  echo "<td>" . $row['champion_surname'] . "</td>";  echo "<td>" . $row['champion_of _the_day_comes_from:'] . "</td>";  echo "<td>" . $row['champions_gross_score'] . "</td>";   echo "</tr>";  }echo "</table>";mysql_close($con);?>

[birbal]

are you sure your table page is working? i tried to query it and its not returning anything. first you have to make sure data is get manupulated in mysqlreturntable.php

[SA Rob]

are you sure your table page is working? i tried to query it and its not returning anything. first you have to make sure data is get manupulated in mysqlreturntable.php

Hi,Thanks for the reply.I thought I had the table created - hence me providing the two files. The second one TABLE is what I used to create the table.Maybe that is wrong - not sure

[birbal]

i was talking about this page

<?php$q=$_GET["q"];$con = mysql_connect("localhost", "*****_r*****h", "********") orif (!$con) {die ("Hey loser, check your server connection.");}mysql_select_db("eghfya_Members", $con);$sql="SELECT * FROM _Form_Nr_6 WHERE id = '".$q."'";$result = mysql_query($sql);echo "<table border='1'><tr><th>NAME</th><th>SURNAME</th><th>DIVISION</th><th>SCORE</th></tr>";while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['champions_name'] . "</td>"; echo "<td>" . $row['champion_surname'] . "</td>"; echo "<td>" . $row['champion_of _the_day_comes_from:'] . "</td>"; echo "<td>" . $row['champions_gross_score'] . "</td>"; echo "</tr>"; }echo "</table>";mysql_close($con);?>

http://www.gnjgf.co.za/MySQLreturntable.php are not this link point to that file?did you test the page it is returning data? is that page creating the html table as you wanted?

[SA Rob]

i was talking about this pagehttp://www.gnjgf.co.za/MySQLreturntable.php are not this link point to that file?did you test the page it is returning data? is that page creating the html table as you wanted?

Hi,This is the page I am talking about. All th ecode on that page is as you have seen and presented.It only gives me a blank page - that is my problem - i dont know why it is giving me the blank page.Even when i test it it doe sthe same and obviously not having the knowledge I would not know where to start.

[birbal]

then let first fix the mysqlreturntable page.first check the query is succes or not .its good to always check it before working the resultset ($result)

if(!$result)echo mysql_error();

then make sure any rows actualy coming or not.echo mysql_num_rows($result);what is it showing?

[dsonesuk]

1) in select box page insert null into sendchangexmlhttp.send();to xmlhttp.send(null);2) $con = mysql_connect("localhost", "*****_r*****h", "********") orshould be $con = mysql_connect("localhost", "*****_r*****h", "********");3) note ':' echo "<td>" . $row['champion_of _the_day_comes_from:'] . "</td>";should this be echo "<td>" . $row['champion_of _the_day_comes_from'] . "</td>";you still have problem in that when you add a page with <html> <body> you insert this into another pages <html> <body>, you have to found out how you insert code such as javascript or css, and link to external js and css into the head of your setup you are using. I had to strip away the extra <htm> <body><head> tags.

[SA Rob]

then let first fix the mysqlreturntable page.first check the query is succes or not .its good to always check it before working the resultset ($result)then make sure any rows actualy coming or not.echo mysql_num_rows($result);what is it showing?

Hi I have mad ethe following changes:

<?php$q=$_GET["tournament_results_for"];$con = mysql_connect("localhost", "*", "*") orif (!$con)  {die ("Hey loser, check your server connection.");}mysql_select_db("eghfya_Members",$con);$sql="SELECT * FROM _Form_Nr_6 WHERE id = '".$q."'";$result = mysql_query($sql);if(!$result)echo mysql_error();echo mysql_num_rows($result); echo "<table border='1'><tr><th>NAME</th><th>SURNAME</th><th>DIVISION</th><th>SCORE</th></tr>";while($row = mysql_fetch_array($result))  {  echo "<tr>";  echo "<td>" . $row['champions_name'] . "</td>";  echo "<td>" . $row['champion_surname'] . "</td>";  echo "<td>" . $row['champion_of _the_day_comes_from:'] . "</td>";  echo "<td>" . $row['champions_gross_score'] . "</td>";   echo "</tr>";  }echo "</table>";mysql_close($con);?>

No status changeMaybe where I am making the change is incorrect

[SA Rob]

Hi I have mad ethe following changes:

<?php$q=$_GET["tournament_results_for"];$con = mysql_connect("localhost", "*", "*") orif (!$con)  {die ("Hey loser, check your server connection.");}mysql_select_db("eghfya_Members",$con);$sql="SELECT * FROM _Form_Nr_6 WHERE id = '".$q."'";$result = mysql_query($sql);if(!$result)echo mysql_error();echo mysql_num_rows($result);echo "<table border='1'><tr><th>NAME</th><th>SURNAME</th><th>DIVISION</th><th>SCORE</th></tr>";while($row = mysql_fetch_array($result))  {  echo "<tr>";  echo "<td>" . $row['champions_name'] . "</td>";  echo "<td>" . $row['champion_surname'] . "</td>";  echo "<td>" . $row['champion_of _the_day_comes_from:'] . "</td>";  echo "<td>" . $row['champions_gross_score'] . "</td>";   echo "</tr>";  }echo "</table>";mysql_close($con);?>

No status changeMaybe where I am making the change is incorrect

Hi Dsoneuk,The changes you have suggested have had a positve results - thank you - I can now see thatthe pages talke to one another as the table headings appear , Nam, Surname, etcThe table below how ever is not populating with data now from the Database.Please advise what you mean by stripping . . .

[dsonesuk]

look at the page source of the select box, we had the same problem before with previous project, you have 2 x <html><head></head><body></body></html>, at the moment you will have to insert just

<script type="text/javascript">function showUser(str){if (str=="")  {  document.getElementById("txtHint").innerHTML="";  return;  }if (window.XMLHttpRequest)  {// code for IE7+, Firefox, Chrome, Opera, Safari  xmlhttp=new XMLHttpRequest();  }else  {// code for IE6, IE5  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");  }xmlhttp.onreadystatechange=function()  {  if (xmlhttp.readyState==4 && xmlhttp.status==200)	{	document.getElementById("txtHint").innerHTML=xmlhttp.responseText;	}  }xmlhttp.open("GET","MySQLreturntable.php?q="+str,true);xmlhttp.send(null);}</script><form><select name="users" onchange="showUser(this.value)"><option value="">Select a tournament:</option><option value="1">Kempton Park Golf Club</option><option value="2">Kyalami Golf Club</option><option value="3">St.Francis Links</option><option value="4">Gardener Ross Golf Estate</option><option value="5">Rustenburg Golf Club</option><option value="6">Witbank Golf Course</option><option value="7">Ebotse Golf Estate</option><option value="8">Umhlali Golf Estate</option><option value="9">Durban Country Club</option><option value="10">Steenburg Golf Club</option><option value="11">Maccauvlei Golf Club</option><option value="12">Mt.Edgecombe Country Club</option><option value="13">Randpark Golf Club</option><option value="14">Oubaai Golf Club</option><option value="15">Zwartkop Country Club</option><option value="16">ERPM Golf Club</option><option value="17">Polokwane Golf Club</option><option value="18">Erivale</option><option value="19">Royal Cape Golf Club</option><option value="20">Paarl Golf Club</option><option value="21">Irene Country Club</option><option value="22">Stellenbosch Golf Club</option><option value="23">Mowbray Golf Club</option><option value="24">Bryanston Golf Club</option><option value="25">Schoeman Park Golf Club</option><option value="26">Nelspruit Golf Club</option><option value="27">The Hill</option><option value="28">Mossel Bay Golf Club</option><option value="29">Wanderers Golf Club</option><option value="30">Pecanwood Golf Club</option></select></form><br /><div id="txtHint"><b>The Results of the Winners will be listed here.</b></div>

removing <html> etc

[dsonesuk]

Having a quick read about bluevoda web builder, you have to place any header code, or links to external files in header section, and html code BETWEEN <body> and </body> within html section.

[SA Rob]

look at the page source of the select box, we had the same problem before with previous project, you have 2 x <html><head></head><body></body></html>, at the moment you will have to insert just

<script type="text/javascript">function showUser(str){if (str=="")  {  document.getElementById("txtHint").innerHTML="";  return;  }if (window.XMLHttpRequest)  {// code for IE7+, Firefox, Chrome, Opera, Safari  xmlhttp=new XMLHttpRequest();  }else  {// code for IE6, IE5  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");  }xmlhttp.onreadystatechange=function()  {  if (xmlhttp.readyState==4 && xmlhttp.status==200)	{	document.getElementById("txtHint").innerHTML=xmlhttp.responseText;	}  }xmlhttp.open("GET","MySQLreturntable.php?q="+str,true);xmlhttp.send(null);}</script><form><select name="users" onchange="showUser(this.value)"><option value="">Select a tournament:</option><option value="1">Kempton Park Golf Club</option><option value="2">Kyalami Golf Club</option><option value="3">St.Francis Links</option><option value="4">Gardener Ross Golf Estate</option><option value="5">Rustenburg Golf Club</option><option value="6">Witbank Golf Course</option><option value="7">Ebotse Golf Estate</option><option value="8">Umhlali Golf Estate</option><option value="9">Durban Country Club</option><option value="10">Steenburg Golf Club</option><option value="11">Maccauvlei Golf Club</option><option value="12">Mt.Edgecombe Country Club</option><option value="13">Randpark Golf Club</option><option value="14">Oubaai Golf Club</option><option value="15">Zwartkop Country Club</option><option value="16">ERPM Golf Club</option><option value="17">Polokwane Golf Club</option><option value="18">Erivale</option><option value="19">Royal Cape Golf Club</option><option value="20">Paarl Golf Club</option><option value="21">Irene Country Club</option><option value="22">Stellenbosch Golf Club</option><option value="23">Mowbray Golf Club</option><option value="24">Bryanston Golf Club</option><option value="25">Schoeman Park Golf Club</option><option value="26">Nelspruit Golf Club</option><option value="27">The Hill</option><option value="28">Mossel Bay Golf Club</option><option value="29">Wanderers Golf Club</option><option value="30">Pecanwood Golf Club</option></select></form><br /><div id="txtHint"><b>The Results of the Winners will be listed here.</b></div>

removing <html> etc

Hi,I believe I have mad ethe changes as requested - yet the status remins the same.

[dsonesuk]

make sure id is the same as the field name used in table example not 'Id', or 'ID', and check other column names, also if spelled different, or you have added a space after or before name, which will cause it to fail.

[SA Rob]

make sure id is the same as the field name used in table example not 'Id', or 'ID', and check other column names, also if spelled different, or you have added a space after or before name, which will cause it to fail.

Would I be correct in understanding you as follows:

document.getElementById("txtHint").innerHTML=xmlhttp.responseText;

The "getElementId" is ok . . . .it is the "txtHint" part that needs changing ?I dont see any other places that have the word Id or ID appear.The field name for which the numbers appear in not listed in MySQL as ID but as Record_Nr and this could be posing the challenge .This field is set up by the form maker and changing that would be an effort in itself.I think then changing soemthing in the script would be the order of the day.

[dsonesuk]

No, i mean$sql="SELECT * FROM _Form_Nr_6 WHERE id = '".$q."'"; echo "<td>" . $row['champions_name'] . "</td>"; echo "<td>" . $row['champion_surname'] . "</td>"; echo "<td>" . $row['champion_of _the_day_comes_from:'] . "</td>"; echo "<td>" . $row['champions_gross_score'] . "</td>";should match exactly the field/column names in your table, the id column records should have ref number that relates to tournament select dropdown value<option value="2">Kyalami Golf Club</option>the sql is looking for $sql="SELECT * FROM _Form_Nr_6 WHERE id = 2"; if field/column name 'id' does not exist or the no records with '2' it will never find or show these.IF from what i gather the field/column 'id' name should be 'Record_Nr' then just change $sql="SELECT * FROM _Form_Nr_6 WHERE id = '".$q."'";to$sql="SELECT * FROM _Form_Nr_6 WHERE Record_Nr = '".$q."'";