CODEWITHSUNDEEP
coldfusion
user1211577
user1211577

Reputation:

ColdFusion increasing number count

Not really sure what to call this, this title is about as close to what I need as I can think of!

Basically, we've set up a competition for a client that assigns more entries to the competition based on how much information is provided. I know I am unclear, but let me try to explain.

In our database, there will be something like

Name | Number of Entries 
''''''''''''''''''''''''    
Josh |        3
Mike |        3
Pat  |        1
Raul |        2

There is more information than this in the database, of course, but this is a simplified version.

What I want it to do is to run some code that will update the database, assigning values based on how many entries they have, i.e.

Name | Number of Entries | Raffle Number(s)
'''''''''''''''''''''''''''''''''''''''''''
Josh |        3          |      1, 2, 3
Mike |        3          |      4, 5, 6
Pat  |        1          |         7
Raul |        2          |        8, 9

etc etc. So the code reads how many entries they have and inserts this many increasing values (based on the last value) into the database.

Let me know if you need any more clarification!

Upvotes: 2

Views: 2186

Answers (2)

Steve Bryant
Steve Bryant

Reputation: 1046

I would definitely create a table to hold entries and another to hold raffle numbers. Your code will be easier and you will have more data available to you if you need it.

Instead of incrementing numbers, just insert a new record into a table storing raffle numbers for each entry. This will use a little more space in your database, but you will have more data and easier code.

Then just use the following code to get the data as you describe

 SELECT EntryID, EntryName
        (
            SELECT  count(*)
            FROM    rafflenumbers
            WHERE   rafflenumbers.EntryID = entries.EntryID
        ) AS NumberOfEntries
 FROM   entries

Addressing a sample that came up in comments, you could then easily find an entry with a raffle number of 5.

 SELECT     EntryID
 FROM       entries
 INNER JOIN rafflenumbers
    ON      entries.EntryID = rafflenumbers.EntryID
 WHERE      rafflenumbers.Number = 5

Upvotes: 2

Seybsen
Seybsen

Reputation: 15587

Maybe this is what your're after:

<CFQUERY name="qGetStats" datasource="#yourdsn#">
    SELECT id,number_of_entries
    FROM mytable
</CFQUERY>
<CFSET counter = 1 />
<CFLOOP query="qGetStats">
    <CFSET dummy = "" />
    <CFLOOP from="#counter#" to="#counter+qGetStats.number_of_entries-1#" index="idx">
        <CFSET dummy = listappend(dummy,idx) />
    </CFLOOP>
    <CFQUERY datasource="#yourdsn#">
        UPDATE mytable
        SET
        raffle_numbers = <CFQUERYPARAM value="#dummy#" cfsqltype="cf_sql_varchar" />
        WHERE id = <CFQUERYPARAM value="#id#" cfsqltype="cf_sql_integer" />
    </CFQUERY>
    <CFSET counter = counter + qGetStats.number_of_entries />
</CFLOOP>

I don't get why you need this, but it could be the answer ;-)

Upvotes: 1

Related Questions