creating a mysql search string dynamically?

Question

I'm trying to create a simple search page, but I'm not 100% sure how to write the actual search string (using the appropriate AND's etc if the variable exists) here's the code:

if ($post) {

    //get all search variables
    $type = JRequest::getVar('type');
    $classifications = JRequest::getVar('classifications', array(0), 'post', 'array');
    $rating = JRequest::getVar('rating');
    $status = JRequest::getVar('status');
    $cterms = JRequest::getVar('cterms');
    $clientid = JRequest::getVar('clientid');
    $company = JRequest::getVar('company');
    $address = JRequest::getVar('address');
    $name = JRequest::getVar('name');
    $surname = JRequest::getVar('surname');
    $city = JRequest::getVar('city');
    $state = JRequest::getVar('state');
    $pcode = JRequest::getVar('pcode');
    $country = JRequest::getVar('country');

    //create search string
    echo "SELECT * FROM #__db_clients "; <- the query is supposed to be done here.. it's in as echo because I was trying to spit it out before trying to make it run.. :)

} else {

    echo 'There has been an error, please try again.';

};

I've tried using (if type != null then searchtype = "where type = 'X'") but then I couldn't figure out how to place the AND before/after if it's required for the search.. if that makes sense?

karim79 · Accepted Answer · 2009-09-15 01:51:05Z

2

This is a quick example. I don't know what kind of data JRequest::getVar returns (always a string, or mixed types?) but this should start you off. Make sure to use whichever escaping method applies within the foreach loop:

if ($post) {
    $criteria = array();
    //get all search variables
    $criteria['type'] = JRequest::getVar('type');
    $criteria['classifications'] = JRequest::getVar('classifications', array(0), 'post', 'array');
    $criteria['rating'] = JRequest::getVar('rating');

    //if there are some criteria, make an array of fieldName=>Value maps
    if(!empty($criteria)) {
        $where = array();
        foreach($criteria as $k => $v) {
            //IMPORTANT!!
            //$v is the value of the field, needs to be quoted correctly!!
            $where[] = "$k = '$v'";
        }
    }
    //create search string
    $query =  "SELECT * FROM #__db_clients";

    if($where) {
        $query .= " where " . join(' AND ', $where);
    }   
} else {    
    echo 'There has been an error, please try again.';
};

edited Sep 15, 2009 at 1:51

answered Sep 15, 2009 at 1:34

karim79

343k67 gold badges420 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Bill Karwin Over a year ago

Be sure to apply escaping to $v as you interpolate it into the expression.

karim79 Over a year ago

@Bill Karwin - Thanks, absolutely! I don't know which escaping method applies to his application, so I put a note into the answer (as well as code comment).

SoulieBaby Over a year ago

Thanks so much, that'll definately help me get my code going :) Muchly appreciated!

karim79 Over a year ago

@SoulieBaby - np, make sure to thoroughly test whatever you turn that into, it helps to put a lot of diagnostic echo statements at each step, so you can see what's going on. And make sure everything is properly escaped/quoted. Good luck!

SoulieBaby Over a year ago

Hmm it doesnt like "foreach($criteria as $k = $v) {" I get this error: Parse error: syntax error, unexpected '=', expecting ')'

|

Collectives™ on Stack Overflow

creating a mysql search string dynamically?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related