Help with multiple parameter search recordset

[bigleo23]

I have a mysql table being searched by two fields City and State
using dreamweaver, I have set up a form with a text field as city and a list as state and I have them being passed with the get method.
The problem is on the results page, I have a recordset SELECT * FROM table name. What do I do with the WHERE, I have tried multiple configurations, and I can't get anything to work. I only want to search by two fields, city and state. The state has to match, but if the city is left blank, I still want to filter by state.

Can somebody help me or point me in the right direction, please?

[bigleo23]

I also wanted it known that I am using php

[bigleo23 · Accepted solution]

Thank you, but I figured my problem out and I no longer need help.

[Newsgroup_User]

bigleo23 wrote:
> Thank you, but I figured my problem out and I no longer need help.

It would be great if you could post your solution, as someone else may
benefit from it when they come across your post.

Steve

[SaigonJeff]

Yes... Please... I am having a similar problem but I am trying to search 3 columns usinga form with a single text field and a dropdown menu to filter the catagory.

Any ideas you could offer would be extremely appreciated.

Thanks in advance.

[Newsgroup_User]

SaigonJeff wrote:
> Yes... Please... I am having a similar problem but I am trying to search 3
> columns usinga form with a single text field and a dropdown menu to filter the
> catagory.
>
> Any ideas you could offer would be extremely appreciated.
>
> Thanks in advance.
>

What server side language are you using? What database are you using?

Steve

[SaigonJeff]

Oops... sorry!

I'm using PHP / MySQL

PHP 4.3.4
MySQL 4.1

Thanks for the help...

[Newsgroup_User]

SaigonJeff wrote:
> Oops... sorry!
>
> I'm using PHP / MySQL
>
> PHP 4.3.4
> MySQL 4.1
>
> Thanks for the help...

I was going to suggest a Stored Procedure approach, as that is how I
always approach filtering, but I use MS SQL, and I don't know if/how to
do it in MySQL.

The first part is fairly easy:

SELECT *
FROM yourTable
WHERE Column1 = MMColParam OR Column2 = MMColParam OR Column3 = MMColParam

Obviously this will match the entire value of MMColParam to the entire
value of any 3 of the columns. If you want to latch the value of
MMColParam to any part of the columns contents, you need to do this:

SELECT *
FROM yourTable
WHERE Column1 LIKE '%' + MMColParam + '%' OR Column2 LIKE '%' +
MMColParam + '%' OR Column3 LIKE '%' + MMColParam + '%'

This doesn't include the filter. How do you want to the filter to work?

Steve

[SaigonJeff]

Hi Steve... Sorry for the long time in replyig... been away on business.

Anyway to answer your question, I would like the input field to accept all or part of a product name or description, and the dropdown list to narrow the search within a specifc category.

Thanks for the help.