[SOLVED] search results show all results from table if feild is left empty

[hidden_pearl]

Hi,

 

i am stuck with this search code ....

 

$query = " SELECT * FROM table WHERE  
     fisrt_name LIKE '$first_name%'
OR last_name LIKE '$last_name%'
OR family_name LIKE '$family_name%'

";
   

 

the problem is: i want to allow users to leave empty feilds in search form for first_name, last_name and family_name. but when a user leave any feild empty then search shows all data from table. all what i want is, that if user leaves any feild empty then it must not include for search parameters.

 

can anyone plz guid me where i am wrong or what should i do.

(plz let me know if i have not explained properly)

 

Regards,

 

 

[Daniel0]

Generate the query dynamically and only add those conditions if the user specified them via the form.

[hidden_pearl]

Generate the query dynamically and only add those conditions if the user specified them via the form.

 

can u plz exaplin a bit more.... plz write example ... thnax a lot for such a quick response.

[Daniel0]

Something like

$query = 'SELECT * FROM table';

$where = array();

if (!empty($first_name)) {
$where[] = "first_name LIKE '%" . mysql_real_escape_string($first_name) . "%'";
}

// same thing for the other fields

if (count($where)) {
$query .= 'WHERE ' . join(' OR ', $where);
}

[hidden_pearl]

 

 

alrite..thanx... let me try ///

[hidden_pearl]

i have triend this code

<?php


$query = 'SELECT * FROM upload';

$where = array();

if (!empty($fname)) {
$where[] = "fname LIKE '%" . mysql_real_escape_string($fname) . "%'";
}
if (!empty($lname)) {
$where[] = "lname LIKE '%" . mysql_real_escape_string($lname) . "%'";
}
if (!empty($fmname)) {
$where[] = "fmname LIKE '%" . mysql_real_escape_string($fmname) . "%'";
}


// same thing for the other fields

if (count($where)) {
$query .= 'WHERE ' . join(' OR ', $where);
}

php?>

 

and it gave me same result ...i.e. if user left feild empty then it shows all results

 

moreover when i enter in any feild it gives query failed error...

 

by the way rest of the main code is

 

<?php
$result = mysql_query($query) or die('Error, query failed');
$numrows =mysql_num_rows($result);
echo"<br>Total Results = $numrows<br>";


while ($db_field= mysql_fetch_assoc($result)) {
echo"ID: <b>$db_field[id] </b><br>"; 
echo"First: <b><font color=red>$db_field[fname]</b></font><br>";
echo" Last:<b> $db_field[lname]</b><br>";
echo"Family: <b>$db_field[fmname]</b><br><br><br>"; 
}
?>

 

plz help

 

[Daniel0]

Then I don't understand what you mean.

[hidden_pearl]

kindly check this link

 

LINK

 

when u try to search with any empty value then it shows all of data from "upload" table.

 

i want to set search parameters in a way that

---if user leaves first name empty then search query will select from posible results from last name and family name.

---if user leaves last name empty then search query will select from posible results from fisrt name and family name.

---if user leaves family name empty then search query will select from posible results from first name and last name.

 

right now if i left all feilds empty then instead of showing 0 results its showing all data.

 

 

[Daniel0]

I suppose you could change

if (count($where)) {
   $query .= 'WHERE ' . join(' OR ', $where);
}

to

if (count($where)) {
   $query .= 'WHERE ' . join(' OR ', $where);
}
else {
   $query .= 'WHERE 1=0';
}

 

That would result in no results if all fields are left empty. If you regard all fields empty as an invalid search operation, why not give the user an error instead though?

[hidden_pearl]

If you regard all fields empty as an invalid search operation, why not give the user an error instead though?

 

yes giving error mesage to users is the best option. but see if i fil in last name and first name is empty then still it gives error. it doesnot run query ...

[Daniel0]

What error does it give?

[hidden_pearl]

 

<?php $query = " SELECT * FROM upload WHERE  
  fname LIKE '$fname%'
OR lname LIKE '$lname%'
OR fmname LIKE '$fmname%'
OR yb = '$yb'
OR mb = '$mb'
OR db = '$db'
OR yd = '$yd'
OR md = '$md'
OR dd = '$dd'

";?>

i have set this error by myself

<?php $result = mysql_query($query) or die('Error, query failed');
?>

 

so it says "'Error, query failed" 

 

 

 

 

[Daniel0]

That is why you according to this blog post, which I've written, should not use die() like that.

[hidden_pearl]

thanx for sharing a wonderful info..

 

i have removed or die ("")

 

but still if all feilds r empty then it shows all data table in result and if one out of 3 feilds r filled then it give this message

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/xcv1001/public_html/saima/pic_upload/test3.php on line 80 and 84

 

and line 80 is

<?php $numrows =mysql_num_rows($result);

and 84 is

<?php while ($db_field= mysql_fetch_assoc($result)) {... 

 

 

 

 

[Daniel0]

I didn't mean you should scrap error handling. You could do one of the alternative I proposed:

 or trigger_error('Query failed: ' . mysql_error($db), E_USER_ERROR);

 

(assuming $db is your MySQL connection handle)

[hidden_pearl]

thanx i will definately use such piece of code...

 

but now problem is to make query successful ....  :-\

[Daniel0]

And that requires the error message from MySQL which you can get using mysql_error().

[hidden_pearl]

 

thanx for sharing this info ... i tried this code:

<?php SELECT * FROM upload WHERE  1=0
  fname LIKE '$fname%'
OR lname LIKE '$lname%'
OR fmname LIKE '$fmname%' ?>

 

and it worked by adding "WHERE  1=0"....

 

but anyway ,today from ur replies i have learnt 3 new things .... am greatful to u 4 that