TalkPHP - Searching with multiple options

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - Searching with multiple options (http://www.talkphp.com/general/4452-searching-multiple-options.html)

Searching with multiple options

I have run into a problem with an app I am working on. I am trying to do a search. I had the basic search down, but I am trying to let people search with multiple options, basically searching in certain fields. If you can point me in the right direction, then many thanks.

Basically, I have an auto database; a stock number, a manufacturer, a model, vin, price, and a year. User can search and I have checkboxes for each of these to include in the search.

I just can't figure out how to form the query. I tried joining them with .= and the ||, but not working right. Basically, I had the intial SELECT FROM WHERE, then checked if each post was set and if so conjoined the query with .= ..

Code:

$query = "SELECT stock_number, manufacturer, model, price, vin, year FROM #__inventory WHERE ";

                                if( isset( $_POST['stock_number'] ) ) {

                                        $query .= "\n `stock_number` LIKE '%$search%'";

                                }

                                if( isset( $_POST['manufacturer'] ) ) {

                                        $query .= "\n`manufacturer` LIKE '%$search%'";

                                }

                                if( isset( $_POST['model'] ) ) {

                                        $query .= "\n || `model` LIKE '%$search%'";

                                }

                                if( isset( $_POST['price'] ) ) {

                                        $query .= "\n || `price` LIKE '%$search%'";

                                }

                                if( isset( $_POST['vin'] ) ) {

                                        $query .= "\n || `vin` LIKE '%$search%'";

                                }

                                if( isset( $_POST['year'] ) ) {

                                        $query .= "\n || `year` LIKE '%$search%'";

                                }

It failed, since if some weren't checked then the || voided everything.

Can anyone help advise on this?

|| doesn't work in mysql, use OR

Ok, thanks. Actually figured this out with the help of someone... Here was the solution...

Code:

$where = array();

                                

                                if( isset( $_POST['stock_number'] ) ) {

                                        $where[] = "`stock_number` LIKE '%$search%'";

                                }

                                if( isset( $_POST['manufacturer'] ) ) {

                                        $where[] = "`manufacturer` LIKE '%$search%'";

                                }

                                if( isset( $_POST['model'] ) ) {

                                        $where[] = "`model` LIKE '%$search%'";

                                }

                                if( isset( $_POST['price'] ) ) {

                                        $where[] = "`price` LIKE '%$search%'";

                                }

                                if( isset( $_POST['vin'] ) ) {

                                        $where[] = "`vin` LIKE '%$search%'";

                                }

                                if( isset( $_POST['year'] ) ) {

                                        $where[] = "`year` LIKE '%$search%'";

                                }

                                $where_size = count( $where );

                                $q_where = null;

                                

                                for( $i = 0; $i < $where_size; $i++ ) {

                                        $q_where .= "$where[$i] OR ";

                                }

                                $q_where = substr( $q_where, 0, -4 ); // remove the last " OR ";

As a simple example you are able to concatenate the string using the following operator .=

php Code:

$szSQL = "SELECT * FROM members WHERE ";
$szSQL .= sprintf("name = '%s' ", 'Wildhoney');
$szSQL .= sprintf("AND dob = '%d-%d-%d'", 10, 10, 1985);
echo $szSQL;