search mysql with more than one full/part word

[krisbadsg]

i am having the database consists of morethan one lac rows. we have a search option

in our website to search the database for required information. the present code is

like this:

 

form.php

 

`<input type="radio" name="tag" value="city" /> CITY <br/>`

`<input type="radio" name="tag" value="name" /> NAME OF CUSTOMER <br/>`

`<input type="radio" name="tag" value="amount" /> CHEQUE AMOUNT <br/>`

`<input type="radio" name="tag" value="somethingelse" /> some thing else

`

`Enter the part of any of the above Here :<input type="text" name="value" />`

 

 

search.php

 

`$tag = $_POST['tag'];`

`$value = $_POST['value'];`

 

`$query = "SELECT * FROM database WHERE $tag LIKE '%$value%' "`

 

note: we always input the part field only.

 

 

with this some times the output comes in thousands of rows. with which we are facing

problems.

 

 

we want to search the two or more fields for getting more precise results.

 

hence i tried this form:

 

`<h3 align="center">ENTER ALL OR DESIRED ITEMS YOU WANT TO SEARCH</h3>`

`<div width="80%" align="center">`

`<input type="text" name="city" /> CITY <br/>`

`<input type="text" name="name" /> NAME OF THE CUSTOMER <br/>`

`<input type="text" name="amount" /> AMOUNT <br/>`

`<input type="text" name="somethingelse" /> SOME OTHER SEARCH FIELD </div> `

 

`$query = "SELECT * FROM database WHERE city LIKE %$city%' || name LIKE %$name%' ||

amount LIKE %$amount%' || somethingelse LIKE %$somethingelse%';`

 

it worked in the mysql console, and even in our website when we give all the

variables. but it displaying the entire database when we dont give even one field in

the search box. i tried to assign NULL to the variable which was not given. it is

also not worked. it works if any variable is replaced with NULL in the query. i

don't know how to do that.

 

 

i tried a lot of queries after searching in lot of code provider websites. but none

of them gave the desired results.hence i request you to provide me a sql query code

for search the database using all of the above fields or any two or even with one.

the code must work independent of number of fields we entered.

[solon]

you have to try and use something like the following:

 

$query = "SELECT * FROM database WHERE ";

if($city != "")
{
   $query .= "city LIKE '%$city%' OR";
}

if($name != "")
{
   $query .= " name LIKE '%$name%' OR";
}

and so on... to complete the query in that way, for the variables that have values in them.

And then execute the query command.

 

Because when the value is empty it show all results if you use "LIKE".

[bspace]

construct the WHERE statement first

 

$WHERE_STATEMENT = "";

 

if($city != "")

{

  $WHERE_STATEMENT .= "city LIKE '%$city%' OR";

}

 

if($name != "")

{

  $WHERE_STATEMENT .= " name LIKE '%$name%' OR";

}

 

etc.......

 

then run the query

 

$query = "SELECT * FROM database WHERE  $WHERE_STATEMENT";

[litebearer]

BTW don't forget about 'AND'.

 

OR will expand your results whereas AND will narrow your results.

 

Perhaps structure your page so that it allows a choice of ALL or ANY of the search words,

 

Also in the examples given, you will need to 'account for' the last OR

[bspace]

accounting for final OR

 

$WHERE_STATEMENT = array();

 

array_push($WHERE_STATEMENT, "city LIKE '%$city%'");

array_push($WHERE_STATEMENT, "name LIKE '%$name%'");

etc.....

$WHERE_STATEMENT = implode(" OR ", $WHERE_STATEMENT);

 

[krisbadsg]

you have to try and use something like the following:

 

$query = "SELECT * FROM database WHERE ";

if($city != "")
{
   $query .= "city LIKE '%$city%' OR";
}

if($name != "")
{
   $query .= " name LIKE '%$name%' OR";
}

and so on... to complete the query in that way, for the variables that have values in them.

And then execute the query command.

 

Because when the value is empty it show all results if you use "LIKE".

 

thank you very much for your reply but , i already tried this. but when a if condition is true the query is processing but OR clause is making a problem and giving an error.

[solon]

Try bspace's approach. remove the "OR" from your queries and implode it later.

 

But one thing you should do is:

echo $query;

 

to see exactly what your query looks like and find the error.

[litebearer]

we want to search the two or more fields for getting more precise results.

Would seem to indicate a use of AND rather than OR