Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
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;
}
?>


See also Similar tool in use for our library

Please note that articles in this section of our web site were current and correct to the best of our ability when published, but by the nature of our business may go out of date quite quickly. The quoting of a price, contract term or any other information in this area of our website is NOT an offer to supply now on those terms - please check back via our main web site

Related Material

Using MySQL Databases in PHP Pages
  [104] - ()
  [515] - ()
  [572] - ()
  [581] - ()
  [647] - ()
  [666] - ()
  [723] - ()
  [915] - ()
  [937] - ()
  [947] - ()
  [1010] - ()
  [1561] - ()
  [1983] - ()
  [2071] - ()
  [2259] - ()
  [2320] - ()
  [2432] - ()
  [2447] - ()
  [2561] - ()
  [2628] - ()
  [3035] - ()
  [3455] - ()
  [4378] - ()
  [4483] - ()

GUI tools for MySQL
  [572] - ()
  [1724] - ()

Interfacing Applications to MySQL Databases
  [104] - ()
  [644] - ()
  [663] - ()
  [723] - ()
  [1381] - ()
  [1450] - ()
  [1518] - ()
  [1561] - ()
  [1885] - ()
  [2263] - ()
  [2381] - ()
  [2745] - ()
  [2790] - ()
  [3035] - ()
  [3099] - ()
  [3447] - ()
  [3455] - ()
  [4436] - ()

Interfacing Applications to MySQL Databases
  [104] - ()
  [644] - ()
  [663] - ()
  [723] - ()
  [1381] - ()
  [1450] - ()
  [1518] - ()
  [1561] - ()
  [1885] - ()
  [2263] - ()
  [2381] - ()
  [2745] - ()
  [2790] - ()
  [3035] - ()
  [3099] - ()
  [3447] - ()
  [3455] - ()
  [4436] - ()

resource index - PHP
Solutions centre home page

You'll find shorter technical items at The Horse's Mouth and delegate's questions answered at the Opentalk forum.

At Well House Consultants, we provide training courses on subjects such as Ruby, Lua, Perl, Python, Linux, C, C++, Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer) many questions, and answers to those which are of general interest are published in this area of our site.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2024: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/solutions/php-mysq ... ments.html • PAGE BUILT: Wed Mar 28 07:47:11 2012 • BUILD SYSTEM: wizard