Skip to main content
A
Anonymous
October 5, 2010
Question

Two parameters question

  • October 5, 2010
  • 2 replies
  • 5707 views

I have a database with around 1600 records as of now. I have several ways to search the db and thought i had all my bases covered. The problem is this. My employer has chosen to name several products the same name or (item number) but differing in that they are different brands or manufacturers. This poses a problem witht he way I have my search results set up obviously. When I search my db for say item number "BAB", I think I get 4 records with item# BAB returned. When I click on one of the items to view the details of the product, my code is assuming all BAB products are the same.

I need to know how to pass not only the item URL parameter, but the brandName URL parameter as well. I know how to pass URL parameters, but obviously I am doing something wrong because I cant get it to work correctly. Here is my query along with my output:


<cfparam name="URL.item" type="any" default="1">
<cfquery name="rsDetails" datasource="rlbulbs">
SELECT a.*, b.brandID, b.brandName
FROM rlbbulbs a, rlbbrand b
WHERE a.brandID = b.brandID
AND a.item = '#URL.item#'
</cfquery>

<h2>Bulb Details for Item #: <cfoutput><span class="yellowBG">#rsDetails.item#</span></cfoutput></h2>
      <p><strong><em>Description:</em></strong> <cfoutput>#rsDetails.bulbDesc#</cfoutput></p>
      <div id="detailTable"><cfoutput>
          <table align="center">
            <tr>
              <td><cfif rsDetails.image1 NEQ "">
                  <a href="images/#rsDetails.image1#" target="_blank"><img src="images/#rsDetails.image1#" alt="#rsDetails.item#" title="#rsDetails.item#"/></a>
                </cfif></td>
            </tr>
          </table>
        </cfoutput>
        <p> </p>
        <table border="0" align="center" cellpadding="2" cellspacing="3">
          <!--- <tr>
    <th scope="row">Bulb ID</th>
    <td><cfoutput>#rsDetails.bulbID#</cfoutput></td>
  </tr> --->
          <tr>
            <th scope="row">Brand</th>
            <td><cfoutput>#rsDetails.brandName#</cfoutput></td>
          </tr>
          <tr>
            <th scope="row">Price</th>
            <td><cfoutput>#dollarFormat(rsDetails.price)#</cfoutput> for <cfoutput>#rsDetails.qtyPerPrice#</cfoutput> unit/s</td>
          </tr>
          <tr>
            <th scope="row">Wattage</th>
            <td><cfoutput>#rsDetails.wattage#</cfoutput></td>
          </tr>
          <tr>
            <th scope="row">Voltage</th>
            <td><cfoutput>#rsDetails.voltage#</cfoutput></td>
          </tr>
          <tr>
            <th scope="row">Base</th>
            <td><cfoutput>#rsDetails.base#</cfoutput></td>
          </tr>
          <tr>
            <th scope="row">Glass</th>
            <td><cfoutput>#rsDetails.glass#</cfoutput></td>
          </tr>
          <tr>
            <th scope="row">Filament</th>
            <td><cfoutput>#rsDetails.filament#</cfoutput></td>
          </tr>
          <tr>
            <th scope="row">Avg Life</th>
            <td><cfoutput>#rsDetails.avgLife#</cfoutput> hours</td>
          </tr>
          <tr>
            <th scope="row">Beam Angle</th>
            <td><cfoutput>#rsDetails.beamAngle#</cfoutput></td>
          </tr>
          <tr>
            <td colspan="2" align="center"><a href="../search.cfm">Search for another bulb</a></td>
          </tr>
        </table>
      </div>


Thanks for any help in advance!

    This topic has been closed for replies.

    2 replies

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    October 5, 2010

    Well, first, ideally you should have a unique field or set of fields within the table that can be used as a primary key, and you should use that for your filtering needs. A primary key, by definition, is the thing you can look at within the table to guarantee the uniqueness of a record. If you don't have that, you should really change your database schema to include it - not just to solve this problem, but to solve other problems you don't know you have yet.

    It sounds like the set of fields "item number" and "brand name" could collectively be used as a primary key - you don't have any two items with the same item number and brand name, right? But you'd probably be better served by creating a new field specifically for use as a primary key. This kind of primary key is called a surrogate key - you (or the database) typically just make up a value. Most database servers have a field type specifically designed for this: in SQL Server, you have identity, in MS Access you have autonumber, etc. This is typically created automatically for you when you create a new record, and you then use it to refer to records within the table and guarantee their uniqueness.

    But to really understand that, you'll probably need to do a decent amount of reading. So, to solve your immediate problem, you can just filter by the brand name in your query:

    <cfquery name="rsDetails" datasource="rlbulbs">
    SELECT a.*, b.brandID, b.brandName
    FROM rlbbulbs a, rlbbrand b
    WHERE a.brandID = b.brandID
    AND a.item = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.item#">

    AND b.brandName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.brandName#">
    </cfquery>

    In the above example, I'm using CFQUERYPARAM to sanitize inputs, so that the query isn't vulnerable to SQL injection attacks. You should definitely do that too.

    Dave Watts, CTO, Fig Leaf Software

    Dave Watts, Eidolon LLC
    A
    Adam Cameron.
    Inspiring
    October 5, 2010

    Can you not add a proper primary key to the tables in question & use that instead? There are performance and maintenance considerations as well as the accessibility issues you already have here.

    --

    Adam

    A
    Anonymous
    October 5, 2010

    Hi Adam, and Dave. Thanks for the replies. Yes I do already have primary keys set up in my database. I assumed that I need to pass item number and brand name for SEO purposes correct? instead of just passing a primary key parameter that is useless for search engines like google.

    Again, as it is now, its passing a url that likes this:

    http://www.teed-younger.com/bulbsDetail.cfm?item=BAB

    but once again by passing BAB that may or may not be unique, I need a way to filter out repeats in BAB.

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    October 5, 2010

    I don't know if this is going to make that much of an SEO difference - as long as you have good markup (primarily a title) that represents the page contents, that's the most important thing. Search engines are pretty good about that stuff nowadays.

    But in any case, you can just include multiple URL parameters:

    http://www.teed-younger.com/bulbsDetail.cfm?item=BAB&brandName=WHATEVER

    Dave Watts, CTO, Fig Leaf Software

    Dave Watts, Eidolon LLC
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices