CPSC340A
Database Systems

Create Connection

• Create a connection to a MySQL database

  <?php
  include("../password.php");
  $mysqli = new mysqli($host, $user, $password, $database);
  if ($mysqli->connect_errno) {
      die("Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);
  }
  ?>

• To avoid putting your password in a publicly readable file, put your passowrd in a file outside of public_html that php can read but others can not

Query Database

• Query data using MySql queries in strings

  $column = htmlspecialchars($_GET["col"]);
  $result = $mysqli->query("SELECT $column FROM TableName");

Iterate over Result

• The result of the query is in an array

• Iterate over it to see the contents

  print("<ol>");
  while ($row = $result->fetch_assoc()) {
      print("<li>");
      print("$column = " . $row[$column]);
      print("</li>");
  }
  print("</ol>");

• More information in the documentation

JSONify

• Print a JSON string so that javascript can easily use the query data

  $resultArray = array();
  while ($row = $result->fetch_assoc()) {
      array_push($resultArray, $row[$column]);
  }
  print(json_encode($resultArray));

AJAX HTTP Request

• In javascript, use AJAX to get the json string

  var handleResponse = function() {
      if (httpRequest.readyState === XMLHttpRequest.DONE &&
          httpRequest.status === 200) {
          console.log(httpRequest.responseText);
      }
  }
  var httpRequest = new XMLHttpRequest();
  httpRequest.onreadystatechange = handleResponse;
  httpRequest.open('GET', 'http://cs.roanoke.edu/~username/sql.php', true);
  httpRequest.send();

Parse JSON

• In the response handler, convert the json string to a JavaScript object

  var responseList = JSON.parse(httpRequest.responseText);
  for (var i = 0; i < responseList.length; i++) {
      console.log(responseList[i]);
  }

Update HTML

• Use the Javascript object to update the HTML document

  var responseList = JSON.parse(httpRequest.responseText);
  for (var i = 0; i < responseList.length; i++) {
      var orderedList = document.getElementById('theList');
      var item = document.createElement('li');
      var text = document.createTextNode(responseList[i]);
      item.appendChild(text);
      orderedList.appendChild(item);
  }