Select form to display data from MySQL not working when using AJAX

webguync · January 4, 2012

I had this working, but when I try and get fancy and use AJAX the data doesn't display. I think this is a PHP problem though.

My code for the select form including AJAX code

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-GB">
<head>
<title>AJAX Example</title>
<link rel="stylesheet" type="text/css" href="Form.css" media="screen" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$("tr:odd").addClass("odd");
});
</script>
<script type="text/javascript">
function showPlayers(str)
{
var xmlhttp;
if (str=="")
  {
  document.getElementById("DataDisplay").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("DataDisplay").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","Query.php?category_id="+str,true);
xmlhttp.send();
}
</script>
        </head>

<body>

<h1">AJAX Example</h1>

<?php
#connect to MySQL
$conn = @mysql_connect( "localhost","username","pw")
or die( "You did not successfully connect to the DB!" );

#select the specified database
$rs = @mysql_SELECT_DB ("MyDB", $conn )
or die ( "Error connecting to the database test!");
?>
<form name="sports" id="sports">
<legend>Select a Sport</legend>
<select name="category_id" onChange="showPlayers(this.value)">
<option value="">Select a Sport:</option>
<?php
$sql = "SELECT category_id, sport FROM sports ".
"ORDER BY sport";

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
  echo "<option value=\"".$row['category_id']."\">".$row['sport']."</option>\n  ";
}
?>
</select>

</form>
<br />
<div id="DataDisplay"></div>

</body>
</html>

Query.php


<?php
#get the id
$id=$_GET["category_id"];

#connect to MySQL
$conn = @mysql_connect( "localhost","username","pw")
or die( "Error connecting to MySQL" );

#select the specified database
$rs = @mysql_SELECT_DB ("MyDB", $conn )
or die ( "Could not select that particular Database");
#$id="category_id";
#create the query
$sql ="SELECT *
FROM sports
INNER JOIN players ON sports.category_id = players.category_id
WHERE players.category_id = '".$id."'";


echo $sql;

#execute the query
$rs = mysql_query($sql,$conn);
#start the table code
echo "<table><tr><th>Category ID</th><th>Sport</th><th>First Name</th><th>Last Name</th></tr>";
#write the data
while( $row = mysql_fetch_array( $rs) )
{
echo ("<tr><td>");
echo ($row["category_id"] );
echo ("</td>");
echo ("<td>");
echo ($row["sport"]);
echo ("</td>");

echo ("<td>");
echo ($row["first_name"]);
echo ("</td>");

echo ("<td>");
echo ($row["last_name"]);
echo ("</td></tr>");
}
echo "</tr></table>";
mysql_close($conn);
?>

I think the problem is either with this part in the AJAX

xmlhttp.open("GET","Query.php?category_id="+str,true);

or most likely in my Query.php code

when I wasn't using AJAX and using POST it worked fine, but adding the AJAX stuff and GET it doesn't work.

When I echo out the SQL the result is

SELECT * FROM sports INNER JOIN players ON sports.category_id = players.category_id WHERE players.category_id = ''

so the category_id is not being selected properly and that is the primary key/foreign key in the MySQL table which connects the JOIN.

AyKay47 · January 4, 2012

1. instead of onchange=showPlayers(this.value); you will want

onchange=showPlayers(this.options[this.selectedIndex].value);

2. don't get into the habit of using the suppression operator @, should only be used in certain instances, this is not one of them.

scootstah · January 4, 2012

2. don't get into the habit of using the suppression operator @, should only be used in certain instances, this is not one of them.

It's acceptable to use in this case, because otherwise if you fail to connect it's going to spit it out something like this for everyone to see:

Warning: mysql_connect(): Access denied for user 'blah'@'localhost' (using password: YES)

EDIT: By the way, why are you mixing jQuery with native Javascript? One of the most awesome things about jQuery is handling AJAX. jQuery.post

AyKay47 · January 4, 2012

2. don't get into the habit of using the suppression operator @, should only be used in certain instances, this is not one of them.

It's acceptable to use in this case, because otherwise if you fail to connect it's going to spit it out something like this for everyone to see:
Warning: mysql_connect(): Access denied for user 'blah'@'localhost' (using password: YES)
EDIT: By the way, why are you mixing jQuery with native Javascript? One of the most awesome things about jQuery is handling AJAX. jQuery.post

unless you suppress the warnings/errors the correct way, which would be to set the php.ini directives "error_reporting" and "display_errors" to 0 and no, respectfully, for a live server.

scootstah · January 4, 2012

2. don't get into the habit of using the suppression operator @, should only be used in certain instances, this is not one of them.

It's acceptable to use in this case, because otherwise if you fail to connect it's going to spit it out something like this for everyone to see:
Warning: mysql_connect(): Access denied for user 'blah'@'localhost' (using password: YES)
EDIT: By the way, why are you mixing jQuery with native Javascript? One of the most awesome things about jQuery is handling AJAX. jQuery.post
unless you suppress the warnings/errors the correct way, which would be to set the php.ini directives "error_reporting" and "display_errors" to 0 and no, respectfully, for a live server.

True, you shouldn't be displaying ANY warnings to the public on a live server.

But there's no ill side effects from suppressing mysql_connect(). It ensures that if your code ever lands on an improperly configured server that the world isn't going to get a glimpse at your database credentials.

AyKay47 · January 4, 2012

2. don't get into the habit of using the suppression operator @, should only be used in certain instances, this is not one of them.

It's acceptable to use in this case, because otherwise if you fail to connect it's going to spit it out something like this for everyone to see:
Warning: mysql_connect(): Access denied for user 'blah'@'localhost' (using password: YES)
EDIT: By the way, why are you mixing jQuery with native Javascript? One of the most awesome things about jQuery is handling AJAX. jQuery.post
unless you suppress the warnings/errors the correct way, which would be to set the php.ini directives "error_reporting" and "display_errors" to 0 and no, respectfully, for a live server.
True, you shouldn't be displaying ANY warnings to the public on a live server.

But there's no ill side effects from suppressing mysql_connect(). It ensures that if your code ever lands on an improperly configured server that the world isn't going to get a glimpse at your database credentials.

no argument there, but it should be done by using the php.ini directives listed above.

webguync · January 4, 2012

thanks for the replies, but my problem with the data not displaying still exist. I changed the select to

<select name="category_id" onchange=showPlayers(this.options[this.selectedIndex].value);">

still doesn't display. I think the issue may be with my SQL query not executing the WHERE clause properly. When I echo it out I STILL get.

SELECT * FROM sports INNER JOIN players ON sports.category_id = players.category_id WHERE players.category_id = ''

scootstah · January 4, 2012

Can you confirm that str holds a value before passing it via AJAX?

webguync · January 4, 2012

how would I confirm that?

scootstah · January 4, 2012

function showPlayers(str)
{
     alert(str);

webguync · January 4, 2012

I didn't get the alert when I selected a sport.

My code


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-GB">
<head>
<title>Inspired Evolution : : AJAX Example</title>
<link rel="stylesheet" type="text/css" href="Form.css" media="screen" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$("tr:odd").addClass("odd");
});
</script>
<script type="text/javascript">
function showPlayers(str)
{
var xmlhttp;
if (str=="")
  {
  document.getElementById("DataDisplay").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("DataDisplay").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","Query.php?category_id="+str,true);
xmlhttp.send();
}
</script>
<script type="text/javascript">
function showPlayers(str)
{
     alert(str);
}
</script>
        </head>

<body>

<h1 id="Tutoring">Inspired-Evolution.com : : AJAX Example</h1>


<p>Below is an application example which utilizes the following web technologies to display the data. HTML, CSS, PHP/MySQL, JQuery and AJAX.</p>

<?php
#connect to MySQL
$conn = mysql_connect( "internal-db.s118256.gridserver.com","db118256_Bruce","Phoenix90")
or die( "You did not successfully connect to the DB!" );

#select the specified database
$db = mysql_SELECT_DB ("db118256_MyTestDB", $conn )
or die ( "Error connecting to the database test!");
?>
<form name="sports" id="sports">
<legend>Select a Sport</legend>
<select name="category_id" onchange=showPlayers(this.options[this.selectedIndex].value);">
<option value="">Select a Sport:</option>
<?php
$sql = "SELECT category_id, sport FROM sports ".
"ORDER BY sport";

$db = mysql_query($sql);

while($row = mysql_fetch_array($db))
{
  echo "<option value=\"".$row['category_id']."\">".$row['sport']."</option>\n  ";
}
?>
</select>

</form>
<br />
<div id="DataDisplay"></div>

</body>
</html>

scootstah · January 4, 2012

You're missing a double quote.

<select name="category_id" onchange=showPlayers(this.options[this.selectedIndex].value);">

Should be:

<select name="category_id" onchange="showPlayers(this.options[this.selectedIndex].value);">

webguync · January 4, 2012

thanks. I am getting the alert now. The value is indeed holding.

scootstah · January 4, 2012

So how does your query look now?

webguync · January 4, 2012

when I go to Query.php in the browser it echoes out as:

SELECT * FROM sports INNER JOIN players ON sports.category_id = players.category_id WHERE players.category_id = ''

scootstah · January 4, 2012

Of course, because the id is supposed to be sent as a query string.

What does the AJAX response look like?

xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
         alert(xmlhttp.responseText);

webguync · January 4, 2012

not getting the alert which I guess means the conditions are being met.

AyKay47 · January 4, 2012

when I go to Query.php in the browser it echoes out as:
SELECT * FROM sports INNER JOIN players ON sports.category_id = players.category_id WHERE players.category_id = ''

really, I would recommend that you switch to using the AJAX api with Jquery, much easier and less coding.

scootstah · January 4, 2012

when I go to Query.php in the browser it echoes out as:
SELECT * FROM sports INNER JOIN players ON sports.category_id = players.category_id WHERE players.category_id = ''
really, I would recommend that you switch to using the AJAX api with Jquery, much easier and less coding.

Agreed. Here's an example:

$.ajax({
    url: "Query.php?category_id=" + str,
    success: function(response){
        $('#DataDisplay').html(response);    
    }
});

webguync · January 4, 2012

Thanks I will try that. Would that code be all I need to replace this?

function showPlayers(str)
{
var xmlhttp;
if (str=="")
  {
  document.getElementById("DataDisplay").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("DataDisplay").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","Query.php?category_id="+str,true);
xmlhttp.send();
}

or would I need to add type:"GET"; and other stuff?

scootstah · January 4, 2012

Actually, you can ditch that entire function, as well as the onchange bit on the select with this:

$(function(){
    $('select[name="category_id"]').change(function(){
        var id = $('select[name="category_id"] option:selected').text();
        
        $.ajax({
            url: 'Query.php?category_id=' + id,
            success: function(response){
                $('#DataDisplay').html(response);
            }
        });
    });
});

AyKay47 · January 4, 2012

Edit: lack of attention to details.

scootstah · January 4, 2012

Before you start with Jquery, note that you will need to include the latest jquery lib in head section of your page. You can find this on google, they actually host a library that you can use.

He already is. ;P

webguync · January 4, 2012

ok cool, it sorta kind almost works now, but the Query is still wrong. My table is displayed below the form, so it's pulling the data, but my query is now.

SELECT * FROM sports INNER JOIN players ON sports.category_id = players.category_id WHERE players.category_id = 'Football'

the query is pulling the corresponding sport instead of the id integer it should be:

SELECT * FROM sports INNER JOIN players ON sports.category_id = players.category_id WHERE players.category_id = '4'

scootstah · January 4, 2012

Change

var id = $('select[name="category_id"] option:selected').text();

To

var id = $('select[name="category_id"] option:selected').val();

Sign In

Select form to display data from MySQL not working when using AJAX

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information