Fetching data from mysql through anchor tag onclick event using ajax

Question

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

It seems like most of your php code is on the same page as the HTML. To click on a button to request data, usually the click leads to an ajax call that returns json data, and the json data is parsed and distributed to various elements. I think we're going to need more information. Are you trying to update old software? — TimSPQR
– TimSPQR, Commented Feb 3, 2014 at 5:55
This is just a prototype , im planning to separate php code once i get this to work. i don't know how to use json im still a beginner, and im not using jquery here, i accidentally tagged jqeury here. — rez
– rez, Commented Feb 3, 2014 at 6:22
when i click on the anchor tag link, the onclick event will query the name and seatid from the seatmapping database and display it on the same page, but i can't get to display the name and seatid from database but it shows the table with headers "Name" and "Seat Number".. — rez
– rez, Commented Feb 3, 2014 at 6:24

Kamehameha · Accepted Answer · 2014-02-04 05:36:44Z

1

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

edited Feb 4, 2014 at 5:36

answered Feb 3, 2014 at 6:00

Kamehameha

5,4781 gold badge25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

rez Over a year ago

I tried just using database name as parameter, but still doesn't work.. i check the query to connect to the database and it's working fine. it doesn't display the result though which is the name and seatid..

Kamehameha Over a year ago

Try running getinfo.php?q=some_correct_value from the browser. It sure is not an Ajax error, since the table is being displayed. In mysql_select_db and mysql_query use or die(mysql_error()) like in the answer. And also, in your php code, you have not checked if $_GET['q'] is empty or not.

rez Over a year ago

Thanke you very much for helping me. I tried using or die(mysql_error()) and now the table won't show when i click the link..

rez Over a year ago

Thanks again for helping me, the table is now displaying the result of the query but the "getinfo.php?q=" must be set to a value, example 1, in order for the result to display. But when i set it the value to a variable, in my case the str, it says "No rows to be fetched". But i want to be able to display the name and seatid based on the link i clicked. I'm starting to think maybe there's something wrong with <a> tag hyperlink as that is where the value assigned to the variable is coming from. Or i could be wrong...

Kamehameha Over a year ago

@rez Yep,you are right.Now I noticed.When I tried it locally I hardcoded the arguments. You can the check the edit now.

|

Collectives™ on Stack Overflow

Fetching data from mysql through anchor tag onclick event using ajax

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related