Hi,

I have to build a search function that can search a database and show the results of chemical products based on either a ProductName or a CASNumber. I also need for the results found to display a link to a product specification page, but a pre-built html page rather than a dynamically created 'Go to detail page'.

I'm using Access database with Dreamweaver MX 2004.

I'm a complete novice at this so it's been a bit of a painful effort :) but so far I've got my MS Access database created with a single table populated with the following fields:

(Primary key)ProductID [data type Autonumber] 1
ProductName [data type Text]ammonia solution
CASNumber [data type Text] 7664-41-7
ProductURL [data type Text] specs/ammonia-solution.html

On my page that shows the search results I've defined a recordset using the selected columns

ProductName
CASNumber
ProductURL

Filter: ProductName =
URL Parameter chemsearch (the name of the text field on the search input form)
Sort: ProductName Ascending

and I have the aforementioned selected columns as dynamic text fields on the page.

I then selected ProductURL dynamic text field and clicked the 'link' folder icon in the property inspector then 'Select file from: Data sources' and selected ProductURL from the recordset, thus setting the following link: <%=(rsChem.Fields.Item("ProductURL").Value)%>

Now this works up to a point but as I see it, I have several problems with this set up.

1. It works fine if I input an exact match product name but returns no results if I input a CAS number.
2. It only returns results for an exact match found in the database so if I input 'ammonia' instead of 'ammonia solution', it returns no results.
3. Instead of using the URL path text string from the database to link to the pre-prepared html detail page I'd like to be able to use the ProductName field as the direct link to the detail page and do away with the necessity for the complete URL string showing on the page.

Are my issues down to the way my database is designed or have I missed something on the Dreamweaver side of things?

I would be grateful for suggestions to solutions to my issues and/or recommendations for books which will show me exactly how to build site search engines in their various forms.

Thanks in advance.

Hi Jason,

Maybe this article helps: http://Imar.Spaanjaars.Com/QuickDocId.aspx?QUICKDOC=241

Cheers,

Imar

Hi Imar,

I found your article very helpful indeed thank you so much. I got my application working pretty much as I wanted. Although i worked all the way the through the code and found the principles very interesting I ended up doing something much simpler that seemed to work just as well. What really helped me was your SQL explanations (I've never used it before, having only used the simple recordset interface in Dreamweaver) and the idea of using a keywords column in the database.

Basically, in conjunction with a new KeyWords column and the existing columns I used the following statement for the Advanced recordset query:

SELECT ProductName, CASNumber, ProductURL
FROM Products
WHERE ProductName LIKE '%MMColParam%' OR CasNumber LIKE '%MMColParam%' OR Keywords LIKE '%MMColParam%'
ORDER BY ProductName ASC

with the variable:

Name Default Value Run-time Value
MMColParam 1 Request.QueryString("chemsearch")

I would have been really struggling without the benefit of your article so I'd just like to say a very heart-felt thank you once again.

The only thing I would have liked to have done better is improve the method I used to link to the product detail pages.

Using: ProductURL [data type Text] to produce links to static html pages means that although the actual links work they are visually unwieldy. E.g

specs/ion-pair-reagents-propane-sulfonic-acid-sodium-salt.html

I'd like to be able to format the text of the link to read whatever I want it to or even use an image as the link. How would I go about that? Again can you point me to a guide or book I can buy that would give me the specifics?

Thanks.

Hi Jason,

If you want a book that explains these concepts in more detail, look at Beginning Dreamweaver MX 2004. The book teaches you Dreamweaver and database-driven web site development at the same time. The article you read was written as a follow-up on that book, in basically the same style.

If you want to display other text, then you can put whatever you want between the <a> and </a> tags. I assume you use YourRecordset("ProductURL") as the href for the link, and you could use the ProductName as the description of the link.

Your solution works fine if you don't need basic AND and OR queries. If you use the search query "Jason AND Imar", your solution would find records that have the exact text "Jason AND Imar", whereas my solution would return records that had the words Jason and Imar somewhere in the text. If your criteria don't need boolean logic, your solution is indeed much less complicated and gives you exactly what you need.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: What Your Soul Sings by Massive Attack (Track 2 from the album: 100th Window) What's This?

Thanks for all you invaluable help Imar. Time for a browse through the Wrox book section.