Coder2345 Posted November 12, 2016 Share Posted November 12, 2016 I'm trying to select data from a MySQL database that is hosted on a webserver. I want to be able to retrieve the data from a table within the database and then illustrate it within a HTML table. There's an example on W3Schools that I've been following, but I'm unable to retrieve the data successfully. http://www.w3schools.com/php/php_ajax_database.asp Below is the source code: (HTML) <html> <head> //Javascript code <script> function showUser(str) { if (str == "") { document.getElementById("txtHint").innerHTML = ""; return; } else { 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 (this.readyState == 4 && this.status == 200) { document.getElementById("txtHint").innerHTML = this.responseText; } }; xmlhttp.open("GET","getuser.php?q="+str,true); xmlhttp.send(); } } </script> </head> <body> <form> <select name="users" onchange="showUser(this.value)"> <option value="">Select a person:</option> <option value="1">Peter Griffin</option> <option value="2">Lois Griffin</option> <option value="3">Joseph Swanson</option> <option value="4">Glenn Quagmire</option> </select> PHP File: (getuser.phd) <!DOCTYPE html> <html> <head> <style> table { width: 100%; border-collapse: collapse; } table, td, th { border: 1px solid black; padding: 5px; } th {text-align: left;} </style> </head> <body> <?php $q = intval($_GET['q']); $con = mysqli_connect('www.example.com','user_Admin','12345-678','my_DB'); if (!$con) { die('Could not connect: ' . mysqli_error($con)); } mysqli_select_db($con,"ajax_demo"); $sql="SELECT * FROM user WHERE id = '".$q."'"; $result = mysqli_query($con,$sql); echo "<table> <tr> <th>Firstname</th> <th>Lastname</th> <th>Age</th> <th>Hometown</th> <th>Job</th> </tr>"; while($row = mysqli_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['FirstName'] . "</td>"; echo "<td>" . $row['LastName'] . "</td>"; echo "<td>" . $row['Age'] . "</td>"; echo "<td>" . $row['Hometown'] . "</td>"; echo "<td>" . $row['Job'] . "</td>"; echo "</tr>"; } echo "</table>"; mysqli_close($con); ?> </body> </html> *MySQL table is attached I think the issue might exist from mysqli_select_db($con,"ajax_demo"); onwards inside the PHP file. Should I be referring to the table that contains the data inside the database? I have the PHP File hosted on my webserver, so I'm not sure why it won't retrieve that data when a person is selected from the list of options on the HTML page. Any help would be much appreciated. Link to comment Share on other sites More sharing options...
Ingolme Posted November 12, 2016 Share Posted November 12, 2016 You have to put the name of your database in mysqli_select_db(). But you don't even need to call mysqli_select_db() because the database was already selected when calling mysqli_connect() Link to comment Share on other sites More sharing options...
Coder2345 Posted November 12, 2016 Author Share Posted November 12, 2016 You have to put the name of your database in mysqli_select_db(). But you don't even need to call mysqli_select_db() because the database was already selected when calling mysqli_connect() Thank you. I will try that now. Link to comment Share on other sites More sharing options...
Coder2345 Posted November 12, 2016 Author Share Posted November 12, 2016 You have to put the name of your database in mysqli_select_db(). But you don't even need to call mysqli_select_db() because the database was already selected when calling mysqli_connect() I changed it to the name of my database, but it still doesn't work? Any other suggestions? When I view the code in Netbeans, the line below states this; $q = intval($_GET['q']); Do not access SuperGlobal $_GET Array Directly... use some filtering functions instead e.g filter_input( ), conditions with_*is( ) functions, etc.). Link to comment Share on other sites More sharing options...
Ingolme Posted November 12, 2016 Share Posted November 12, 2016 I would ignore the Netbeans error, but you must not put variables directly into SQL queries, use prepared statements: http://www.w3schools.com/php/php_mysql_prepared_statements.asp What errors are you getting when you run the code? "Doesn't work" is not very descriptive. Link to comment Share on other sites More sharing options...
Coder2345 Posted November 12, 2016 Author Share Posted November 12, 2016 I would ignore the Netbeans error, but you must not put variables directly into SQL queries, use prepared statements: http://www.w3schools.com/php/php_mysql_prepared_statements.asp What errors are you getting when you run the code? "Doesn't work" is not very descriptive. When I run the PHP file and use Developer Tools in the Chrome browser; Failed to load resource: the server responded with a status of 500 (Internal Server Error) When I run the HTML file and use Developer Tools in the Chrome browser; selectuser.html:45 Uncaught ReferenceError: showuser is not defined(…)onchange @ selectuser.html:45 ^ This occurs when I attempt to select one of the users from the list. Thanks for replying. Link to comment Share on other sites More sharing options...
Ingolme Posted November 12, 2016 Share Posted November 12, 2016 You should check the response of the HTTP request to see if a PHP error message shows up. Remember that functions in Javascript are case sensitive. showuser and showUser are two different identifiers. Link to comment Share on other sites More sharing options...
dsonesuk Posted November 12, 2016 Share Posted November 12, 2016 (edited) You are only supposed to retrieve table and data not html, head tags, css etc, the css should be placed the file the content/data is returned to. This is only required to be returned echo "<table> <tr> <th>Firstname</th> <th>Lastname</th> <th>Age</th> <th>Hometown</th> <th>Job</th> </tr>"; while($row = mysqli_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['FirstName'] . "</td>"; echo "<td>" . $row['LastName'] . "</td>"; echo "<td>" . $row['Age'] . "</td>"; echo "<td>" . $row['Hometown'] . "</td>"; echo "<td>" . $row['Job'] . "</td>"; echo "</tr>"; } echo "</table>"; Edited November 12, 2016 by dsonesuk Link to comment Share on other sites More sharing options...
Coder2345 Posted November 12, 2016 Author Share Posted November 12, 2016 (edited) You should check the response of the HTTP request to see if a PHP error message shows up. Remember that functions in Javascript are case sensitive. showuser and showUser are two different identifiers. Thanks. Do you mean like this? The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator, webmaster@example.net and inform them of the time the error occurred, and anything you might have done that may have caused the error. More information about this error may be available in the server error log. Additionally, a 500 Internal Server Error error was encountered while trying to use an ErrorDocument to handle the request. I have both set to showUser in the HTML file. Edited November 12, 2016 by Coder2345 Link to comment Share on other sites More sharing options...
Coder2345 Posted November 12, 2016 Author Share Posted November 12, 2016 You are only supposed to retrieve table and data not html, head tags, css etc, the css should be placed the file the content/data is returned to. This is only required to be returned echo "<table> <tr> <th>Firstname</th> <th>Lastname</th> <th>Age</th> <th>Hometown</th> <th>Job</th> </tr>"; while($row = mysqli_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['FirstName'] . "</td>"; echo "<td>" . $row['LastName'] . "</td>"; echo "<td>" . $row['Age'] . "</td>"; echo "<td>" . $row['Hometown'] . "</td>"; echo "<td>" . $row['Job'] . "</td>"; echo "</tr>"; } echo "</table>"; My apologies, but I don't quite understand what you mean? Link to comment Share on other sites More sharing options...
dsonesuk Posted November 12, 2016 Share Posted November 12, 2016 What is returned as responseText will include ALL html tags and css styling etc shown in red <!DOCTYPE html><html><head><style>table {width: 100%;border-collapse: collapse;}table, td, th {border: 1px solid black;padding: 5px;}th {text-align: left;}</style></head><body><?php$q = intval($_GET['q']);$con = mysqli_connect('www.example.com','user_Admin','12345-678','my_DB');if (!$con) {die('Could not connect: ' . mysqli_error($con));}mysqli_select_db($con,"ajax_demo");$sql="SELECT * FROM user WHERE id = '".$q."'";$result = mysqli_query($con,$sql);echo "<table><tr><th>Firstname</th><th>Lastname</th><th>Age</th><th>Hometown</th><th>Job</th></tr>";while($row = mysqli_fetch_array($result)) {echo "<tr>";echo "<td>" . $row['FirstName'] . "</td>";echo "<td>" . $row['LastName'] . "</td>";echo "<td>" . $row['Age'] . "</td>";echo "<td>" . $row['Hometown'] . "</td>";echo "<td>" . $row['Job'] . "</td>";echo "</tr>";}echo "</table>";mysqli_close($con);?></body></html> BUT! All you require is the results produced by php code in blue, which is placed in element with id 'txtHint', else what you will be adding is returned html page from server within a element of a html page, with multiple <!doctype html><head><html><body> etc, the styling, which should be in between <head>...</head> of html page will appear in element with ID 'txtHint'. 1 Link to comment Share on other sites More sharing options...
dsonesuk Posted November 12, 2016 Share Posted November 12, 2016 (edited) The html page should have a element with textHint id, and styling for table placed in between the <head>...</head> <html> <head> //Javascript code <script> function showUser(str) { if (str == "") { document.getElementById("txtHint").innerHTML = ""; return; } else { 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 (this.readyState == 4 && this.status == 200) { document.getElementById("txtHint").innerHTML = this.responseText; } }; xmlhttp.open("GET","getuser.php?q="+str,true); xmlhttp.send(); } } </script> <style> table { width: 100%; border-collapse: collapse; } table, td, th { border: 1px solid black; padding: 5px; } th {text-align: left;} </style> </head> <body> <form> <select name="users" onchange="showUser(this.value)"> <option value="">Select a person:</option> <option value="1">Peter Griffin</option> <option value="2">Lois Griffin</option> <option value="3">Joseph Swanson</option> <option value="4">Glenn Quagmire</option> </select> </form> <div id="txtHint">Result from php page should appear here</div> </body> </html> Edited November 12, 2016 by dsonesuk 1 Link to comment Share on other sites More sharing options...
Coder2345 Posted November 12, 2016 Author Share Posted November 12, 2016 The html page should have a element with textHint id, and styling for table placed in between the <head>...</head> <html> <head> //Javascript code <script> function showUser(str) { if (str == "") { document.getElementById("txtHint").innerHTML = ""; return; } else { 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 (this.readyState == 4 && this.status == 200) { document.getElementById("txtHint").innerHTML = this.responseText; } }; xmlhttp.open("GET","getuser.php?q="+str,true); xmlhttp.send(); } } </script> <style> table { width: 100%; border-collapse: collapse; } table, td, th { border: 1px solid black; padding: 5px; } th {text-align: left;} </style> </head> <body> <form> <select name="users" onchange="showUser(this.value)"> <option value="">Select a person:</option> <option value="1">Peter Griffin</option> <option value="2">Lois Griffin</option> <option value="3">Joseph Swanson</option> <option value="4">Glenn Quagmire</option> </select> </form> <div id="txtHint">Result from php page should appear here</div> </body> </html> Thank you for your input. I'll try what has been suggested. Link to comment Share on other sites More sharing options...
dsonesuk Posted November 12, 2016 Share Posted November 12, 2016 IF int you don't require quotes for value received from form, quotes are required for string values. $sql="SELECT * FROM user WHERE id = ".$q; 1 Link to comment Share on other sites More sharing options...
dsonesuk Posted November 12, 2016 Share Posted November 12, 2016 Ha, just checked the w3schools ajax example link, BAD, BAD example, totally wrong! Link to comment Share on other sites More sharing options...
Coder2345 Posted November 12, 2016 Author Share Posted November 12, 2016 Ha, just checked the w3schools ajax example link, BAD, BAD example, totally wrong! It seems so! Thanks for the help. Link to comment Share on other sites More sharing options...
dsonesuk Posted November 12, 2016 Share Posted November 12, 2016 Try checking php page works by setting id value to one that exist, I presume database, table, table column names match what is required and matching id exist to match <?php //$q = intval($_GET['q']); $q = 1; // set to matching id in table $con = mysqli_connect('www.example.com','user_Admin','12345-678','my_DB'); if (!$con) { die('Could not connect: ' . mysqli_error($con)); } mysqli_select_db($con,"ajax_demo"); $sql="SELECT * FROM user WHERE id = '".$q."'"; $result = mysqli_query($con,$sql); echo "<table> <tr> <th>Firstname</th> <th>Lastname</th> <th>Age</th> <th>Hometown</th> <th>Job</th> </tr>"; while($row = mysqli_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['FirstName'] . "</td>"; echo "<td>" . $row['LastName'] . "</td>"; echo "<td>" . $row['Age'] . "</td>"; echo "<td>" . $row['Hometown'] . "</td>"; echo "<td>" . $row['Job'] . "</td>"; echo "</tr>"; } echo "</table>"; mysqli_close($con); ?> Link to comment Share on other sites More sharing options...
Coder2345 Posted November 13, 2016 Author Share Posted November 13, 2016 Try checking php page works by setting id value to one that exist, I presume database, table, table column names match what is required and matching id exist to match <?php //$q = intval($_GET['q']); $q = 1; // set to matching id in table $con = mysqli_connect('www.example.com','user_Admin','12345-678','my_DB'); if (!$con) { die('Could not connect: ' . mysqli_error($con)); } mysqli_select_db($con,"ajax_demo"); $sql="SELECT * FROM user WHERE id = '".$q."'"; $result = mysqli_query($con,$sql); echo "<table> <tr> <th>Firstname</th> <th>Lastname</th> <th>Age</th> <th>Hometown</th> <th>Job</th> </tr>"; while($row = mysqli_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['FirstName'] . "</td>"; echo "<td>" . $row['LastName'] . "</td>"; echo "<td>" . $row['Age'] . "</td>"; echo "<td>" . $row['Hometown'] . "</td>"; echo "<td>" . $row['Job'] . "</td>"; echo "</tr>"; } echo "</table>"; mysqli_close($con); ?> I've tried what you suggested. "Could not connect:" is the response when a name is selected from the form. The same response occurs when the php page is run in the browser. Two questions; $sql="SELECT * FROM user WHERE id = '".$q."'"; What is user referring to here? My table? Shouldn't user have quotes around it? <select name="users" onchange="showUser(this.value)"> Also, what is users referring to? Any help would be much appreciated. Link to comment Share on other sites More sharing options...
Ingolme Posted November 13, 2016 Share Posted November 13, 2016 All of this is in the tutorial, please try to make an effort to learn: http://www.w3schools.com/php/php_forms.asp http://www.w3schools.com/php/php_mysql_intro.asp http://www.w3schools.com/php/php_mysql_select.asp 1 Link to comment Share on other sites More sharing options...
dsonesuk Posted November 13, 2016 Share Posted November 13, 2016 You should have setup a database with name 'my_DB', with table 'user, field names or column names wil be if correct be 'id' as shown in sql 'WHERE id', this should match exactly the same case (lower or upper) as field names used in column names of table. The remaining column names should match exactly the names referenced here $row['FirstName'] $row['LastName'] $row['Age'] $row['Hometown'] $row['Job'] With records of id, FirstName, LastName, etc 1 Peter Griffin 2 Lois Griffin 3 Joseph Swanson 4 Glenn Quagmire If none of these exist, or names do not match exactly, it won't work, if 'id' in sql and table column name is 'ID' they are treated as different. 1 Link to comment Share on other sites More sharing options...
Coder2345 Posted November 13, 2016 Author Share Posted November 13, 2016 You should have setup a database with name 'my_DB', with table 'user, field names or column names wil be if correct be 'id' as shown in sql 'WHERE id', this should match exactly the same case (lower or upper) as field names used in column names of table. The remaining column names should match exactly the names referenced here $row['FirstName'] $row['LastName'] $row['Age'] $row['Hometown'] $row['Job'] With records of id, FirstName, LastName, etc 1 Peter Griffin 2 Lois Griffin 3 Joseph Swanson 4 Glenn Quagmire If none of these exist, or names do not match exactly, it won't work, if 'id' in sql and table column name is 'ID' they are treated as different. Thank you! I understand what you mean. Link to comment Share on other sites More sharing options...
Coder2345 Posted November 15, 2016 Author Share Posted November 15, 2016 This is the error I get when I try to select a person from the list; Uncaught ReferenceError: xmlhttp is not defined at showUser: xmlhttp.onreadystatechange = function() { at HTMLSelectElement.onchange: <select name="users" onchange="showUser(this.value)"> How does one solve this issue? Any help would be much appreciated. Link to comment Share on other sites More sharing options...
justsomeguy Posted November 15, 2016 Share Posted November 15, 2016 In general, that variable is out of scope. Maybe you defined it as a local variable somewhere then tried to reference it from a global function, or maybe you're using the wrong name or something in a closure. Link to comment Share on other sites More sharing options...
Coder2345 Posted November 15, 2016 Author Share Posted November 15, 2016 In general, that variable is out of scope. Maybe you defined it as a local variable somewhere then tried to reference it from a global function, or maybe you're using the wrong name or something in a closure. I can't quite pinpoint the area where this might be occurring. A quick Google search of "xmlhttp is not defined" lead me to this: https://www.npmjs.com/package/xmlhttprequest Should I add that code to mine? Link to comment Share on other sites More sharing options...
justsomeguy Posted November 15, 2016 Share Posted November 15, 2016 No, that is node.js, that won't run in a browser. That's totally unrelated to what you're doing. You need to understand what's going on, not add random pieces of code. You're trying to use a variable that doesn't exist. "xmlhttp" is not a reserved word in Javascript, it is a variable name in your code. The variable isn't defined. I couldn't guess why you don't have it defined, other than just the fact that it's not defined where you're trying to use it, but if you show all of your code then maybe I can point it out. In the code on the w3schools tutorial they define it here: if (window.XMLHttpRequest) { // code for IE7+, Firefox, Chrome, Opera, Safari xmlhttp = new XMLHttpRequest(); } else { // code for IE6, IE5 xmlhttp = new ActiveXObject("Microsoft.XMLHTTP"); }Maybe you decided to remove that code, maybe you changed the variable name, maybe it's in a different scope than where you're trying to set the onreadystatechange handler. I can't really guess without seeing your code. 1 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now