Sometimes, you'll be able to define the queries you'll be doing on a database to a number of specific types, but in other applications you'll be able to narrowly define how you do your joins, but you'll have a wide variety of extraction requirements. This sort of requirement is typical of medical researchers, who have a database of information that they've gathered over the years and they want to look at the results in all sorts of different ways in order to find patterns and trends in the data. From such patterns and trends will come the clues from which an understanding develops, and from which in turn, and in time, a cure can be found.

So how do you ensure that the strange and adhoc queries that you wish to run maintain the integrity of the background data? One very good way is by fronting your MySQL database with a PHP script which forces you to do the correct joins between the tables to ensure the data integrity, but gives you full flexibility in the columns you select and in your where clauses.

On the course concluded last Friday, I wrote a demonstration PHP page to show this type of application, and as it's of general interest I've put all the source code and help files up on this site. I've also installed a full working copy on our server if you wish to try it out. You're very welcome to log in as demo with a password of xxx111.

This is a VERY flexible application. I could just change a few configuration lines and it would switch from estate agents and houses for sale to some totally different set.

If you want to learn how to write an application like this yourself, or how to tailor and look after mine, have a look at our PHP courses and MySQL courses. If you're already into PHP and MySQL, our Opentalk Forum provides a venue where you can ask any remaining questions you have and discuss the best techniques.
(written 2006-01-22, updated 2006-06-05)

 S158 - GUI tools for MySQL
  [1724] addslashes v mysql_real_escape_string in PHP - (2008-07-27)

S157 - More MySQL commands
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)
  [159] MySQL - Optimising Selects - (2004-12-21)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [449] Matching in MySQL - (2005-09-24)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [515] MySQL - an FAQ - (2005-12-03)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [673] Helicopter views and tartans - (2006-04-06)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1331] MySQL joins revisited - (2007-09-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)

Q916 - Object Orientation and General technical topics - Database design principles
  [375] Oops - I got my initial database design wrong - (2005-07-12)
  [666] Database design - get it right from first principles - (2006-04-02)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)

H204 - PHP and MySQL example application - adhoc database analysis
H115 - Designing PHP-Based Solutions: Best Practice
  [123] Short underground journeys and a PHP book - (2004-11-19)
  [237] Crossfertilisation, PHP to Python - (2005-03-06)
  [261] Putting a form online - (2005-03-29)
  [340] Code and code maintainance efficiency - (2005-06-08)
  [394] A year on - should we offer certified PHP courses - (2005-07-28)
  [426] Robust checking of data entered by users - (2005-08-27)
  [563] Merging pictures using PHP and GD - (2006-01-13)
  [839] Reporting on the 10 largest files or 10 top scores - (2006-08-20)
  [896] PHP - good coding practise and sticky radio buttons - (2006-10-17)
  [936] Global, Superglobal, Session variables - scope and persistance in PHP - (2006-11-21)
  [945] Code quality counts - (2006-11-26)
  [1047] Maintainable code - some positive advice - (2007-01-21)
  [1052] Learning to write secure, maintainable PHP - (2007-01-25)
  [1166] Back button - ensuring order are not submitted twice (PHP) - (2007-04-28)
  [1181] Good Programming practise - where to initialise variables - (2007-05-09)
  [1182] Painting a masterpiece in PHP - (2007-05-10)
  [1194] Drawing hands on a clock face - PHP - (2007-05-19)
  [1321] Resetting session based tests in PHP - (2007-08-26)
  [1323] Easy handling of errors in PHP - (2007-08-27)
  [1381] Using a MySQL database to control mod_rewrite via PHP - (2007-10-06)
  [1389] Controlling and labelling Google maps via PHP - (2007-10-13)
  [1390] Converting from postal address to latitude / longitude - (2007-10-13)
  [1391] Ordnance Survey Grid Reference to Latitude / Longitude - (2007-10-14)
  [1482] A story about benchmarking PHP - (2007-12-23)
  [1487] Efficient PHP applications - framework and example - (2007-12-28)
  [1490] Software to record day to day events and keep an action list - (2007-12-31)
  [1533] Short and sweet and sticky - PHP form input - (2008-02-06)
  [1623] PHP Techniques - a workshop - (2008-04-26)
  [1694] Defensive coding techniques in PHP? - (2008-07-02)
  [1794] Refactoring - a PHP demo becomes a production page - (2008-09-12)
  [2199] Improving the structure of your early PHP programs - (2009-05-25)
  [2221] Adding a newsfeed for your users to a multipage PHP application - (2009-06-06)
  [2430] Not just a PHP program - a good web application - (2009-09-29)
  [2679] How to build a test harness into your PHP - (2010-03-16)
  [3539] Separating program and artwork in PHP - easier maintainance, and better for the user - (2011-12-05)
  [3813] Injection Attacks - PHP, SQL, HTML, Javascript - and how to neutralise them - (2012-07-22)
  [3820] PHP sessions - a best practice teaching example - (2012-07-27)
  [3926] Filtering PHP form inputs - three ways, but which should you use? - (2012-11-18)
  [4069] Even early on, separate out your program from your HTML! - (2013-04-25)
  [4118] We not only teach PHP and Python - we teach good PHP and Python Practice! - (2013-06-18)
  [4326] Learning to program - comments, documentation and test code - (2014-11-22)
  [4641] Using an MVC structure - even without a formal framework - (2016-02-07)
  [4691] Real life PHP application using our course training MVC example - (2016-06-05)

H113 - Using MySQL Databases in PHP Pages
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)
  [647] Checking for MySQL errors - (2006-03-15)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [915] Paging through hundreds of entries - (2006-11-05)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [947] What is an SQL injection attack? - (2006-11-27)
  [1010] Dates, times, clickable diarys in PHP - (2006-12-28)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1983] Keeping PHP code in database and running it - (2009-01-09)
  [2071] Setting up a MySQL database from PHP - (2009-03-08)
  [2320] Helping new arrivals find out about source code examples - (2009-08-03)
  [2432] Using print_r in PHP to explore mysql database requests - (2009-10-01)
  [2447] MySQL stored procedures / their use on the web from PHP - (2009-10-10)
  [2561] The future of MySQL - (2010-01-03)
  [2628] An example of an injection attack using Javascript - (2010-02-08)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [4378] What FGW passengers want to talk about / and PHP programming to find out - (2015-01-01)
  [4483] Moving from mysql to mysqli - simple worked example - (2015-05-03)

Back to Well House Manor

Previous and next or Horse's mouth home

Forward to Giving an excuse for Christmas

Why run two different web servers
Eating for the single business traveller
PHP - dividing a string up into pieces
Giving an excuse for Christmas
Giving the researcher power over database analysis
Well House Manor
Features and Benefits
Instructions for bright people
NB ''Aspiration''