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 insert select example with manipulation of fields

This mysql "insert from another table with select" example shows how you can concatenate two of the selected fields into one inserted field. Obviously you can use any of the other MySQL select string, arithmetic and mathematical operators and functions.

insert into tbl_properties (
agent_property_id,
agent_id,
property_address,
property_borough,
.. etc
)
select
Prop_ID,
concat(prop_firmid,prop_branchid),
Prop_Name,
Prop_Street,
.. etc
from tbl_raw_data;

Simple PHP Delete MySQL Records Function

function fn_delete_records($agent)
{
$query="delete from tbl_lpp where agent_id='$agent'";
$result=mysql_query($query) or fn_mysql_error($query,__LINE__,mysql_error());
}

function fn_mysql_error($query,$line,$mysql_error)
{
echo <<<__HTML__ <br><b>Line</b>   : $line
<br><b>Query</b>   : $query
<br><b>MySql Error</b> : $mysql_error
__HTML__;
exit;
}

Using MySQL Meta Fields and Data

Imagine you have records with a field containing a UK Postcode (ZIP) code.
A typical UK Postcode is OX1 1AA or OX10 1BA but in fact in the software you also need just the Area Code part eg OX1 or OX10. Rather than parse out the Area Code dynamically in your code every time you need it why not store the Area Code in an additional meta-field. This makes for more elegant and more efficient code at the cost of a small amount of extra data. The additional advantage is that you can check for invalid postcode data at entry time.

Meta Fields such as this can be used for financial data which must be converted to a percentage etc.

Using MySQL's Case Statement

The date field "fund_date" is of form 'yyyy-mm-dd'.
The following query extracts the fund performance on a per-month-per-year basis.

SELECT year(fund_date) as year,
sum(CASE month(fund_date) WHEN 1 THEN percent_return END) AS Jan ,
sum(CASE month(fund_date) WHEN 2 THEN percent_return END) AS Feb ,
...
sum(CASE month(fund_date) WHEN 11 THEN percent_return END) AS Nov ,
sum(CASE month(fund_date) WHEN 12 THEN percent_return END) AS 'Dec'
FROM tbl_funds group by year(fund_date) ORDER BY year(fund_date) desc

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!