PDA

View Full Version : How to make a Voucher bank ?

dawilis
08-27-2002, 10:22 AM
I need to make a voucher bank.
fields are
id , catagorie , name , pic , startdate , enddate, cost
How do I set up a dynamic menu to populate based on catagories that have a voucher in them? ie if i catagorie is empty dont display that one.
then user can click on the dynamic link to populate a page with all the entries in that catagory.
whammy
08-28-2002, 02:38 AM
MyQuery = "SELECT id , catagorie , name , pic , startdate , enddate, cost FROM table_name WHERE catagorie IS NOT NULL"

I think that will do it... but you might have to be more clear on what a "voucher bank" is...
dawilis
08-28-2002, 02:48 AM
local business offfer discounts and deals on avoucher, people print the voucher they want, and redeme at the store.
thanks for the help
dawilis
08-28-2002, 03:16 AM
All fixed thanks mate
dawilis
08-28-2002, 06:01 AM
me again, this works very well except, I only want to display the groups that are populated for the menu, we have achieved that, but now only 1 from each group. so if I have 5 records within the accomodation group, I only want to display accomadation once
how can I filter out the other records
whammy
08-29-2002, 01:13 AM
MyQuery = "SELECT TOP 1 id , catagorie , name , pic , startdate , enddate, cost FROM table_name WHERE catagorie IS NOT NULL"

I think will work for ya. That should only ever select the top record though - so if you want them to be random or something, you might need to select all of them and then populate an array with the values, and then extract the "voucher" from that.
dawilis
08-29-2002, 01:33 AM
hi whammy that only displays the first record, any other thoughts
dawilis
08-29-2002, 01:37 AM
I was thinking of an array.
using an if statment to populate the array from the query then cycle through the array to find only 1 instance of each group.
and display it, But I cant do that either.
dawilis
08-29-2002, 02:02 AM
I was thinking furhter to my last thoughts
Is this the right way to go? what If I get 200, 400, 600 vouchers in my database this will take forever to bring the page up wont it?
If I make a static menu with the number of vouchers in the database next to each catagorie will at the least be faster.
raf
08-29-2002, 10:28 AM
I don't want to panic you, buth i would take a few steps back if i was you.

with a better databes design, all your problems (at least this one) will go away.

if you create a relational database with (for your problem) at least 2 linked tables, things will go faster and easier. imagen what you are doïng right now :

- you store to much data in your database
- you are creating future update/insert/delete-problems
- your slowing down your database and server by spending to much selecting-time and by building up arrays you don't need.

I would suggest you create a seccond table, containing the "categorie info" (lets say catID|catDescription|number) and you replace the content of "categorie" in your existing table with the corresponding "catID", so the tables can be linked.
Now you only have to search this second table ( "select * from table 2 etc etc" and your select query should have an INNER JOIN based on the "categorie" and the "catID". If a categorie hasn't been used, it won't appear in the resulting recordset

a lot faster. easier to manage etc


see code for example (names or in dutch, sorry). what this code does, is:
-create a form with listbox with 'on click' submit functio,
- creating a recordset with the used "soort" (name, ID (=nummer) and count of the times it occurs. (if you order it by this count, you can populate the listbox so that the most used option comes first) every "soort" has only one record.
- populating the listbow with the items of the recordset


code (VB for Access):


<body>
<form action="ven_eigenschappen_soort.asp" target="body" id="FORM1" method="post" name="FORM1">
<b>Klik op de gewenste soort.</b>
<p><select id="select1" name="soorten" size="10" onclick="submit();">
<%
dim conGranIT
set conGranIT=server.CreateObject("adodb.connection") 'connectievariabele definiëren

conGranIT.Open("provider=microsoft.jet.oledb.4.0;data source="&server.MapPath("granit.mdb"))
dim rsSoort 'variabele voor recordset declareren
set rsSoort = server.CreateObject("adodb.recordset") 'object aanmaken voor recordset

dim sql 'een variabele maken om de sql-string in te plaatsen
sql="SELECT DISTINCTROW Soorten.naam, Soorten.nummer, Count(*) AS [Count Of werkstukken] FROM Soorten INNER JOIN werkstukken ON Soorten.nummer = werkstukken.soort GROUP BY Soorten.naam, Soorten.nummer
"

rsSoort.Open sql, conGranIT

'populating listbox
do while rsSoort.EOF=false
'nummer = mumber = the value that will be posted after someone clicks on an option
Response.Write("<option value=" & rsSoort.Fields("nummer")& ">")
Response.Write(server.HTMLEncode(rsSoort.Fields("naam")) & "</option>")
'server.htmlencode transfers the text in your db to html
rsSoort.MoveNext
loop



rsSoort.Close
conGranIT.Close

set rsSoort=nothing
set conGranIT = nothing

%>
</select>
</form>
</body>


If you wan't to go this way and have further question, let us know.