Hi
My Search page contains - Product Name, Manufacturer Name, Model Name, Keywords
Form name - search_pd, and GET method.

Result page contains following fields: prod_id, feature, man_name, model_name, prod_image

SQL: SELECT prod_id, prod_name, man_name, model_name, prod_feature, prod_image, keywords
FROM products
WHERE prod_name LIKE '%MMColParam1%' AND man_name LIKE '%MMColParam2%' AND model_name LIKE '%MMColParam3%' AND keywords LIKE '%MMColParam4%'

Variables-MMColParam1, Default value-%, Run Time Value-Request.QueryString("prod"
Variables-MMColParam2, Default value-%, Run Time Value-Request.QueryString("man"
Variables-MMColParam3, Default value-%, Run Time Value-Request.QueryString("model"
Variables-MMColParam4, Default value-%, Run Time Value-Request.QueryString("key"

SQL in Code: "SELECT prod_id, prod_name, man_name, model_name, prod_feature, prod_image, keywords FROM products WHERE prod_name LIKE '%" + Replace(rssearch__MMColParam1, "'", "''" + "%' AND man_name LIKE '%" + Replace(rssearch__MMColParam2, "'", "''" + "%' AND model_name LIKE '%" + Replace(rssearch__MMColParam3, "'", "''" + "%' AND keywords LIKE '%" + Replace(rssearch__MMColParam4, "'", "''" + "%'"

With this code the result comes empty, No results found. MyDB holds 50 records.
But I don't find any error.

What could be the possible problem in page? And how can I achieve the result?

Please any friend advice me, how can I get it right.

Many thanks for your time.


Babu

Edited by - babui on 02 Dec 2004 15:36:31

Edited by - babui on 02 Dec 2004 17:02:13

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Hi
My Search page contains - Product Name, Manufacturer Name, Model Name, Keywords
Form name - search_pd, and GET method.

Result page contains following fields: prod_id, feature, man_name, model_name, prod_image

SQL: SELECT prod_id, prod_name, man_name, model_name, prod_feature, prod_image, keywords
FROM products
WHERE prod_name LIKE '%MMColParam1%' AND man_name LIKE '%MMColParam2%' AND model_name LIKE '%MMColParam3%' AND keywords LIKE '%MMColParam4%'

Variables-MMColParam1, Default value-%, Run Time Value-Request.QueryString("prod"
Variables-MMColParam2, Default value-%, Run Time Value-Request.QueryString("man"
Variables-MMColParam3, Default value-%, Run Time Value-Request.QueryString("model"
Variables-MMColParam4, Default value-%, Run Time Value-Request.QueryString("key"

SQL in Code: "SELECT prod_id, prod_name, man_name, model_name, prod_feature, prod_image, keywords FROM products WHERE prod_name LIKE '%" + Replace(rssearch__MMColParam1, "'", "''" + "%' AND man_name LIKE '%" + Replace(rssearch__MMColParam2, "'", "''" + "%' AND model_name LIKE '%" + Replace(rssearch__MMColParam3, "'", "''" + "%' AND keywords LIKE '%" + Replace(rssearch__MMColParam4, "'", "''" + "%'"

With this code the result comes empty, No results found. MyDB holds 50 records.
But I don't find any error.

What could be the possible problem in page? And how can I achieve the result?

Please any friend advice me, how can I get it right.

Many thanks for your time.


Babu

Edited by - babui on 02 Dec 2004 15:36:31

Edited by - babui on 02 Dec 2004 17:02:13
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>


Found a small discrepency in

With this "SELECT prod_id, prod_name, man_name, model_name, prod_feature, prod_price, prod_price_vat, keywords
FROM products"

I can test the result and found my records from database.

But when I add this lines:

WHERE prod_name LIKE '%MMColParam1%' AND man_name LIKE '%MMColParam2%' AND model_name LIKE '%MMColParam3%' AND keywords LIKE '%MMColParam4%'
ORDER BY man_name

It does not show any records.

Look forward from any friend. Help


Babu

does all four of your search inputs have to be in the database for a result to return? If not, instead of using AND try using OR. This might help.

Hi Simon

Thanks a million. Its working but bringing all the records together.

What else I can do for not bring all records.


Babu

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Hi Simon

Thanks a million. Its working but bringing all the records together.

What else I can do for not bring all records.


Babu
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>


Yes now its working fine. But a little bit problem - How can I make combination - Like Product Name - Manufacturer Name - Model Name - Keywords

Currently when I choose - First three - its filtering with only Product Name

Look forward from you Simon.

Babu

Babu,

Not quite sure what you want to do. Do you only want results to be returned based on them matching 3 categories?

Thanks Simon,

Well actually, I am using a Dynamic Drop Down Menu Extension. With these I made three drop down menu -

Products
Manufacturer
Model
---Keywords---(In a Text Box)

The extension helps me - to bring data automatic - like when select Products - then second menu brings all the manufacturer available, then when I select manufacturer it brings all models available. Without selection first one you can't select second menu.

Well these is the menu configuration. So right now, If I select Products (e.g. Mobile phone) Result comes all Mobile phone. But when I make Combination with Products and Manufacturer, it does not filter. Result comes just only what ever product I selected.

Finaly I wanted make combination -

If I select First menu - Result should be Positive
If I select 2nd menu - Result should be Positive (With Products + Manufacturers)
If I select 3rd Menu - Result should be Positive (With Products + Manufactuerers + Model)


This is what actualy I am hoping to get.

Thanks a lot for your help Simon




Babu

Edited by - babui on 02 Dec 2004 21:57:25

If you're selecting from drop down menu's then you shouldn't need to use the LIKE keyword in your SQL. Assuming your drop down menus are populated from your database tables then when you select the text description (labels), in the background the values for that item are being passed. e.g. you may select Mobile Phones in the combo box, but the ID number for Mobile Phones should be sent to the next script; in otherwords it is an exact match and no LIKE is required.

Does the user have to complete ALL combo boxes before they can search? IFF then you can use the keyword AND when building your SQL because you are asking for results where value 1 = MMColParam1 AND value 2 = MMColParam2 etc... But if your user only submits from 1 combo box and you are looking for results where 1 AND 2 AND 3 AND 4 match you will not get any results because the user has only submitted 1 criteria. So if you want to have the option to only submit 1 or 2 or 3 values you will need to build a CASE statement which is a slightly more sophisitcated IF ELSE type conditional technique.
Check out this article for some helpful examples and info on CASE www.4guysfromrolla.com/webtech/102704-1.shtml

However if each combo box is dependent on the previous then there must be a relationship between the data in them. So you dont need to match all 3 criteria as your select can simply look for the ID value that's passed to it and build the recordset from that. Each combo box will send a different ID type, e.g. First Menu will be productID, Second Menu will be manID and Third modelID.
Each model will have a manID as a foreign key, likewise each manufacturer will have a relationship with the Product via a foreign key. e.g. if its a Nokia 6600 mobile phone then the model 6600 will have its modelID which uniquely identifies that model and from that you know that the manfacturer must be Nokia...

Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]

Hi Simon

Thanks for your advice, It is quite clear to me and understandable.

OK first The drop down menus picks the data from DB automatic, like Product name (Mobile phone) - next menu brings (Nokia, Motorolla, Siemens whatever available items) third menu brings (8210, 8220 - what ever model are availble in DB) and all items are coresponding with PID.

(Note: If I select first menu then second menu bocome active, otherwise inactive, If I select second menu then third menu become active otherwise inactive - this is how the menu works I found the menu extension in www.tecnorama.org )

I did not make saparate of my DB, tryied to make it simple and easy - so Product name - Manufacturer name - Model name - are in one table

So currently - Product Name dorp down menu brings the result correctly.

But when I choose Manufacturer name - it does not work. (But my drop down menu is showing the available manufacturers name) So this two combination - how can I do it? Here I am confuse.

Finally - A text box - Keywords - My DB has got a culum name keywords, so I wanted to bring keywords result - I have never done this before - how can bring data from DB with the TEXT BOX (like search field)

And - The combination -
Product Name (By itself - working fine)

Product Name + Manufacturer (not working)
Or
Keywords (By itself)


Thanks again Simon

Babu

I would suggest that you ALWAYS normalise your data, even for a small simple database; and also plan it out on paper before you ever turn on your computer
1. Gather information.
2. Define the database's purpose.
3. Create a list of all the data elements.
4. Divide the data elements into tables.
5. Normalise the tables.
6. Normalise new tables created during step 5.
7. Define datatypes, relationships and joins, foreign keys, and indexes.

Then
8. Create the tables using the lists as reference.
9. Enter sample data.
10. Create prototype queries, forms, and reports.

You might then
11. Let the users evaluate the product.
12. Redesign based on user feedback.
13. Build actual forms and reports.
14. Repeat steps 11 through 13 as necessary.
15. Test beta.
16. Release product.

So seriously consider redeveloping your database, getting it wrong will cause you lots of work later on.
&lt;/lecture&gt;

For your keyword searching you are on the right track. With a text box for input you should filter the recordset using the keyword LIKE to return results that are similar to the values submitted.

Is the keyword search done in conjunction with the drop down menus? e.g. select "mobile phone", then select "nokia" then nothing in the 3rd drop down but enter "colour screen in the search box? If so then the recordset you build on results.asp will need some conditional logic so that you can cater for every type of search in your WHERE. e.g. (drop down1) OR (drop down1 AND drop down2) OR (drop down1 AND searchbox) OR (drop down1 AND drop down2 AND drop down3) OR (drop down1 AND drop down2 AND searchbox)...

Alternatively build a CASE select

Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]


Edited by - ganseki on 03 Dec 2004 17:37:40

Hi Simon

I just copy and paste to my Folder all 16 Steps. Thanks for your valuable advice, it will help me to go furthur. And also in future in every steps I will follow the routine task.

What ever I learned in Web Designing, its just following tutorial and taking advice of senior peoples from internet, I never done any courses because I am student and for financial support, and every time I am learning, trying to understand step by step. And finally from DMXZone, this site gave me a lot, a lot of things I learnt from here.

WHERE. e.g. (drop down1) OR (drop down1 AND drop down2) OR (drop down1 AND searchbox) OR (drop down1 AND drop down2 AND drop down3) OR (drop down1 AND drop down2 AND searchbox

Now I am going to follow this steps, lets see what result I can find.

Will be back soon - with good result.

Thanks a lot Simon.




Babu

Edited by - babui on 03 Dec 2004 19:36:32