IPT - A Virtual Approach - PHP

IPT A Virtual Approach by Peter Whitehouse

Quick Links:
2Do
Classnotes

PHP Query Engine

based on Music Database

Run the Script live...

The difficulty with any PHP immediate-mode project (ie. type a question, get an answer, now) is getting the query to the MySQL server. This is made more complex by an HTML form's inability to accurately encode symbols entered through a form element (text boxes, areas etc). Escape characters, like the double quote (") have a reserved meaning and are encoded out of a form as \". Note the leading slosh (\) put in by the form.. This then mangles the query and the SQL engine winges.

PHP kindly provides a function called stripslashes() which removes errant escape character junk allowing the query to be sent as typed. stripslashes has been used in the following script.

The script is a fairly functional query interface - standard MySQL can be entered into the text area and the answer table is returned with numbered lines. For explanation of actual codey bits - look lower in this page.

<?php
  $link = mysql_connect("localhost", "username", "password") or die("Unable to connect");
  mysql_select_db("dbname") or die("Unable to select database");
?>

<form action="musicquery.php" method="post">
  <p>Type your query here:<br>
    <textarea name="querytext" rows="10" cols="50" wrap></textarea>
    <br>
    <input type="submit" name="submitquery" value="SUBMIT">
  </p>
</form>

<?php
   if ($query = stripslashes($_POST["querytext"])) {
      if ($result = mysql_query($query)) {
          echo "<p>Your Query has been processed.</p>";
          echo "<p>Your Query was:<br><pre>".stripslashes($query)."</pre></p>";
          $numrows = mysql_num_rows($result);
          $columns = mysql_num_fields($result);
          echo "<p> Here is your result table: </p>";

          echo "<p>The answer table has ".$numrows." rows, each having ".$columns." fields";
          echo "<br> (collating may take some time - please be patient)</p>";
   
          if ($numrows > 0) {
             print "<p> <table border=1 cellpadding=1 cellspacing=0><tr>";
             for ($x = 0; $x < $columns; $x++) {
                 print "<th>";
                 $name = mysql_field_name($result,$x);
                 print "$name";
                 print "</th>";
             }
             print "</tr>";

             while ($row = mysql_fetch_row($result)) {
                   print "<tr>";
                   for ($y =0; $y < $columns; $y++) {
                        print "<td>$row[$y]</td> ";
                   }
                   print "</tr>";
             } //end while

          print "</table>";

      } //end if result

      } else { echo "<p>Error Processing Query: " .mysql_error() . "</p>"; }
   }
?>

Notice:

I use a dirty trick of asking if there IS a query submitted, otherwise post an error first submit
if there was something in the query test box, I attempt to process it regardless of whether it is correct sql or not - this is not dangerous as the user permissions will let the user of this database SELECT only, so no nefarious commands can be injected
I print out the query, the number of rows and fields per row(if it is an error free query and it executed correctly) then the answer table
I use a "for each row, for each field" nested loop routine to visit each field row by row and output the answer table amongst table markup schrapnel

Run the Script live...