close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Need Help with setting up recordset for downloads

Thread began 11/22/2009 12:40 am by bac4571381904 | Last modified 12/21/2009 2:25 pm by Eric Mittman | 3306 views | 29 replies |

bac4571381904

Need Help with setting up recordset for downloads

Hi i have your super suite and i want to create a small database driven online store that sells digital products only. I am using php with my sql in dreamweaver. I have basically set up the store connected to a payment gateway and looked at all your tutorials about setting up a digital store with digital file pro.
There is one portion of the tutorial that confuses me however. Setting up the recordset that allows the user to buy the product then download it on the confirm_success page. And the two recordsets on the download page that sets a limit on how many times the person can download the file.(rsdownloads and rsnodownloadsleft according to your tutorial). I have 3 pictures of the three database structures as an attached file. My website will NOT have user login functionality. Basically I need help with setting up the sql statements with the database fields i have attached for the recordset on the confirm_success and setting up the parameters for someone to purchase the product then it takes them to the confirm_success page where they can download the file. And setting up the two recordsets on the download page to limit the amount of times a user can download.
If you guys can help me with this hurdle then i can finally get this site launched. It would be much appreciated.
Thank You.

Sign in to reply to this post

bac4571381904

It looks like the pictures I attached are to small so i wrote out the fields for all three tables

Order Details Table
details_ID
first_name
last_name
details_orderID
details_productID
details_productname
detail_productdesc
detail_quantity
detail_price
timestamp
details_downloads
street1
zip

Orders table
orderID
first_name
last_name
orderReferenceID
ordervisitorID
ordertotal
orderdate
orderaddress
orderzip
City
State

Products Table
pro_ID
pro_name
pro_shortdesc
pro_longdesc
pro_price
pro_thumb
pro_download

Sign in to reply to this post

Eric Mittman

Thanks for the details it always helps. I have a few questions though about the setup. I'm curious if you have any part of this working now and if so how it is working for you and the result that you get. Also please describe for you how you would like this to work from the perspective of the end user. With these details I will better be able to help you get your downloads worked out.

Sign in to reply to this post

bac4571381904

I do not have the download part up at all. All I have is the shopping car connected to the payment gateway able to process mock orders and send sales receipt.. I need help with writing the recordset on the confirm success page and the paramaters i have to add to the download link and need help with the 2 recordsets on the download page it self and setting up the digital file pro behavior.

And this just occurred to me today when I do a mock order and i get a sales recepit the order number is either 1 or blank. I followed the instructions on the ecart documentation. When i looked at the blue sky database I noticed the details order ID in the details table is a foreign key from the order ID field in the orders table. It makes sense because the order ID field in the order table is the primary key and it is auto increment and im assuming the details order ID grabs the info from the orders ID field in the orders table So my question is, is there a way of creating a order ID that doesnt require the database to have any foreign keys or do they have to have that foregin key to create the order number?

Sign in to reply to this post

Eric Mittman

Getting the download server behavior setup to work with your scenario should not be too difficult but it will depend what you would like the user experience to be. So if you can please describe how you would like this to work and I can help you get it going. The tutorial is for a specific scenario that may not be the best fit for you, so if you describe what you need it to do and how it should work that would help me to get you started.

As for the order id this should be using the eCart session id binding. What is the binding that you are displaying on the pages?

Sign in to reply to this post

bac4571381904

Thank you for your quick reply, basically I want the customer to be able to click add to cart to purchase the item,Then on the cart page click the checkout button, then enter the necessary billing info on the checkout out page then it will take them to the confirm page to make sure info the imputed is correct. Then if everything goes well they will receive a sales receipt via email and on the order has been authorized page there will be a download link to download the product and when they click that, the download box should open up to save the download. And if the download box doesn't open then it will take them to the actual download page (download.php).

And this is for the other question

Order Details Table (ecart store details in database)
details_ID left bank (primary key with auto increment)
first_name
last_name
details_orderID <?php echo ((isset($_SESSION["eCart1_OrderID"]))?$_SESSION["eCart1_OrderID"]:""); ?>

details_productID
details_productname
detail_productdesc
detail_quantity
detail_price
timestamp
details_downloads
street1
zip

Orders table ( ecart store summary in database)

orderID left bank (primary key with auto increment)
first_name
last_name
orderReferenceID <?php echo session_id(); ?>
ordervisitorID
ordertotal
orderdate
orderaddress
orderzip
City
State

My database is set up with NO foreign keys and both of those tables have primary keys.

Sign in to reply to this post

bac4571381904

sorry typo I it left blank* for both of the primary keys in the tables above when doing the database manipulation

Sign in to reply to this post

Eric Mittman

To get started on this you will need to ensure that you have a user login area of the site, there will be no way to securely deliver the items to the user unless you can verify their identity.

After you have that setup you will need to ensure that your items table has the file name listed there.

You will need to make sure that you are getting the order and details stored in your db at the time of the checkout.

One the checkout success page you can put in links to the files that the user has access to, they should go to your download page with an identifier attached to them to indicate the item id. On the download page you will create a recordset that selects all from the item details table and joins the items table on the item id, you will filter it by the item id and where the downloads left is >= 1 and where the userID matches the id of the logged in user.

For the download server behavior you will trigger it on the recordset if there is at least one result. For the file itself you want to make sure to reference the file name column from your items table. This way it will ensure that the user can only download items they have purchased and that there are downloads left for them on that item. If any of these conditions are not met then the download will not occur.

You will need to ensure that you have all of these parts in place, when you get to the query on the download page if you need any further assistance or direction let me know. The way I described it here should work but if it does not or if you have trouble following I will try to help you get it working.

Sign in to reply to this post

bac4571381904

I dont want to have a login function. Basically we are going to only a sell a couple of ebooks. We are going to have a sales page then they just can click add to cart then it goes from there. On the checkout_success there will be a download link when the order has been fully authorized. To my knowledge theres no way they can access the download file unless the transaction has gone through and been authorized right? My digital files are stored outside of the webroot so they can't be browsed. So theres no need to have a login page becuase we are not going to expect returning customers a whole lot. I can understated having a login functionality for a site that sells electronics or numerous downloads. But is it required for me to have this feature or can i do without?

Sign in to reply to this post

Eric Mittman

Ok, understood. If you are not going to identify the user then you will need to have some other identifier so the user only gets the items they have paid for. You can use the stored order id and compare this to the session order id that is present. So the recordset would be about the same but you would want to filter where the order id matches the current order id and make sure you check that the transaction was successful.

Other than checking on the order id rather than the user's id the rest of this will be about the same. You would query your db where the item id matches from the items table and join the orders tables then filter on the order id and the status of the order. This should give you a recordset with the record of an item, you can use these recordset values in the download server behavior.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...