2 Replies Latest reply on May 20, 2007 1:56 PM by JohnGree

    loop query

    JohnGree Level 1
      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>
        • 1. Re: loop query
          Dan Bracuk Level 5
          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.
          • 2. loop query
            JohnGree Level 1
            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?