rez rez - 2 months ago 8
Javascript Question

Fetching data from mysql through anchor tag onclick event using ajax

I would like to be able to display data from mysql in a table on same page using ajax. When user clicks the anchor link with value=seatid the table displays the table showing the name using the seatid. But i can't get to display the name, when i click the link it just display the table with the Header "Name" but not the actual name of the person seating on that seatid.. I'm using php, javascript, ajax, and mysql... Is there something wrong with the code, pls help me...

<html>
<head>
<title>Seat Mapper System</title>
<style>
*{
font-size: 16px;
color: black;
font-family: tahoma;}

td td{
width: 75px;
height: 35px;
}

td td a{
text-decoration: none;
display: block;
width: 100%;
height: 100%;
}
</style>
<script>
function showInformation(str){
if (str==""){
document.getElementById("txtInfo").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("txtInfo").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getinfo.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>
<div id="txtInfo"><b>Person info will be listed here.</b></div>
<?php
$linkID = @ mysql_connect("localhost", "root", "*******") or die("Could
not connect to MySQL server");
@ mysql_select_db("seatmapping") or die("Could not select database");
/* Create and execute query. */
$query = "SELECT * from seats order by rowId, columnId desc";
$result = mysql_query($query);
$prevRowId = null;
$seatColor = null;
$tableRow = false;
//echo $result;
echo "<table width='100%' border='0' cellpadding='0' cellspacing='0'>";
while(list($rowId,$columnId,$status,$updatedby,$name,$seatid)=mysql_fetch_row($result))
{
if ($prevRowId != $rowId) {
if ($rowId != 'A') {
echo "</tr></table></td>";
echo "\n</tr>";
}
$prevRowId = $rowId;
echo "\n<tr><td align='center'>;
echo<table border='0' cellpadding='0' cellspacing='3'><tr>";
}
else {
$tableRow = false;
}
if ($status == 0) {
$seatColor = "99FF33";
} else if ($status == 1 && $updatedby == 'user1') {
$seatColor = "FFCC99";
} else if ($status == 2 && $updatedby == 'user1') {
$seatColor = "FF9999";
} else {
$seatColor = "red";
}

echo "\n<td bgcolor='$seatColor' align='center'>";
echo "<a href=\"#\" onclick=\"showInformation(this)\" value=\"$seatid\"><b>$seatid</b></a>";
echo "</td>";
if (($rowId == 'A' && $columnId == 7) || ($rowId == 'B' && $columnId == 7))
{
// This fragment is for adding a blank cell which represent the "center aisle"
echo "<td>&nbsp;</td>";
}
}
echo "</tr></table></td>";
echo "</tr>";
echo "</table>";

/* Close connection to database server. */
mysql_close();
?>
</body>
</html>


This is the getinfo.php page:

<?php
$q = intval($_GET['q']);

$con = mysql_connect('localhost','root','Newpass123#','seatmapping');
if (!$con)
{
die('Could not connect: ' . mysql_error($con));
}

mysql_select_db($con,'seatmapping');
$sql="SELECT name, seatid FROM seats WHERE seatid = '".$q."'";

$result = mysql_query($con,$sql);

echo "<table border='1'>
<tr>
<th>Name</th>
<th>Seat Number</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['seatid'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);
?>

Answer

Your AJAX code is working fine, but your getinfo.php page has some errors.

You have used mysql, but it's being deprecated. So you need to consider using mysqli or PDO.

mysql_select_db requires only one parameter(the database name), you have given 2.

Check if the query has executed properly. use mysql_query($con,$sql) or die(mysql_error())

getinfo.php re-written with errors echoed.

<?php
if(isset($_GET['q'])){
    $q = intval($_GET['q']);

    $con = mysql_connect('localhost','root','Newpass123#','seatmapping');
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db('seatmapping');
    $sql="SELECT name, seatid FROM seats WHERE seatid = ".$q;
    $result = mysql_query($sql) or die("query err " . mysql_error());   

    if(mysql_num_rows($result) == 0){
        echo "No rows to be fetched.";
    }else{
        echo "<table border='1'>
        <tr>
        <th>Name</th>
        <th>Seat Number</th>
        </tr>";
        while($row = mysql_fetch_array($result))
        {
          echo "<tr>";
          echo "<td>" . $row['name'] . "</td>";
          echo "<td>" . $row['seatid'] . "</td>";
          echo "</tr>";
        }
        echo "</table>";
    }
    mysql_close($con);
}else{
    echo "GET variable q is not set.";
}
?> 

In your HTML(first code), the argument that you are the passing was "this" but in your showInformation() you are treating it as a string. You can directly pass the $seatid.

echo "<a href=\"#\" onclick=\"showInformation($seatid)\" value=\"$seatid\"><b>$seatid</b></a>";
                                              ^^^^^^^ Change this to $seatid