retrieve data from database mysql not working

Question

Hi i am using the html dropdown's onchange event using ajax

In the code i am using, should get the address column value when i change the drop down.

but it is not working.What may have gone wrong?

here is the code

<html>
<head>
  <script>
     function showUser( str ) {
        if ( str == "" ) {
           document.getElementById("txtHint").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("txtHint").innerHTML=xmlhttp.responseText;
           }
        }
        xmlhttp.open("GET", "getuser.php?q=" + str, true);
        xmlhttp.send();
     }
   </script>
</head>
<body>

<form>    
  <?php
     mysql_connect('localhost', 'tiger', 'tiger');
     mysql_select_db('theaterdb');
     $sql = "select theater_name from theater;";
     $result = mysql_query($sql);
     echo "<select name='theater_name' id='course' onchange='showUser(this.value);'>";
     while ( $row = mysql_fetch_array( $result ) ) {
        echo "<option value='" . $row['theater_name'] ."'>" . $row['theater_name']. "</option>";
     }
     echo "</select>";
?>

</form>

<br>
<div id="txtHint"><b>Info</b></div>
</body>
</html>

Code for getuser.php

<?php
   $q = $_GET["q"];
   $con = mysqli_connect("localhost", "tiger", "tiger", "theaterdb");
   if ( !$con ) {
      die('Could not connect: ' . mysqli_error( $con ) );
   }

   mysqli_select_db( $con );
   $sql = "SELECT address FROM theater WHERE theater_name = '".$q."'";

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

   echo "<table border='1'>
     <tr>
        <th>Firstname</th>
     </tr>";

     while( $row = mysqli_fetch_array( $result ) ) {
       echo "<tr>";
          echo "<td>" . $row['address'] . "</td>";
       echo "</tr>";
    }
  echo "</table>";
  mysqli_close($con);
?>

You getting something in $result? print_r($result) And $q? echo $q — Sahil Mittal
– Sahil Mittal, Commented Aug 16, 2013 at 8:23
make sure that the username and password provided are correct — user2672265
– user2672265, Commented Aug 16, 2013 at 8:25
Are you getting the value to str before xmlhttp.open("GET","getuser.php?q="+str,true); line — Sahil Mittal
– Sahil Mittal, Commented Aug 16, 2013 at 8:28

Garry Welding · Accepted Answer · 2013-08-16 08:56:23Z

Ok, I've tweaked your files slightly, you shouldn't be using mysql_ or the mysqli_ functions any more, just don't... And you certainly shouldn't be using mysql function in one file and mysqli functions in the other... I've switched them over to use PDO, you're script now isn't susceptible to SQL injection, and as far as I can tell it works just fine.

index.html

<html>
    <head>
        <script>
            function showUser(str) {
                if(str=="") {
                    document.getElementById("txtHint").innerHTML="";
                    return;
                }

                if(window.XMLHttpRequest) {
                    xmlhttp=new XMLHttpRequest();
                } else {
                    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
                }

                xmlhttp.onreadystatechange = function() {
                    if(xmlhttp.readyState==4 && xmlhttp.status==200) {
                        document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
                    }
                }
                xmlhttp.open("GET","getuser.php?q="+str,true);
                xmlhttp.send();
            }
        </script>
    </head>

    <body>
        <form>
        <?php
        try {
            $dbh = new PDO('mysql:dbname=theaterdb;host=localhost','tiger','tiger');
        } catch (PDOException $e) {
            echo 'Connection failed: ' . $e->getMessage();
        }

        $sql = "SELECT theater_name FROM theater;";

        $sth = $dbh->prepare($sql);
        $sth->execute();

        echo "<select name='theater_name' id='course' onchange='showUser(this.value);'>";

        while($row = $sth->fetch(PDO::FETCH_ASSOC)) {
            echo "<option value='" . $row['theater_name'] ."'>" . $row['theater_name']. "</option>";
        }
        echo "</select>";
        ?>
        </form>
        <br>
        <div id="txtHint"><b>Info</b></div>
    </body>
</html>

getuser.php

<?php
$q = strtolower(trim($_GET["q"]));

try {
    $dbh = new PDO('mysql:dbname=theaterdb;host=localhost','tiger','tiger');
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sql = 'SELECT address FROM theater WHERE LOWER(theater_name) = :q';

$sth = $dbh->prepare($sql);
$sth->bindValue(':q', $q);
$sth->execute();

echo "<table border='1'><tr><th>Firstname</th></tr>";

while($row = $sth->fetch(PDO::FETCH_ASSOC)) {
  echo "<tr>";
  echo "<td>" . $row['address'] . "</td>";
  echo "</tr>";
}

echo "</table>";

$dbh = null;

Prabhat Srivasatva · Accepted Answer · 2013-08-16 08:47:49Z

-1

I am unable to understand why have you connected with database in both of php files ? I would suggest you to visit below link.

http://www.w3schools.com/php/php_ajax_database.asp

answered Aug 16, 2013 at 8:47

Prabhat Srivasatva

11 bronze badge

Collectives™ on Stack Overflow

retrieve data from database mysql not working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related