Coding conditional elements of a MySQL where clause in PHP for a form

It can be tricky to code a complex where clause for a web form driven driven Search box where different options may be present or absent. The following example is a for property search form where it is possible to search by keyword, by number of beds, maximum and minimum price and any combination of these. A simple algorithm is used to ensure that the where clause syntax is respected even when there are absent elements.

if (strlen($price_min)>0)
{
$price_min_sql=<<<__SQL__ (property_sale_price >= '$price_min')
__SQL__;
}
else {$price_min_sql=1; }

if (strlen($price_max)>0)
{
$price_max_sql=<<<__SQL__ (property_sale_price <= '$price_max') __SQL__; } else {$price_max_sql=1; } if (strlen($beds_min)>0)
{
$beds_min_sql=<<<__SQL__ (property_bedrooms >= '$beds_min')
__SQL__;
}
else {$beds_min_sql=1; }

if (strlen($beds_max)>0)
{
$beds_max_sql=<<<__SQL__
(property_bedrooms <= '$beds_max')
__SQL__;
}
else {$beds_max_sql=1;
}

if (strlen($keyword)>3)
{
$keywordsql=<<<__SQL__
((property_borough like '%$keyword%') or (property_address like '%$keyword%'))
__SQL__; }
else {$keywordsql=1; }

$query=<<<__SQL__
select * from tbl_properties where $keywordsql and $beds_min_sql and $beds_max_sql and $price_min_sql and $price_max_sql
__SQL__;

MySQL rewind mysql_data_seek - PHP

// Rewind the pointer on the result handle
mysql_data_seek ($mysql_result, 0);
Just as with conventional PHP arrays you need a rewind if you need to re-read a result

PHP, MySQL and Case Sensitivity of Field Names

MySQL is always case insensitive for field names (but not table names on *nix).

Now here's the subtlety PHP will adopt what ever case you define in your query so if out of perversity you write your query , :-

"select SuRnAmE from addresses"

Then you will have to remember to use the same messy casing in PHP (known as camel case)

$surname=$row['SuRnAmE'];

so better
"select surname from tbl_addresses"

$surname=$row['surname'];


Play Safe always use lowercase!

How much intelligence in the MySQL Query how much in the Code?

SQL queries provide highly efficient access to parallel data (records). Very often you can get the exact data you require with your query and then all you have to do with your code is present it. Other times it simply isn't possible or isn't convenient to do it all in the SQL. In these cases typically you download the query results into an associative array, then you can further manipulate your data at leisure with all the power of PHP, Perl etc.

What do I mean by convenient? well sometimes it might be possible to develop a complex query which drilled right down to the exact data but this might take time to perfect when you could have coded it much quicker.

What's more I have found that over-complex queries are not very future proof, by that I mean a small change in the specification and they have to be rewritten. Further more they may not be very easy for other programmers to understand/maintain.

So do the best query you can and then manipulate the data in your code.

Truism: SQL can never be so flexible as a scripting/programming language such as PHP/Perl

PHPRunner saved me from a Nervous Breakdown!

We know we have to work cleverer rather than harder.

I was working on a large PHP/MySQL website where the specification kept changing. It was bad enough redesigning that data and recoding the PHP all the time. Fortunately by using PHPRunner I was able to rebuild the admin side each time in just minutes. Using PHPrunner you can build your PHP Pages (or ASP using ASPRunner) which allow you to search and list your records and subsequently create, modify or delete them. You know that tedious repetitive stuff.

I know PHPrunner can also build the Web side as well, but I haven't looked into that yet , but will let you know.

There is an evaluation version, download it and get hooked!

Download Here