Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Lua, etc
 
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
  [4483] Moving from mysql to mysqli - simple worked example - (2015-05-03)
  [4378] What FGW passengers want to talk about / and PHP programming to find out - (2015-01-01)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [2628] An example of an injection attack using Javascript - (2010-02-08)
  [2561] The future of MySQL - (2010-01-03)
  [2447] MySQL stored procedures / their use on the web from PHP - (2009-10-10)
  [2432] Using print_r in PHP to explore mysql database requests - (2009-10-01)
  [2320] Helping new arrivals find out about source code examples - (2009-08-03)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2071] Setting up a MySQL database from PHP - (2009-03-08)
  [1983] Keeping PHP code in database and running it - (2009-01-09)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1010] Dates, times, clickable diarys in PHP - (2006-12-28)
  [947] What is an SQL injection attack? - (2006-11-27)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [915] Paging through hundreds of entries - (2006-11-05)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [666] Database design - get it right from first principles - (2006-04-02)
  [647] Checking for MySQL errors - (2006-03-15)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [515] MySQL - an FAQ - (2005-12-03)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

GUI tools for MySQL
  [1724] addslashes v mysql_real_escape_string in PHP - (2008-07-27)
  [572] Giving the researcher power over database analysis - (2006-01-22)

Interfacing Applications to MySQL Databases
  [4436] Accessing a MySQL database from Python with mysql.connector - (2015-02-21)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [3447] Needle in a haystack - finding the web server overload - (2011-09-18)
  [3099] Perl - database access - DBD, DBI and DBIx modules - (2010-12-22)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2381] Checking the database connection manually - (2009-08-28)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [1885] Hiding a MySQL database behind a web page - (2008-11-15)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1518] Downloading data for use in Excel (from PHP / MySQL) - (2008-01-25)
  [1450] Easy selection of multiple SQL conditions from PHP - (2007-11-30)
  [1381] Using a MySQL database to control mod_rewrite via PHP - (2007-10-06)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [663] Python to MySQL - (2006-03-31)
  [644] Using a MySQL database from Perl - (2006-03-13)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

Interfacing Applications to MySQL Databases
  [4436] Accessing a MySQL database from Python with mysql.connector - (2015-02-21)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [3447] Needle in a haystack - finding the web server overload - (2011-09-18)
  [3099] Perl - database access - DBD, DBI and DBIx modules - (2010-12-22)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2381] Checking the database connection manually - (2009-08-28)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [1885] Hiding a MySQL database behind a web page - (2008-11-15)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1518] Downloading data for use in Excel (from PHP / MySQL) - (2008-01-25)
  [1450] Easy selection of multiple SQL conditions from PHP - (2007-11-30)
  [1381] Using a MySQL database to control mod_rewrite via PHP - (2007-10-06)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [663] Python to MySQL - (2006-03-31)
  [644] Using a MySQL database from Perl - (2006-03-13)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

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., 2019: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01225 708225 • FAX: 01225 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