Two parameters question

Report · Oct 05, 2010

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">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!

Report · Oct 07, 2010

Your priorities are way out of whack if you value having product names in the URL over sound database design.

If you MUST have the product name in the URL, go ahead and append it. That does not mean that your programming code needs to make use of it. Pass both the database ID for the product AND the product name and tell your boss that all is well.

In other words, do the job right first, and then add whatever tacky BS the boss wants later.

I absolutely loathe working for people who think keywords are king. What a hassle.

Report · Oct 07, 2010

lol well since he pays my salary, I am obliged to do what he requests. I think now with the suggestions here about adding the urlencode function, my code and database are much more secure. But again, if he wants the product name in the url, that paycheck he gives me on a regular basis says I need to do both..create good code AND make him happy...lol

And yes he thinks keywords ARE the king! lol and again who am I to argue. The results he has garnered on his static site speak for themselves. SO I have had to incorporate keywords in my database design. lol

I think the true issue is his naming convention for his products. Thats what I need to work around.

Report · Oct 07, 2010

lol well since he pays my salary, I am obliged to do what he requests.

I think given he's paying you money to provide a professional service, you're obliged to give him professional advice, and offer the best solution you can. If this means correcting some misunderstanding about how this stuff works, that's part of your obligation.

But, then, having done all that if he's a muppet and says "yeah but do it my way anyhow", then you do it his way anyhow. You've at least done your job as professionally as possible in the mean time.

That said, if you do change any of your URLs (for any reason), make sure you set up some redirects from the old URLs to the new ones, otherwise you will have a hit to your SEO.

--

Adam

Report · Oct 07, 2010

Adam Cameron. wrote:
lol well since he pays my salary, I am obliged to do what he requests.
I think given he's paying you money to provide a professional service, you're obliged to give him professional advice, and offer the best solution you can. If this means correcting some misunderstanding about how this stuff works, that's part of your obligation.
But, then, having done all that if he's a muppet and says "yeah but do it my way anyhow", then you do it his way anyhow. You've at least done your job as professionally as possible in the mean time.
That said, if you do change any of your URLs (for any reason), make sure you set up some redirects from the old URLs to the new ones, otherwise you will have a hit to your SEO.
--
Adam

Well thats pretty much what has happened..lol Our first meeting involved me going over how keywords were somewhat outdated, but I had included them on my CF version of his site(the demo I worked up to get the job) and used a cfinclude to display the keywords based on the each product.

Since coming to work for him, he has insisted that if the product names arent in the url, google cant index them or rank them, regardless of the fact that I have used them in page titles, image alt and title tags, and text throught the pages.

As far as changing urls and redirects, there is nothing to redirect I assume, since this site...my site I have built for him isn't live yet. Well I have it hosted, but have it blocked as of now from search engines.

One thing though is, I know very little about SEO other than the basics and common knowledge techniques. So as I am trying to learn more about that, I am trying to incorporate those techniques into the site design.

Report · Oct 07, 2010

You are doing him a greater service by doing the job right rather than doing what he asks. Grow a pair and be the professional he pays you to be.

If you pass both the ID number and the keywords he wants at the end of the URL, you will both be happy. Your database will work based on the ID number in the database, and he will get a warm fuzzy feeling from seeing the product name in the URL.

It sounds like he read the book "Search Engine Optimization For Dummies" at some point in the last century and thinks the same games work in today's world. That ain't always the case.

Report · Oct 07, 2010

tclaremont wrote:
You are doing him a greater service by doing the job right rather than doing what he asks. Grow a pair and be the professional he pays you to be.
If you pass both the ID number and the keywords he wants at the end of the URL, you will both be happy. Your database will work based on the ID number in the database, and he will get a warm fuzzy feeling from seeing the product name in the URL.
It sounds like he read the book "Search Engine Optimization For Dummies" at some point in the last century and thinks the same games work in today's world. That ain't always the case.

Thanks for the slap across the back of the head...lol

And to be clear, I am passing the ID and product name in the url so he is happy! Well at least I think he is. lol

As for being the professional he's paying me to be, I am just fresh out of tech school at age 44 lol, so at this point I'm a professional in training who has alot to learn! I can suggest different ways and ideas and standards from my very limited knowledge in certain areas, but at the end of the day, he has a system in place for HIS site that works seeminly very well, so it's a little harder for a rookie like myself to argue or debate his successful methods. I am just trying to code the NEW site, which will be run parellel to his static site for sometime, with a balance between what he wants and whats best for coding purposes, whether he believes in them or not.

I think I demonstrated my "pair" lol with our initial meeting by telling him that keywords were outdated which he uses, that tables were silly and obsolete for page layout which he uses, and that external CSS stylesheets have been common and the standard for quite sometime now.

Report · Oct 07, 2010

A few Google searches for products should disabuse him of that notion. Google does just find indexing pages that don't have them. That said, they probably do make some difference, even if it's not a significant one, so if he wants them you can include them. But in any case I'd recommend using the primary key in addition, and actually using that for your lookups. It'll work a little better, and will allow you to have duplicate item names and brand names if any exist.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Dave Watts, Eidolon LLC

Report · Oct 05, 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