MySQL and PHP - enquiry tool for ad-hoc requirements

A SCHEME FOR A DATABASE ENQUIRY SYSTEM FROM A WEB FORM.

This scheme is written as a technical report, with regard to one of our customers who has a large and complex set of tables to join and requires a number of standard reports and also frequent ad-hoc / slightly different reports.

Base tools - LAMP.
Linux operating system.
Apache httpd web server.
MySQL database.
PHP to program the queries and web forms.

MANAGEMENT OVERVIEW

This is a requirement for a statistical reporting system in which data is updated in batches at intervals measured in days or weeks, and between each update a large number of reports will be generated. The table structure may be complex, and although the users will be skilled and sophisticated staff it is felt wise to shelter them from elements of the queries which could lead wrong results. Yet at the same time, they need to be able to run a query with more or less any imaginable parameters.

In the basic scheme demonstrated in this report, a single PHP page is used. When initially called up, it simply offers the user a table of optional elements for his query.

When the form is submitted back to the page, HARD CODED joins are combined with WHERE elements based on the user's form input to roll together a request which provides the desired results.

IN DETAIL - MANDATORY ELEMENTS

Initial demonstration:

The JOINs are hard coded into the application (for maintainability they should be in a separate file, but that makes the demo harder to follow). Since the demonstration includes a many-to-many mapping, there's a third (pivot) table involved.

Later:

Appropriate LEFT and RIGHT options to be added to the JOINS, controllable by the user to select whether orphan records are shown or not, and to give an option to select orphans only.

IN DETAIL - OPTIONAL ELEMENTS

Initial demonstration:

WHERE clauses linked with AND operators can be selected on a number of fields. The exact specification of what's allowed in clauses on each particular filed will be set up by function calls to allow the programmer with good data knowledge to quickly tailor the application.

If the user completes a box asking for a limit, then the limit will be pushed onto a WHERE list which is then imploded with an AND - a quick and easy route to maximum flexibility.

To be added later:

Protection against injection attacks
Sticky fields to allow queries to be tuned
Greater sophistication in helping users set up LIKE and RLIKE
GROUP BY and HAVING selection to allow for summary reports
Output option to file on server
Output option as a CSV file for the browser to save
Support for OR as well as AND operations
Library of common queries
Log in system
Support for LIMIT clause to restrict rows returned
Support for field selection classes to restrict columns
Field selection limits to include SQL functions
Reports to describe query run in details
Ability to edit actual SQL and resubmit (for special cases)
Queries with few matches to describe how relaxed match would increase selection

SOURCE CODE

Here's the complete source code of our initial scheme. Beware - it's a bit rough and ready (but it was written in a very short time) - in particular it's prone to injection attacks.

<?php

include ("sqlhelper.inc");

/*
Clever selection based on books, pivot and authors
CAUTION - does not check user inputs for "nasties" */

$basequery = "select title,isbn,fullname,b_pivot.biid,b_pivot.aiid,
b_pivot.pvid from (b_btab join b_pivot on b_btab.biid = b_pivot.biid)
join b_atab on b_atab.aiid = b_pivot.aiid";

if ($_REQUEST[filled]) {

      mysql_connect("localhost","trainee","abc123");
  mysql_select_db("wellho");

      $wherebits = array();

   if ($_REQUEST[title]) {
         $clause = makeclause("title","titlehow","title");
               // infield method dbfield
                array_push($wherebits, $clause);

                };
      if ($_REQUEST[author]) {
                array_push($wherebits,"fullname like '%$_REQUEST[author]%'");
           };

      $condits = " where ".implode(" and ",$wherebits);
       $full = "$basequery $condits";

  $result = "Running <b>$full</b><br><br>\n";

     $rs = mysql_query($full);
       while ($row = mysql_fetch_assoc($rs)) {
         $result .= $row[title]." ... ";
                $result .= $row[fullname]."<br>\n";
            }
} else {
      $result = "Your results will appear here";
}

?>
<html>
<head><title>Looking up books by Well House Consulants
</title>
</head>
<body><h1>Book Information Example</h1>
<?= $result ?>
<hr>
What do you want to look for? <br>
<form method=POST>
<input name=filled type=hidden value=1>
In title ... <input name=title>
<select name=titlehow>
<option value=exact>Exact
<option value=in>In
<option value=like SELECTED>Like
<option value=rlike>Rlike (careful!)
</select>
<br>
In Author .... <input name=author><br>
<input type=submit>
</form>
</body>
</html>

That file uses sqlhelper.inc, into which we would put all the utility functions that were going to be called up time and again. In this first simple example, the only such function is makeclause which would be used to construct an element of a WHERE clause.

<?php

function makeclause($infield,$method,$dbfield) {
           switch ($_REQUEST[$method]) {
case "like":
               $clause = "$dbfield like '%$_REQUEST[$infield]%'";
              break;
case "in":
                $clause = "$dbfield in ($_REQUEST[$infield])";
          break;
case "rlike":
             $clause = "$dbfield rlike '$_REQUEST[$infield]'";
               break;
// Exact
default:
          $clause = "$dbfield = '$_REQUEST[$infield]'";
           break;
  }
       return $clause;
}
?>