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__;

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;
}

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!

MySQL Subquery or Multiple Select Queries

Hi I have a table with one record per person and per item of clothing

so

peter, jumper,blue
peter,trousers,green
sue,dress,green
peter,jumper,red
gordon,jumper,green


I want to write a query to select all people with green jumpers but only if they have no other color jumper

So in the above case it would ONLY select Gordon not greedy old Peter

A solution provided is a sub select or multiple select query

SELECT *
FROM myTable AS t1
WHERE t1.clothing = 'jumper' AND t1.color = 'green'
AND NOT EXISTS(SELECT *
FROM myTable AS t2
WHERE t2.person = t1.person AND t2.clothing = 'jumper'
AND t2.color <> 'green')

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

Moving a Wordpress Blog to a New Domain

To update WordPress options with the new blog location, use the following SQL command:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.old-domain.com', 'http://www.new-domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

After that you will need to fix URLs of the WordPress posts and pages, which translated from post slug, and stored in database wp_posts table as guid field. The URL values in this field are stored as absolute URLs instead of relative URLs, so it needs to be changed with the following SQL query:

UPDATE wp_posts SET guid = replace(guid, 'http://www.old-domain.com','http://www.new-domain.com');

If you have linked internally within blog posts or pages with absolute URLs, these links will point to wrong locations after you move the blog location. Use the following SQL commands to fix all internal links to own blog in all WordPress posts and pages:

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com');

from http://www.mydigitallife.info




PHP Primitive to Create an HTML Form Dropdown from a MySQL table

$options_html=fn_make_form_selection_element($selected);

function fn_make_form_selection_element($selected_value)
{
$select_html="";
$query = "select * from lookup_offices order by office_id";
$result = mysql_query($query) or die ("query not valid".mysql_error());

while($row = mysql_fetch_assoc($result))
{
$db_value=$row['office_id'];
$db_name=$row['office_real_name'];

$option_selected='';
if ($db_value==$selected_value) $option_selected='selected';
$select_html.=<<<_STR_
< option value="$db_value" $option_selected>$db_name< /option>\n
_STR_;
}
return ($select_html);
}

Creating a Derived Data Column (for efficiency)

Hi
Frequently we query our database for a value but then have to always do the same manipulation on it before we can use it. Well it can often be useful to do this "once" in the database and then store the manipulated value in a separate column of the database.

Here is a very simple example
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*3.14159);

Where col2 is set to the value of col1*pi

You could also do more complex calculation/filtering/shortening in your code eg php,ASP etc

Can you see the power of this technique?