Re: PHP/MySQL - beginner needs help needed with multiple field searchroutine

In the javascript:

<script>
forename: <input name="search['forename']"><br>
lastname: <input name="search['surname']"><br>
address: <input name="search['address']"><br>
hair colour: <input name="search['hair colour']"><br>
occupation: <input name="search['occupation']"><br>
another field: <input name="search['another field']"><br>
</script>

this might be something else than you used to. It puts the column names
in an array, which makes it very easy to search when you get these vars
in php (most probably in the postvars):

<?
// define an empty array
$selector_array = array();

// add all not-empty searchfields to the array
foreach($_POST['search'] as $column)
if ($column) $selector_array[] = "LIKE '%".$column."%";

// build the query
$query = "SELECT * FROM myTable ";
if (!empty($selector_array))
$query .= "WHERE " . implode(" OR ", $selector_array);

// execute query
$result = mysql_query($query);
?>

this is untested but it (or something like this) should work.

Good luck

Harrie

Ian wrote:
> Hi,
>
> Am very new to both PHP and MySQL so please go easy on me. I have a
> small database created in MySQL which contains about 10 or 12 text
> fields. What I want to be able to do is present the user with the
> capability to search the database on all its fields and then retrieve
> and display any records that match, i.e.
>
> Forename:
> Surname: SMITH
> Address:
> Hair colour: BROWN
> Occupation:
> Field 6 etc:
>
>
> So the above search would only select those records that had a surname
> field of or containing the word "SMITH" and where the hair colour field
> was "BROWN". Those fields not entered for the search would be ignored.
>
> Can anyone either show me some sample code that does the above function
> or can point me in the direction of a PHP script that I could modify?
>
> Thanks in advance.
>
>

oh sorry - the <script></script> tags should be <form></form> off course

Harrie Verveer wrote:
> In the javascript:
>
> <script>
> forename: <input name="search['forename']"><br>
> lastname: <input name="search['surname']"><br>
> address: <input name="search['address']"><br>
> hair colour: <input name="search['hair colour']"><br>
> occupation: <input name="search['occupation']"><br>
> another field: <input name="search['another field']"><br>
> </script>
>
> this might be something else than you used to. It puts the column names
> in an array, which makes it very easy to search when you get these vars
> in php (most probably in the postvars):
>
> <?
> // define an empty array
> $selector_array = array();
>
> // add all not-empty searchfields to the array
> foreach($_POST['search'] as $column)
> if ($column) $selector_array[] = "LIKE '%".$column."%";
>
> // build the query
> $query = "SELECT * FROM myTable ";
> if (!empty($selector_array))
> $query .= "WHERE " . implode(" OR ", $selector_array);
>
> // execute query
> $result = mysql_query($query);
> ?>
>
> this is untested but it (or something like this) should work.
>
> Good luck
>
> Harrie
>
> Ian wrote:
>
>> Hi,
>>
>> Am very new to both PHP and MySQL so please go easy on me. I have a
>> small database created in MySQL which contains about 10 or 12 text
>> fields. What I want to be able to do is present the user with the
>> capability to search the database on all its fields and then retrieve
>> and display any records that match, i.e.
>>
>> Forename:
>> Surname: SMITH
>> Address:
>> Hair colour: BROWN Occupation: Field 6 etc:
>>
>>
>> So the above search would only select those records that had a surname
>> field of or containing the word "SMITH" and where the hair colour field
>> was "BROWN". Those fields not entered for the search would be ignored.
>>
>> Can anyone either show me some sample code that does the above function
>> or can point me in the direction of a PHP script that I could modify?
>>
>> Thanks in advance.
>>
>>