How to create Labels that display a single database value or a value calculated via SQL (COUNT, TOTAL, etc.)

Products:   
Areas: ASP, Perl 

In CodeCharge Studio you can create Web pages that display a single value retrieved from the database by utilizing CCDLookup function that is included in the generated programs.

For example to calculate and display the total amount of items in a shopping cart, do as follows:
1. Add a Label somewhere on the page.
2. Enter the following code into the Label's "Default" property:
ASP:
  CCDLookup("SUM(<field_name>)", "<table_name>", Empty, DB<db_connection_name>)

PHP:
The above method is not recommended for PHP because the database connection needs to be declared before it can be used. You could however create a custom PHP function (for example in Common.php), then enter the function's name into the Label's "Default" property.
An example code for such function is:
  global $DB<connection_name>;
  CCDLookUp("<field_name>", "<table_name>", false, $DB<connection_name>);



Alternatively, you can place the above code in Label's "Before Show" event as follows:
ASP:
  <label name>.Value = CCDLookup("SUM(<field_name>)", "<table_name>", Empty, DB<db_connection_name>)

PHP:
  global $DB<connection_name>;
  CCDLookUp("<field_name>", "<table_name>", false, $DB<connection_name>);


(replace the values between < > with the appropriate field, table and database connection names.)
Instead of the SUM, you can also use COUNT, MIN, MAX, FIRST, LAST and other SQL functions.


You may also use CCDLookup function to retrieve a single database value by utilizing WHERE statament, for example to retrieve the name of the user that is currently logged in:
ASP:
  CCDLookup("user_name","users","user_id=" & CCGetUserID(), DB<db_connection_name>)

PHP:
  global $DB<connection_name>;
  $temp = CCDLookUp("user_name", "users", "user_id=" . CCGetUserID(),$DB<connection_name>);


In the above example, CCGetUserID function is used to retrieve the ID of the user that is currently logged in.


Viewed 28293 times.   Last updated: 11/20/2002 4:53:30 AM