Create form search using PDO

Question

Possible Duplicate:
PDO with “WHERE… IN” queries

In my search form, there is a lot of check boxes and fields.
Some checkboxes belong to the group.
Example group, city.
Query like the following

SELECT id, city_id, area, city FROM an_objects  
                WHERE livedays > 0 AND type_id = :typeoffer AND rubric_id = :typerelaty 
                AND CASE 
                    WHEN :1r = '' THEN true 
                    ELSE city_id IN (:1r, :2r, :99r, :100r)
                END
                GROUP BY id ORDER BY date ASC

In this example, the need to fill 4 parameters, or a hundred.
But I want to do

SELECT id, city_id, area, city FROM an_objects  
            WHERE livedays > 0 AND type_id = :typeoffer AND rubric_id = :typerelaty 
            AND CASE 
                WHEN :1r = '' THEN true 
                ELSE city_id IN (:arrCity)
            END
            GROUP BY id ORDER BY date ASC

String form the so

if(isset($param['city']))
{
    for($i=0; $i < 9; $i++)
    {
        if(isset($param['city'][$i]))
            $raion .= $param['city'][$i] . ",";
        else
            break;
    }
    $arrCity = substr($city, 0, -1);        
}

We have the following
(: arrCity) substituted ("1,2,3,4,5,6"). This is obtained as a single string, but how to do the following
(: arrCity) (1,2,3,4,5,6)

Of course this is not a duplicate, but who cares... – Your Common Sense Feb 03 '13 at 06:58 — Your Common Sense, Feb 03 '13 at 06:58

Your Common Sense · Accepted Answer · 2013-02-03T06:40:41.823

This is an interesting question, which clearly demonstrates the fact that PDO turns out to be WAY more toilsome to use than old mysql_* and provide no good security for the wide range of real life queries.

To make such tasks easier, a database access library should allow 2 major things:

additional placeholders for the complex data types to be processed internally.
process placeholders in the arbitary query part, not in the whole query only

and PDO fails with both of them.
So, a programmer have to take care of them oneself.

So, by using a library that implements such principles, the code will be both short and safe:

if(isset($param['city']))
{
    $raion = $db->parse("AND (city_id IN (?a)", $param['city']);
}
//  you can add any number of such conditions making your search flexible
// the only thing you have to keep in mind: add a value ONLY via placeholder

$sql = "SELECT id, city_id, area, city FROM an_objects  
       WHERE livedays > 0 AND type_id = ?i AND rubric_id = ?i
       ?p
       GROUP BY id ORDER BY date ASC";
$data = $db->getAll($sql, $typeoffer, $typerelaty, $raion);

Yes, just these few lines of concize and readable code!

If I misunderstood your conditions - feel free to ask for more details, providing exact logic of building your query. I'll be glad to write exact code according to your conditions, just to demonstrate the power of the library I am talking about.

You me correctly understand. But this is library personally your? In any case, thank you! you gave me the interesting idea. — Vayas, Feb 05 '13 at 09:17
It is written by me, but it is free to use for anyone. You can find the link in my userinfo. Or you are welcome to use whatever ideas you find useful. — Your Common Sense, Feb 05 '13 at 09:31
Thank you. Nice thing is that there are enthusiasts like you. Such unique creations ready to give free, for what would the world programming was better . Thank you for writing to me. I am already using your library, and reduced by more than 300 lines of code in my application )) — Vayas, Feb 06 '13 at 05:51

Create form search using PDO

1 Answers1