Solved

Store customer information in MySQL-database

Posted on 2008-06-23
32
414 Views
Last Modified: 2013-12-12
Hello,

Let me first describe what I want to accomplish:
On my download page customers can download a trialversion of a softwarepackage. When they click on the link to download it, I want to grap some information from the customer. This should be a form with some multiplechoice questions so the customer can give the answers very quickly. The given answers I want to store in the MySQL-database.

What is already there?
A server with PHP and MySQL is already in place.

What do I need?
Everything after the customer presses the downloadlink. I would like to have a basic example on how to do this. The example can contain a questionair with a dummy dropdownlist, radiobutton, editfield and a "Start download"-button. Pressing the button will store the entered data in the database and start the download.

Please let me know if you need any more information.

Regards, Stef
0
Comment
Question by:Delphiwizard
  • 17
  • 15
32 Comments
 
LVL 5

Expert Comment

by:mms_master
ID: 21845897
Hi,

Just a few questions to get a better idea of what you want.

1) Do you want it so the user MUST fill in the form in order to continue to the download? or do you want it so they have the choice to skip the form?

2) What is your HTML and PHP knowledge like? Do you want a full working solution where you just edit the questions? or do you want a basic example to see how it works, so that you can create your own or modify it?

3) Do the users have accounts on your website? if so, must they be logged in to download files?

4) Do you want to take any identifiable information (E.g. email address) in which case each time a file is downloaded and questions are answered the answers can be linked to the email address so you can see who left which answers.

5) Do you want the questions to be teh same for every file?

mms_master
0
 
LVL 5

Expert Comment

by:mms_master
ID: 21846583
I have made a basic example and attached the files to this post. You will need to edit settings.php so the pages can connect to your database.

Could you still answer the questions in my last post please.
Have a look at the example (the appearance is only basic and can be changed), let me know if theres anything you would like changed in the example.

At the moment the example asks questions which are set in the HTML. (I.e. it does not retrieve a set of questions from the database it only stores the answers in the database.) The answers are stored as numbers. (I.e. if the user selects the first option a 1 is inserted in the database, if the user selects option 2 a 2 is inserted in the database and so on.) I did this to keep the size of the database to a minimum. However if you want to change the questions on a regular basis, I would have to use a different method.

NOTE: Rename the end of the attached files from .txt to .php

mms_master
download.txt
download2.txt
settings.txt
index.txt
0
 

Author Comment

by:Delphiwizard
ID: 21846919
Hello mms_master,

1. The user must fill in the questions
2. My knowledge is basically zero. But I do have a lot of programming experience. So it code is documented I will be able to change it to my needs.
3. No users don't have an account
4. Linking the data to an E-mail address is however a very good idea.
5. There is only one file-download where this needs to be done.  The questions are always the same (for now at least).
0
 

Author Comment

by:Delphiwizard
ID: 21847063
Can you also tell mu which fieldtype and length must be used in the table for each field?
0
 
LVL 5

Expert Comment

by:mms_master
ID: 21847117
Sorry forgot to tell you in my last post.
If you looking at the example I submitted above set table as follows (NOTE: the table will change for the next version because you want the email address added).

Database name: Set in settings.php
Table name: Set in settings.php

Fields:
ID (INT, auto_increment, Primary Key)
Question1 (INT, length: 2)
Question2 (INT, length: 2)
Question3 (INT, length: 2)

How urgent do you need the working solution? as Im at work at the moment, and I leave in 10 minutes...
Would it be ok to continue with this tomorrow? or do you need it asap?

mms_master
0
 
LVL 5

Expert Comment

by:mms_master
ID: 21847148
I also noticed I made a mistake in download.php.
Line 27:
<input type='radio' name='Question1' value='1' /> Yes<br /><input type='radio' name='Answer1' value='2' /> No

should be:

<input type='radio' name='Question1' value='1' /> Yes<br /><input type='radio' name='Question1' value='2' /> No

mms_master
0
 

Author Comment

by:Delphiwizard
ID: 21847161
Continue tomorrow is fine with my thanks for now.
0
 

Author Comment

by:Delphiwizard
ID: 21847459
I've uploaded the adjusted files to the server.
Storing the answers is working good.
Only downloading the actual file isn't working yet. If created a folder inside the folder where the PHP-files reside. In that folder "Downloads" I've copied the download file. Somehow the filename isn't added to the URL-address.
0
 
LVL 5

Expert Comment

by:mms_master
ID: 21853812
What is the file name of the file you want to download?

Have you modified the links index.php to match your file name?
The links need to look like:
<a href='download.php?f=YOUR_FILE_NAME'>Link Text</a>

Could you run it again, and when it gets to the page saying your download will start shortly, right click the page and click view source. Then copy that source code here for me please.

Thanks
mms_master
0
 

Author Comment

by:Delphiwizard
ID: 21853924
This is the source.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Downloads</title>

</head>
 

<body>

<a href='download.php?f=Downloads/Balanced-Plus%20Setup.exe'>Download Balanced-Plus Setup.exe</a><br />

<a href='download.php?f=Downloads/Balanced-Plus%20Setup.exe'>Download Balanced-Plus Setup.exe</a><br />

</body>

</html>
 

**** AND ****
 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<meta http-equiv="refresh" content='5' URL='downloads/' />

<title>Downloading...</title>

</head>
 

<body>

Your download will start shortly...<br />

If your download does not start after 10 seconds click <a href='downloads/'>here</a>

</body>

</html>

Open in new window

0
 
LVL 5

Expert Comment

by:mms_master
ID: 21853993
The page that starts the downloads allready looks for the file specified in a downloads folder. So by linking to "Downloads/Balanced-Plus%20Setup.exe" you are actualy linking to "downloads/Downloads/Balanced-Plus Setup.exe".

I can see that the $FileName isn't being passed to downlad2.php

Try changing the link to this first:
<a href='download.php?f=Balanced-Plus%20Setup.exe'>Download Balanced-Plus Setup.exe</a>

See if anything changes. If it doesn't could you do right click > view source for both the page that asks the questions, and the page that starts the download.

Thanks
mms_master
0
 

Author Comment

by:Delphiwizard
ID: 21854033
No change.


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<meta http-equiv="refresh" content='5' URL='downloads/' />

<title>Downloading...</title>

</head>
 

<body>

Your download will start shortly...<br />

If your download does not start after 10 seconds click <a href='downloads/'>here</a>

</body>

</html>

Open in new window

0
 
LVL 5

Expert Comment

by:mms_master
ID: 21854264
hmm strange, it was working fine on my server...
Could you right click > view source on the page that displays the questions please.
Or if the files are on a live server perhaps you could give me a link so I can have a look myself?

p.s. Im working on the changes you wanted at the moment (i.e. adding email and making sure user fills in form etc)

thanks
mms_master
0
 

Author Comment

by:Delphiwizard
ID: 21854291
0
 
LVL 5

Expert Comment

by:mms_master
ID: 21854851
Bare with me, im working on it as fast as I can. Realy busy day at work today, everytime I sit down to work on this the phone rings or someone calls me on the radio.

Instead of trying to fix this at them moment as your not going to be using that version, I will just continue with what Im working on and see if the same problem occurs with that.

mms_master
0
 

Author Comment

by:Delphiwizard
ID: 21855587
It's fine with me. I realy appreciate your help and I'm not in a hurry.

Regards, Stef
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 5

Accepted Solution

by:
mms_master earned 500 total points
ID: 21856734
Try this.

Delete your old table and create the following tables:

//Email table
ID (INT, auto_increment, Primary Key)
Email (VARCHAR, length:255)

//Questions table
ID (INT, auto_increment, Primary Key)
EmailID (INT)
Question1 (INT, length: 2)
Question2 (INT, length: 2)
Question3 (INT, length: 2)

Rename the attached files from .txt to .php and replace your existing files with them.
Edit settings.php

mms_master
index.txt
settings.txt
download.txt
download2.txt
0
 
LVL 5

Expert Comment

by:mms_master
ID: 21857302
Just thought id point out that the field EmailID in the tables I said to create is email id. (The lower case l and upper case i look the same...)

mms_master
0
 

Author Comment

by:Delphiwizard
ID: 21860733
Thank you very much, tommorow I will test everything and let you know.
0
 

Author Comment

by:Delphiwizard
ID: 21862899
Goodmorning,
Everything works except that there are always two records added in the questions-table. Hereby it doesn't matter if the Email already existed or not.
0
 
LVL 5

Expert Comment

by:mms_master
ID: 21863332
ok, will look into that.

mms_master
0
 
LVL 5

Expert Comment

by:mms_master
ID: 21863898
Do you mean it always adds 2 sets of questions even if it is the first time an email address has been used? or do you mean if you use the same email address more than once it duplicates the questions but not the email address?

mms_master
0
 

Author Comment

by:Delphiwizard
ID: 21865335
Do you mean it always adds 2 sets of questions even if it is the first time an email address has been used?  YES
0
 
LVL 5

Expert Comment

by:mms_master
ID: 21865516
I had another look through my code. There doesn't seem to be anything that could cause this. I cant see anything that would cause the query to be executed twice... and the query only inserts one record at a time. Im a bit puzzled... Just thinking of silly things now. You didnt press back or refresh? (even by mistake)

It works fine on my server aswell, which also suggests the code is ok. I will use the link you gave me now, and try it with this email address: mms_master@experts-exchange.com (which is obviously fake, but its only for testing). Could you please take a screen shot of both tables after my record appears and submit them here. (You can hide your email addresses if you want)

thanks
mms_master
0
 
LVL 5

Assisted Solution

by:mms_master
mms_master earned 500 total points
ID: 21865585
I think I might have discovered the problem...

Im using Internet Explorer 7. When the download starts I get the bar at the top of the page saying "To help protect your security, Internet Explorer blocked this site from downloading files to your computer. Click here for options..." If you then click the bar and press "Download File..." it will refresh the page to restart the download.

Did you get this bar? and did you then press it and click "Download File"?

(Attached a picture of what I mean)

mms_master
IE7.JPG
0
 

Author Comment

by:Delphiwizard
ID: 21866463
Yes I did press that bar and clicked on Download file too.
0
 
LVL 5

Expert Comment

by:mms_master
ID: 21866542
Ok, that means that the code is working. When you press "Download File" internet explorer refreshes the page (automatically resubmitting the questions. Hence why you are getting duplicate question entries).

I can change the script in one of 2 ways to solve this problem.

1) If an email address has been used before, dont inserts any answers for that address (even if the answers are different to last time)

2) If an email address has been used before, insert answers ONLY if the answers are different to the last asnwers inserted for that email address.

This will sove the problem because when the user clicks "Download File" and the page gets refreshed, it will check if the email address has been used (which it will have), and then follow one of the steps above to stop the same answers being inserted again. Does that make sense?

Which method do you want?

mms_master
0
 

Author Comment

by:Delphiwizard
ID: 21866620
Wouldn't it be more correct when adding the data into the database would be done at a different place in the process in such a way that data will never be added more than once?
If the above isn't possible then I would opt for option 2.
0
 
LVL 5

Expert Comment

by:mms_master
ID: 21866751
>>Wouldn't it be more correct when adding the data into the database would be done at a different place in the process in such a way that data will never be added more than once?

This is possible. The code for inserting a record would have to be placed in a file that does not start any downloads. It cant be placed in download.php because the form hasnt been submitted then and it wont have anything to insert. If I was to create another file for inserting the record you would see something like this:

Click download link > Answer questions > See blank page > Get download blocked bar (click Download File) > Download begins

(Notice the blank page)

and your files will be:

index.php
download.php
download2.php
download3.php  <<<< Adding an extra file (which will display a blank page and then redirect)

So yes it is possible, but it means displaying the blank page and slightly increasing execution time (due to having to load another page).

Let me know what you want to do.

mms_master
0
 

Author Comment

by:Delphiwizard
ID: 21867068
In that case I prefer to have it like option 2 please.
0
 
LVL 5

Assisted Solution

by:mms_master
mms_master earned 500 total points
ID: 21872899
Try this.

mms_master
download2.txt
0
 

Author Closing Comment

by:Delphiwizard
ID: 31469715
Thank you very much, this is exactly what I needed.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article discusses how to create an extensible mechanism for linked drop downs.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now