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

Question

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.

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>
</form>
<br>
<div id="txtHint"><b>Person info will be listed here...</b></div>

</body>
</html>

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>

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.

Have you watched the AJAX request / response in the browser's developer tools? Are there any errors reported? — Jay Blanchard, Nov 07 '16 at 22:28
All of those curly quotes will get you in trouble - quit editing code with Word. You have multiple syntax issues because of this. — Jay Blanchard, Nov 07 '16 at 22:29
You don't need to select a DB if you're putting it in the original connection. — Jay Blanchard, Nov 07 '16 at 22:30
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Nov 07 '16 at 22:30
I want to be able to copy this example. Just for learning purposes. I won't be using this on a real website. http://www.w3schools.com/php/php_ajax_database.asp — Skin99, Nov 07 '16 at 22:35
@JayBlanchard Using developer tools shows that there an error 500 with my server at the point: — Skin99, Nov 07 '16 at 22:44
@JayBlanchard at the point; xmlhttp.open("GET","getuser.php?q="+str,true); xmlhttp.send(); — Skin99, Nov 07 '16 at 22:45
If you have a 500 error it is because something is wrong in your PHP script. Probably all of those curly quotes. — Jay Blanchard, Nov 07 '16 at 22:51
@JayBlanchard Thanks. So I should replace the curly quotes with ' and " instead? — Skin99, Nov 07 '16 at 22:54

score 0 · Answer 1 · edited Apr 23 '19 at 23:19

0

mysqli_select_db($con,"ajax_demo");

Replace the "ajax_demo" with your database name.

edited Apr 23 '19 at 23:19

Wai Ha Lee

8,598
83
57
92

answered Apr 23 '19 at 22:48

Vivek Hitendra Dhande

1

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

1 Answers1