Query Questions

[brianism]

Hello all,
I am trying to pull out information from the database based on what a link is passing. But I am pulling out information I don't need.

Here are my links:

cad1.cfm?headmodel=14

cad1.cfm?headmodel=14V

And the Query:

<cfquery name="getforce" datasource="mydb">
SELECT * FROM presses WHERE model_head LIKE '#headmodel#%'
</cfquery>

Now my problem is that I need only the results with a V in it to come up when using that link and vice versa. But they come up when using the link without the V as well.

[paross1]

Setting headmodel=14 should give you results both with and without the V, and headmodel=14V should include only those with the V. Is that what you are getting?

Phil

[brianism]

quote:

---

Originally posted by: paross1
Setting headmodel=14 should give you results both with and without the V, and headmodel=14V should include only those with the V. Is that what you are getting?

Phil

---

That is correct. What I need is setting headmodel=14 to not bring up any results with the V. I don't know how to do that.

[paross1]

quote:

---

What I need is setting headmodel=14 to not bring up any results with the V. I don't know how to do that.

---

Then your query should look more like this:

SELECT * FROM presses WHERE model_head = '#headmodel#'

Of course, when using = instead of using LIKE with wildcards (%), the value in #headmodel#' must match exactly with the values in the model_head column.

Phil

[brianism]

I think what I want to to can't be done. There are more things after the 14 or 14v.

Example 14f/14fr or 14v/14vfr

So I can't query the exact match because I would have the database for things that match the following. That is why I used the LIKE statement:


cad1.cfm?headmodel=14 (14 or 14f or 14f/14fr)

cad1.cfm?headmodel=14V (14v or 14vf or 14vfr)

[Dan_Bracuk]

Your logic problem is that you are not using wildcards. Performance wise, you should scope your variables and use cfqueryparam.

[Dan_Bracuk]

exactly how are these url variables being generated anyway?

[brianism]

quote:

---

Originally posted by: Dan Bracuk
exactly how are these url variables being generated anyway?

---

Well, I am just putting the "cad1.cfm?headmodel=14" as a link so when you click it the query pulls out results according to what the headmodel equals. That's where I'm running into the problem, because I don't know what to put to just bring out 14's or 14v's. There is always going to be things after the 14 or 14V Ex. 14F or 14VFR.

[Dan_Bracuk]

Let's rephrase that question. Are these url variables hard coded? Are they query results? Are they random strings?

[brianism]

Hi Dan,
These are just hard coded variables.

[Dan_Bracuk]

It's possible that you have a bad plan and/or a bad database design. However, there is not enough information to tell.

Will these url variables eventually be coming from an anchor tag or a form field where the method = get?

[brianism]

Anything is possible. Here is what a portion of the database looks like. It is MS Access.

My hardcoded links to choose either 14...whatever or 14v...whatever

cad1.cfm?headmodel=14

cad1.cfm?headmodel=14V

Then to the action page with the query:

<cfquery name="getforce" datasource="mydb">
SELECT * FROM presses WHERE model_head LIKE '#headmodel#%'
</cfquery>

And a link to the database design of it.

<a href=" http://i20.photobucket.com/albums/b218/indigobluedreamz/db.jpg">Click Here</a>

[Dan_Bracuk]

Your task would be so much easier if you normalized your db.