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;