[Miben]

Im stuck to do a proper Search Function.
I need to do searching base on few criteria.

For example, book title, book serial number, book specific code number and so on....

Then way I use in my php code is,
if(!empty(book tiltle)&& !empty(book serial number) && !empty(book specific code number) && ...){
// sql code here
}
else if(!empty($something) && ...){
//sql code here
}

... ...

The problem is, if i have more 3 or more criteria need to be consider in, then i need to write a lot of if...else to determine which sql query and it will become more messy in coding.

Anyone, pls help me to solve my situation more organize or proper way????
pls help!!!

[landing]

Code:

SELECT * FROM `table` WHERE `book` LIKE '%$book_title%' AND `serial_number` LIKE '%$book_serial_number%' AND ...

... and so forth.

By using 1 query with the LIKE operator, it allows the user to perform a custom search. If they leave a field blank it will act as a wildcard character effectively ignoring the field.

If the user enters a book title and leaves all other fields blank, it will simply search for the book. It's quite good as it allows easy expansion and gives the user more control.

The downside is that users can be idiots... but that's another story.

[Miben]

Thanks for your reply..

But, how if I got another text field for user to search by keyword???
In this keyword, user can key in any word they like.

This is the way i do:

SELECT * FROM `table` WHERE book_id='%keyword%' OR book_name='%keyword%' OR book_type='%keyword%' AND book_title= '%$book_title%' AND serial_number='%$book_serial_number%' AND book_type="%book_type%" AND... so on

But, the search result i get by using the query above doesnt return exactly or aproached. The result seem like ignore all "AND" in my query.Means, no matter I put book_title, serial_number ..or not, it doesnt filter the book i search..

Any idea???

[Jcbones]

Group them...

Code:

SELECT * FROM `table` WHERE (book_id='%keyword%' OR book_name='%keyword%' OR book_type='%keyword%') AND book_title= '%$book_title%' AND serial_number='%$book_serial_number%' AND book_type="%book_type%" AND... so on

edit: srry, your syntax is wrong, only use wildcard (%) with LIKE.

Code:

SELECT * FROM `table` WHERE (book_id LIKE '%keyword%' OR book_name LIKE '%keyword%' OR book_type LIKE '%keyword%') AND book_title LIKE '%$book_title%' AND serial_number LIKE '%$book_serial_number%' AND book_type LIKE '%book_type%' AND... so on

[dgreenhouse]

Here's a possibility:

Code:

<?php

$_POST['book_title'] = 'Bambi does Hawaii!';
$_POST['book_sn'] = '12342';

$criteria = array(
  'book_title',
  'book_sn',
  'book_code'
);

$query_base = "SELECT from table";

$where = '';

foreach($criteria as $criterion) {
  if (isset($_POST[$criterion])) {
    if (!empty($where)) {
      $where .= ' AND';
    } else {
      $where .= ' WHERE';
    }
    $where .= ' ' . $criterion . ' LIKE "%' . $_POST[$criterion] . '%"';
  }
}

$query = $query_base . $where;

print $query;

?>

You can embellish it with ON ORDER based on which WHERE clauses get appended later.

Adding OR or parenthetical logic; which could be done using the $_POST or $criteria array(s), makes it a bit more complicated, but that's for another post.

[Miben]

Thanks for help...