Get a random record from a database [number of records in db] - Banner Advertising

This tutorial will give you an easy way to create random banner advertising or get a random record from your database.

This is done by using RandRange that will generate a pseudo-random integer in the range between two specified numbers.

You can also use "rec_count" to find out how many records your database contains.


Let's assume that your Database looks like this:

After using the database for awhile, your database will not contain the ID numbers in correct increasing sequence. There will be "holes" in the ID keys. Example: 1,2,5,8,9,14 and so on.

First we need to find out how many records the database contains.
This is done by creating a record count.

Query the database and loop to all the records and count them.

Example:

<!--- Query the database --->
<cfquery name="Query_name" datasource="Datasource_name"  username="User_name" password="User_password">
SELECT ID FROM your_database
</cfquery>

<!--- Create a new array. --->
<cfset db_array = arrayNew(1)>

<!--- Create a variable that will count all the records in the database. --->
<cfset rec_count = 0>

<!--- Start looping the database. --->
<cfloop query="Query_name">
  <cfset rec_count = rec_count + 1>
  <cfset db_array[rec_count] = "#ID#">
</cfloop>

<!--- Use the RandRange to find a randomized record in the database. --->
<cfparam name="swap_image" default="#randrange(1, rec_count)#">

<!--- Finally output the random record. --->
<cfoutput>#swap_image#->#db_array[swap_image]#</cfoutput>

Notice:
The "swap_image" is the number of the record in the database.
The db_array[swap_image] is the randomized record ID in the database.


Example to find out how many records your database contains:

<!--- Query the database --->
<cfquery name="Query_name" datasource="Datasource_name"  username="User_name" password="User_password">
SELECT ID FROM your_database
</cfquery>

<!--- Start looping the database. --->
<cfset rec_count = 0>
<cfloop query="Query_name">
  <cfset rec_count = rec_count + 1>
</cfloop>

<!--- Output number of records in database. --->
<cfoutput>#rec_count#</cfoutput>

Advertisement

No User Comments.

No User Comments, be the first one to write your comments?