Jump to content
Coder2345

Selecting and retrieving Data from a MySQL Database using PHP and AJAX

Recommended Posts

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.

 

 

Share this post


Link to post
Share on other sites

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()

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.).

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by dsonesuk

Share this post


Link to post
Share on other sites

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 by Coder2345

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites

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 by dsonesuk
  • Like 1

Share this post


Link to post
Share on other sites

 

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

 

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.

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.
  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...