4 cfquery's on one form - is there an better way to do this?

[newcow]

HI,
Just wanted to run this code by you all. I have a form that displays the details of 1 record. The ID is passed on from another cfm page and if they is nothing passed it uses ID 30. Then I have 3 querys. 1 that gets all the fields for the passed or default record and 2 that display other vehicles of the same Make and Model.
I need another query that will give me all vehicles that are within +-$2000 and seem to be having problems figuring out how to do it.
After I do that I will have 4 different querys. Is this the right way to do things.

Here is the +-$2000 that I have been trying to get to work:

Can some one point me in the right direction here?
newcow

<cfquery name="CarPrice" datasource="cardata">
SELECT tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName
FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) INNER JOIN tblDealers 
ON tblCars.Dealer=tblDealers.Dealer
WHERE tblCars.ID <> <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
AND tblCars.Model = <cfqueryparam value="#Price#" cfsqltype = "cf_sql_varchar">
ORDER BY tblCars.Model, tblCars.Year
</cfquery>

This is the working 3 querys:

<!---Set default ID = to '30' --->
<cfparam name="url.id" default="30">
<!---Query returning search results --->
<cfquery name="CarQuery" dataSource="Cardata">
SELECT tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName
FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) INNER JOIN tblDealers 
ON tblCars.Dealer=tblDealers.Dealer
<cfif IsDefined("URL.ID")>
WHERE tblCars.ID = <cfqueryparam value="#url.id#" cfsqltype = "cf_sql_integer">
</cfif>
</cfquery>	
<cfset Make = CarQuery.Make>
<cfset Model = CarQuery.Model>
<cfset carID = CarQuery.ID>
<cfset Price = CarQuery.Price>	
<cfquery name="CarModel" datasource="cardata">
SELECT tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName
FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) INNER JOIN tblDealers 
ON tblCars.Dealer=tblDealers.Dealer
WHERE tblCars.ID <> <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
AND tblCars.Model = <cfqueryparam value="#Model#" cfsqltype = "cf_sql_varchar">
ORDER BY tblCars.Model, tblCars.Year
</cfquery>		
<cfquery name="CarMake" datasource="cardata">
SELECT tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName
FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) INNER JOIN tblDealers 
ON tblCars.Dealer=tblDealers.Dealer
WHERE tblCars.ID <> <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
AND tblCars.Make = <cfqueryparam value="#Make#" cfsqltype = "cf_sql_varchar">
ORDER BY tblCars.Model, tblCars.Year
</cfquery>
<!---Query returning search results

 

[ecobb]

Unless you're working with tremendous amounts of data in the database, you shouldn't have any problem running multiple queries. I do it all the time.

Since I see that you're using <cfqueryparam>, I assume you have an enterprise level database like SQL Server or Oracle. If you feel that you're starting to overload your pages with queries, you may want to look into using Stored Procedures in your database. That way, you can have the database itself run all of your queries at one time and just return the results to CF. Then, CF would just have to make 1 call to the database with the <cfstoredproc> tag.



Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher

 

[newcow]

Ecobb: my db will have about 200 to 300 records and each record will have 40 checkbox and 20 text fields, so things are pretty small. So multi cfquery on the same form isn't a problem, great.
I am having problems with figuring out the +-$2000 query but I will repost that question.
newcow

 

[imstillatwork]

shouldn't you use cfqueryparam all of the time?

 

[ecobb]

Yes, I use cfqueryparam in every query I write. But I didn't know what database he was using, and it's been a few years since I worked with Access so I couldn't remember if it supported cfqueryparam or not.



Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher

 

[newcow]

Yes Access supports cfqueryparam and I am using Access.
newcow

 

[newcow]

Ok I am back to working on this issue. I have one more query to do on this page. I want to pull all the records, except for the current record, that are +-$2000 with in the Price of the current record.
Here is how far I have gotten with it.

<cfquery name="CarPrice" datasource="cardata">
SELECT tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName
FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) ON tblCars.Dealer=tblDealers.Dealer
WHERE tblCars.ID <> <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
AND tblCars.Price BETWEEN  ((price of #url.id#) - 20000>) AND ((price of #url.id#) + 20000)
ORDER BY tblCars.Model, tblCars.Year

Any one have any ideas how I can write the above query?
newcow

Here are the other 2 query's that I have working that pull all the vehicles of the same Make but the current vehicle and all the vehicles of the same Model except the current vehicle.

MAKE

<cfquery name="CarMake" datasource="cardata">
SELECT tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName
FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) INNER JOIN tblDealers 
ON tblCars.Dealer=tblDealers.Dealer
WHERE tblCars.ID <> <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
AND tblCars.Make = <cfqueryparam value="#Make#" cfsqltype = "cf_sql_varchar">
ORDER BY tblCars.Model, tblCars.Year
</cfquery>

MODEL

<cfquery name="CarModel" datasource="cardata">
SELECT tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName
FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) INNER JOIN tblDealers 
ON tblCars.Dealer=tblDealers.Dealer
WHERE tblCars.ID <> <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
AND tblCars.Model = <cfqueryparam value="#Model#" cfsqltype = "cf_sql_varchar">
ORDER BY tblCars.Model, tblCars.Year
</cfquery>

 

[newcow]

Well here is my update:

I have the below query up and jumping. It seems to work fine, how does it look?

newcow

<cfquery name="CarPrice" datasource="cardata">
SELECT tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName
FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) INNER JOIN tblDealers 
ON tblCars.Dealer=tblDealers.Dealer
AND tblCars.Price BETWEEN ((SELECT tblCars.Price FROM tblCars 
WHERE tblCars.ID=#url.id#) - 5000)
AND ((SELECT tblCars.Price FROM tblCars 
WHERE tblCars.ID=#url.id#) + 5000)
ORDER BY tblCars.Model, tblCars.Year			
</cfquery>

 

[newcow]

Seems like I am the only one posting on this thread but anyway here I go again.
I have 3 querys now, you can see them above, and they are working fine. The 3 query's fill in vehicles on the right side of this page

http://www.carsinlondon.com/pcardetail.cfm

under Other #Model#, Other #Make# and Other #VName +-$5000.
What I would like to be able to do is the vehicles that are in the "Other #Model#" column to not go into the "Other #Make# column; and the vehicles that are in "Other #Model#" and "Other #Make#" to not go into the "Other #VName +-$5000. Then I would like to limit the total number of vehicles that will display in these three columns to 25 vehicles so it will not go further than the bottom of the column.
How hard is this going to be to do? Is this a SQL question? I have made the other query's pretty well on my own should I just work away at them or is there some concept that I need to understand before I try to do this?
Thanks for any help!
newcow