Skip to main content
A
Anonymous
May 18, 2007
Question

loop query

  • May 18, 2007
  • 1 reply
  • 222 views
Hi i have a payment table, when a user pays for our service the details get inserted into.

I have ClientID, Amount, CostPerUnit as my columns

say i have the clientID as 34 who has an Amount of 1000 and a PerUnit is 0.12

and the i have the same client 34 with Amount of 250 and a PerUnit is 0.17

what i need to do is insert the perunit cost into my records table everytime a unit has been used

ie
<cfquery datasource="#application.ds#">
insert into Records
(CientID, Cost, ClientCostPerSMS)
values
('#session.ClubLogin#', 1', UNITCOST)
</cfquery>

but before i insert it i need to know which unit cost to insert, so i have a session which tells me how many uints they have left

'#session.TotalSMSLeft#'

how can i have a query which selects the first record by ClientID and if the '#session.TotalSMSLeft#' is more than the Amount to select the next record an so on....

<CFQUERY datasource="#application.ds#" Name="GetPayInfo">
SELECT *
FROM payment_table
WHERE ClientID = #ClientIDD#
</cfquery>
This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
May 20, 2007
select amount
from payment_table
where clientid = #session.clublogin#
where amount > #session.totalsmsleft#
order by amount

You want row 1 of the result. You need more code to cope with 1st records per client and stuff like that.
A
Anonymous
May 20, 2007
ok yes that will work, but how do i code if the session.totalsmsleft is more than the amount from that query,

then i need to find the next record with the clientid and do the same query again

so if the amount inthe query is 3000
and the session is 5000

i need to do is add up the amounts until the 5000 is reached

so if i have 3 records from the same client of 1000, 3000, 2000

i need to add 1000 plus 3000 plus 2000 then the session of 5000 will be less so the record of 2000 would be the one i need to use.


do i need to do a loop?

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