Jump to content

Help - Fetch Data from MySQL using PHP to populate Table


SA Rob
 Share

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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');?>

Edited by baligena
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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 .

Edited by dsonesuk
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.

Edited by dsonesuk
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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);?>

Edited by SA Rob
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 . . .
Link to comment
Share on other sites

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

Edited by dsonesuk
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Edited by dsonesuk
Link to comment
Share on other sites

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.

Edited by SA Rob
Link to comment
Share on other sites

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."'";

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...