|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
February 19th, 2008, 11:20 AM
|
|||
|
|||
|
Adding records together
Hello all, long time reader, first time poster ( finally registered on here lol )
Here is the problem I am having. I have build a shopping carts of sorts that stores the input into session variables based on part numbers (ie PN01, PN02, etc) After the person goes all the way through the form at the end, it shows them the product number they have built based on the choices they have made. Example PN01 = T, so if during the form they picked PN01, the product number will have T in it. So all of that works great and fine but where I am running into trouble is that I want to get them a price based on the product number they made. I already have in the DB the price for each product number and it is working. I can pull PN01 from the db, which is T, and it costs 400 bucks. So it is pulling the right info but I dont know how to add the different numbers together. Say there product number is T 0 1 0 1 L 1 - D D - I D D...It should go find the cost of PN01, which equals T, then go to PN02 which equals 0, so on and so forth, and add them all together for one big fat price...but I am stuck. Here is the code that I am using to select the price from the db based on the part number: Code:
<cfquery datasource="#db#" name="getPrice"> select price from pricing a left join pricing_cats b on a.cat=b.name where a.type = 2 and a.designator ='#session.PN01#' </cfquery> Then to display the price I have Code:
<cfoutput> #getPrice.price# </cfoutput> The pricing table has the price, and the designator(product number) for each part and the pricing_cats just has the type for sorting purposes. Let me know if anyone has any ideas or needs me to explain something else, its pretty confusing and i wrote it lol Thanks Adam //cyberjaz.net
|
|
#3
February 19th, 2008, 01:00 PM
|
|||
|
|||
|
Quote:
Like This? Code:
<cfquery datasource="#db#" name="getPrice"> select sum(price) as total_price from pricing a left join pricing_cats b ona.cat=b.name where a.type = 2 and a.designator '#session.PN01#'</cfquery><cfoutput> #getprice.total_price#</cfoutput> This is what I get: The sum or average aggregate operation cannot take a varchar data type as an argument. So change varchar to int? Also there has to be more then just a.designator '#session.PN01#' I have up to PN18 that need checked and added
|
|
#4
February 19th, 2008, 01:13 PM
|
||||
|
||||
|
change VARCHAR to DECIMAL(7,2)
Code:
... and a.designator IN
( '#session.PN01#'
, '#session.PN02#'
...
, '#session.PN18#' )
|
|
#5
February 19th, 2008, 01:27 PM
|
|||
|
|||
|
Code:
<cfquery datasource="#db#" name="getPrice">select sum(price) as total_price from pricing a left join pricing_cats b on a.cat=b.name where a.type = 2 and a.designator IN( '#session.PN01#' ,'#session.PN02#' ,'#session.PN03#' )</cfquery><cfoutput> $#getprice.total_price#</cfoutput> With the following code I am only getting the total of PN01 which is 400 bucks. PN02 and PN03 both have money values in them but they arent adding in with the first number thanks for your help thus far 
|
|
#6
February 19th, 2008, 01:30 PM
|
|||
|
|||
|
never mind!!! I wasn't redoing the whole form, so it was erasing the sessions and just keeping the last page, which is PN01....so there was nothing else to add to the number. thanks a ton!!!
|
|
#7
February 19th, 2008, 02:54 PM
|
|||
|
|||
|
so I guess I spoke too soon and its not doing what I need it to. Its not adding all of the numbers together. Its only adding some of them for whatever reason ( happens to just be the 2 highest prices, 800 and 400 ) so i keep getting 1200 for my final price and it should be much higher then that.
Ill paste the code again Code:
<cfquery datasource="#db#" name="getPrice2"> select sum(price) as total_price from pricing a left join pricing_cats b on a.cat=b.name where a.type = 2 and a.designator IN( '#session.PN01#' ,'#session.PN02#' ,'#session.PN03#' ,'#session.PN04#' ,'#session.PN05#' ,'#session.PN06#' ,'#session.PN07#' ,'#session.PN09#' ,'#session.PN10#' ,'#session.PN12#' ,'#session.PN13#' ,'#session.PN14#' ,'#session.PN15#' ,'#session.PN17#' ,'#session.PN18#' )</cfquery><cfoutput> $#getprice2.total_price#.00 </cfoutput>
|
|
#8
February 20th, 2008, 12:47 PM
|
||||
|
||||
|
for me to understand what's happening, i'd have to know a lot more about your tables and what's in 'em
perhaps you could show a few sample rows from each table, and show what result you expect the query to produce 
|
|
#9
February 20th, 2008, 12:54 PM
|
|||
|
|||
|
Quote:
well I actually figured everything out. Whenever someone makes a selection, the value gets stored in the session, which also relates to an entry in my db. For example, if someone picks a yes option for PN10, in the DB PN10 cost XX amt of money. So at the end, where i was having problems, is that i needed to concat some part numbers before i added anything. Like pn10 = 4 and pn11 =5 and in the other table in the db 45= something...but 4 and 5 by themselves dont mean anything. So i combined the parts that needed combined first, THEN, added everything together. still confusing to explain, but everything works and im pumped!!! lol thanks for your help, really appreciate it!!
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > ColdFusion Development > Adding records together |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
