Need Advise on upcomming development

smilinarmadillo

Hi All,

I have learned a lot reading forums and site like phpbuilder, and it makes my new journey into PHP world quite fun.

Here is the situation where i need help and i havent found any ressources related to it.

I have to build a system that will display content and video files.

The customer will be offer with 3 member package, the package will be a credit / points system.

To view video files , the customer would have to purchase a package of credit or points.

Every time the custmoer will view a video file, credit / points will be deducted from the customer account. When the credit in the account is not enough or is 0, the custmoer will be advise with a message and will have to purchase more credits or points in order to view more video file.

The Database im planning on using is Mysql and the programming language PHP4.

I need help on how this could be done / if it can be done .

I dont want the code pasted here, i just want advise from more experimented programmers on how to structure the mysql tables and what are the main important thing to look for or do in the php side.

The content management i have learned to build it, it is now working, but this part seems complicated.

Any advise, help, examples would be appreciated. If you can point me to a good reference site that concern the subject mentioned above i will also be happy.

Sincerly,

Smilin

Frederick

need

"credits" field in customers account table
credit "cost" field in the videos table

Step 1 - in the link leading to the video details or view, place a variable in the link to reference the "id" of the video.

Step 2 - create a process page:
pull the persons account and their credit balance
pull the video cost

in the process page, $sum = credits - cost;

update persons account with $sum and then place a header() function to transfer the person to the video details or view.

jimson

To : smilinarmadillo
I try to analyze your situation.
The problems you might face.
1. you said everytime the customer "will view" a video file. So are you going to deduct their point just when they start viewing and not after you're confirmed that they finished watching the video which is not a logic for me as a user to sign up. Since video is streaming, what if their line get disconnected during half of the show or the host line is to busy to provide a good streaming? They will ask you to top up back their lost points and that really a matter!

i got a solution for this problem but i am reluctant to share with you because i don't know what kinda video you are provide. Base on current market, only "Those" video (u know what i mean right!) required points or credits. If you are those "folks" then sorry i can't help you BUT if you aren't then please tell me explicitly what it is so I can convince my heart that I am doing the right thing.

smilinarmadillo

Thanks Jimson for your input.

I havent thought about the video streaming and the problem with the downloading.

I know and i am not surprised for your question :-) Yes the only time i saw a system like that was on a Erotic site. But this development im working on is not for a Erotic / Porn site, it is for
a Pool website.

Here are more precision:

The video file will be Lessons and Trick shots that will come with informative / step by step / text.

All registered member will be able to view the tutorials. But only member with credits / points in their account will be able to view video files. Since the video will , for example be 5 credit for each trick shot video, 3 credit for lesson video.

When a member desire to view a specific video, i will have to check if he is a member and if he does have credit available in the account, if so, check if he got enough to view the video. else suggest that the member get more.

Now, You brought up an excellent point about the streaming.

So if i deduct the credits after the member downloaded the file.

How will i found out he did download the file completely?

Will i have to use session or cookies and will i need special applications to track the streaming of each video ?

Pfeeww, this look overwhelming, but all the task i started with PHP looked overwhelming at the begining, with help and good advise it always clear up.

And i guess its will be hard to prevent the file from beeing saved to the member computer since most of the files will end up in the users cache, he will then just need to go in his cache to grab it?

and How many tables should i need in my DB besides the View tables and User tables.

I already have the table for the CMS set up with a video field there as well.

smilinarmadillo

Is there anymore people with the knowledge to guide me in this development?

jimson

em...

To help you, i need you to answer me with the following questions.

i wish to know your decision whether you will stream the file using the realplayer or mediaplayer or it just a typical mpeg type file or a zipped file.
can the user download directly on a given url example :
http://www.anywhere.com/video/trick1/trick1.mpg.avi.any_else
you know how use apache right? and the user authentication mode right. people usually call it .htaccess file... he he he..
you ask how many tables you need... while actually i don't know how to answer you... 🙁 ... it really depends on what functionality you wish to provide for your user. Now take out a sheet of paper and write down what functionality you wish to provide. The sooner you wrote, the sooner you will have a complete like architecture. Then using those architecture to analyze what type of data you want to save in the database.
After that group in data in the table like entry.. by then you will know how many tables you need.

My Help Statement on this thread.

remember, I try to help you because I know I am not doing something harmful to others... remember, the God will question this to you someday if you try to cheat others to help you doing something bad without their own knowledge.

smilinarmadillo

HI Jim,

Okay,

here are answers to your questions.

Q1. i wish to know your decision whether you will stream the file using the realplayer or mediaplayer or it just a typical mpeg type file or a zipped file.

A1. For now it will be WMP 9 series or all windows media player series.

Q2.can the user download directly on a given url.

A2. The video will be display in a popup window. Since im not an expert in streaming stuff, i would say that i prefer they can view directly from a given url. What are the "Pros" and "Cons" about that subject in particular?

Q3.you know how use apache right? and the user authentication mode right. people usually call it .htaccess file

A3. I havent tried anystuff with the apache rights yet. Beeing a newbie, i learn most of the stuff online. So when i finally got apache + PHP4 + mysql on my box, i start jumping on php right away and didnt experiment with .htacess yet.

I did read one or two tutorial on it, and do understand the goal of it.

So to answer i would say, i need to read and practice about .htacess. this development will be a good excercise for it. :-)

Q4. That was initially my question about how many tables i should have for this development.

I did a first draft on paper, and came up with 3 tables.

1 table to store the info about the videos
1 table to store the info about the views of those videos
1 table that stores user infos.

I already have a table for my users, i may have to modify it instead of making another table user just for the videos.

I should post the final scheme of my DB,

Thanks again Jim for your assistance on this topic.

Sincerely

Smilin :-)

jimson

if the user can download the file directly from the url, if you are not setting the .htaccess file, then anyone (with/without) credits can download it if they discover the url... (I think it is quite easy because you will follow somekinda standard to place your file and naming them)... I will see without paying...

I think it is better for you to list out all the features you wish to provide.. then probably (if I am free), I can draft a table layout for you.

em.. you should also understand session variable in order to complete your mission.

smilinarmadillo

Hi Everybody,

I decided that once completed this development, i will make a tutorial out of it, Using the reference in this topic (contribution from other user will be credited.

Ok back to the development.

For those who just jumped in the topic, go to my original post if you dont know what we are talking about :-)

Ok Jim,

This is what i have come up with as for tables. I wanted to practise so i created the tables in mysql.

So here is the functionality that i want to implement:

the Tutorial Section: section that holds tutorials of course that comes with Video file, Images and Graphics. Each Tutorial have its own set of video,image and graphic.

quick fictive situation for global example:

member points:

50 pts cost 2$
100 pts cost 4$
250 pts cost 5$

video :

Tutorial video cost 3 pts
Trick video cost 5 pts

Functionality i would like to develop :
after the logon of the member and he lands on the tutorial section

#1 - Display a list at the tutorial home page of the tutorial available (present in the db at the current time).

#2 - After the member choose one tutorial to view, it display that specific tutorial on a display page. The tutorial will come with informative content ( text ), and icons that represent ; video, images and graphic. The cost / point it takes to view will be displayed as well.

#3 - When the member clicks on images to see the tutorial images or graphic to see the graphic, it will be displayed without costing anything. When the member click the video icon/link of the video file related to the specific tutorial i want to check in the db , first if the member is registered (would be a double check), if not send him to the login page, if he is a member check how many points is in the member account.

#4 - Check to see if the member has seen that video already. If YES ,

Count the number of time the user has viewed that specific video, display the video without deducting points to their account. If NO,

check to see if the member have sufficient points to view the video file. If YES, display the video in a popup window (can be displayed on same page if it cause prob the popup). If NO, display a page or message to the member that there was not enough points in the member account to view the video.

From that situation, i would like to give the member the opportunity to either purchase more points or go back to the tutorial page.

#5 - If the member choose to purchase more points and has not seen the video file already. Display the procedure to buy more.

#6- buying procedure, add points to user account.

#7 once points added, the user will be brought back to the tutorial page he was viewing (if possible) and will be able to click the video and view it.

#8 verify that the video is beeing streamed with no problem.

#9 protect the video from beeing downloaded directly or saved on the users computer (not sure if possible)

#10 verify that the member did view completely the video.

#11 deduct from the member account the points used for the video.

#12 increase the number of time the current member has seen the video by 1 (can be used for stats purpose)

#13 Get Time and Date, IP, Username of the current Member

#14 Store the information in a table for the videoviews and table video

The trick section will work the same way.

Now after writing that down, i practise myself trying to sketch tables for this.

i came up with this on my sheet of paper.

Table Video :

id
Video Number
Video Name
Video Category
Video Cost
Number of Views (in general)

Table Video Views

id
Video id
Video Name
Number of View
Username
IP
Date
Time

Table User

id
First Name
Last Name
Username
Password
email
Date of subscribtion
Date of Activation
Last Login
Subcrisption IP
Activation IP
Last login IP
Tutorial video Viewed
Trick video Viewed
member points
banned

I have to figure out now to get this to live in perfect harmony :-)

i am really starting to enjoy php a lot and i have learned how to build a CMS and started to build it for this development as well.

The tutorial part of the CMS works now , and i get the info about the tutorial displayed. but no video section in it so far. I guess that too i will have to make some modification. But its not my main focus right now.

I hope i am not too complicated to understand.

What i have learned by writting my stuff on paper first. Its just like when u design. Its always better to get it on paper first and think about all the situation possible :-) it reduce the modifications at the end

Sincerely

Smilin

jimson

So, i just layout the possible directory structure for you.

/
	/tut
		/course1 // you may also call them from database, depend.
		/course2
		/courseN... until finish
	/img
		/course1 // you may also call them from database, depend.
		/course2
		/courseN... until finish
	/mov
		/course1
		/course2
		/courseN... until finish
	/ico

em, let say, videoA has been viewed by userA, he comeback again and you check that userA has viewed the specified video, userA points not deducted. (am i right) so what will happen if the userA leak out his login ID and password into the internet, then everybody who know it can log into your site and see the userA purchased video.

#9, it is possible once you set up the .htaccess realm.

one of your another problem is, you must set up your apache to be SSL enable if you want to process the credict card things, otherwise, find a merchandise in the web, like CCnow and process the payment like what they ask.

and i hope the column name you use in your table layout are not the name you are going to use in real app. You should make the column name simple and compact and without space, example, First Name can be rendered to

Table User
==========
FName
LName
UName
UPwd
UMail
DSub
DAct		
LastLog		<= datetime
IpSub
IpAct
IpLastLog	<= you can try to use the PHP.net country IP mechanism
TutView		<= is this going to be number or string ?
TrickView	<= is this going to be number or string?
Point		<= integer
Banned		<= i think this is boolean

ok, need any help, just post it here...

smilinarmadillo

Hi all,

Just to advise all the ones that are following this thread,

I was busy with other work and let that project aside for few days. I am now back into resuming the development of it.

So stay tune for new progress soon, and probrobably more helpful hint and advises from members.

sincerely, smilin armadillo
:-)

smilinarmadillo

Hello All,

Sorry for the delay, most of the viewers probably forgotten about this thread.

I was caught up with lot of work and had to let that project sit for a while. Bills, still have to pay them unfortunately :-\

Ok,

Most of my Content management system is completed and now has 7 modules that are related to 7 section of the site

My original post was related to my desire to build a points system assign to Registered User. (For a full brief on that project, read the first post of the thread.)

Now with the kind advises of Jimson, i have build a DB to handles the points system. Now like i mentioned before, i am a graphic designer who is still learning all the power of PHP/MySQL. I am open to all suggestion and advises. I am also looking to program the good way, and make sure i am doing everything properly.

Maybe because its my first PHP app baby, but i am starting to like my Content Management system, and as i mentionned in previous post, i will share the final App and how i did build it, so it can help others as well.

So to get back where i left, i have added to my CMS 2 more tables for the points system and made modifications to my User table as well.

So here is the structure.

(Please note that the name i am using is the description name so its is easier for everybody to understand the tables fields, the real name of the field will not be that long.,

Tutorial video table

video ID (integer)
video Number (integer)
video Name (varchar)
video category (varchar)
video cost (integer)
(will be the amount of point deducted from the User points)

Tutorial video views table

view ID (integer)
video ID (the Id of the video viewed)(integer)
view Time (the time it was viewed)(Time, or maybe Timestamp)
view Date (same thing here either Date, Datetime or timestamp)
number of view (integer)
viewed by (store the username) (varchar)

(I dont grab the IP since i already do it when they Log on to their account) Actually while i am writting this i think that maybe i should grab it again. Let say it would be optional.

User table

id (integer)
First Name(varchar)
Last Name(varchar)
Email (varchar)
Username (varchar)
Password (varchar) or maybe (password)
User Level (integer)
Signup Date(datetime)
Last Login (datetime)
Sign Up IP (here im not sure, text or varchar?)
Activation IP(here im not sure, text or varchar?)
Login IP(here im not sure, text or varchar?)
Activated (char) yes or no
User Points (integer)

The site structure looks like this:

/tutorial
/course1 // Called from the CMS DB.
/course2
/course3
/img
/course1 // so far only the names of the Pics are entered in the CMS the rest is called from the php scripts.
/course2
/course3
/mov
/course1
/course2
/course3

/ico

Now my challenge is to make this work.
Assign an ammount of point to users when they register (paypal probably)

Then establish a system that deduct points for each time the User views a tutorial video.

Now my concerns are, is it possible with a system with paypal, to verify that the user paid and which package the User took, than add the points to the DB?

How can i find out if the Video was successfuly loaded and viewed completely by the user before deducting points?

How should i manage to add points to the User DB or deduct points , whic system is the most efficient?

So im open to any guides, advises or any URL to related subject would be appreciated.

Jimson! if you are still arround, your wisdom is always appreciated :-)

Meanwhile i will try to figure some of it out with some research .

Sincerely, Smilin.

jimson

this link might be able to help u

http://www.zend.com/zend/tut/tutorial-paypal.php

smilinarmadillo

Thank you Jim,

I have check it out and of course tomorrow i will start that tutorial.
This will able me get familiar with credit card procedure. And it should points me what i need for the points system.

Meanwhile, i had a trouble with my CMS when i entered text containing "single quote". Last night i have browsed the web once again for info and solutions, but this time i found the explanation in one of my php/mysql books.

Of course those test are run on my box and i didnt want to set the Magic_quotes on, they are all disable. I want to learn the right way.

So i have stumble on
The addslashes/stripslashes,

i have apply it to all my $var that need it and it worked with no problem.

Now this raised more questions in my mind. What are the normal procedure to insert data in a mysql table.

so far before i discovered yesterday night how to play with strings with function such as strstr() strtrim() strchop()

i was using varchar() in mysql for Titles , Names, URLs ,

For small text blurb i was using varchar set to 255.

For long text the Text

Now im discovering more stuff on mysql such as BLOB etc..

Anyone cant point me a good tutorial online on how to develop the good work method to insert data/text/numbers in Mysql would be gladly appreciated.

Like i said as a php rookie i want to develop good programming habits,

i Have started with including in all my code "error_reporting(E_ALL);" , first i wanted to cry, :-) all those lines of errors or notices. With the $_POST i have fix 90% of the notice. What i like about it , it forces me to make sure my code is "programmed the right way" well at least towards that way :-)

Please all usefull URL that would serve a php/beginner like me or others.

Thanks all.

As for the points system, after i complete the Paypal tutorial i will start playing with it. But i think i may have to learn how to make mathematical calculation with php and mysql, and increment or decrement a alphanumerical value in Mysql.

Any URL ?

;-)

Smilin

smilinarmadillo

I want to add to my member system, Points that user can purchase. With those points, the user will be able to view Tutorial videos and Drill video.

I would like to be able to :

1 - Offer say, 3 or 4 different bundle of points and assign a price to each of them.

2 - At the start the user once registered does not need points to browse most of the site. Only the tutorial sections with video will required points.

with Paypal i would like to for example:

package 1 = 25 points / 5$
package 2 = 50 points / 10$
package 3 = 120 points / 15$
package 4 = 200 points / 20$

User choose the package of points they want, it brings them to paypal for payment.

Once the payment made and everything is legit and OK, i want to add the points to the User account and bring the user back to the page they was viewing.

I need to track the User ID / Username and The package they choose in order to know the ammount of points to add in the user points database.

Being a rookie i got no idea if this can be done using paypal and if so how can i track it, and can i assign extra var to the process in order to track the info.

Now i have been on paypal.com/pdn and all the example was for a shopping cart mostly for product catalogs. I dont think these example are good for what i want to do, because the User will not be able to choose more than one package. But i may be wrong. I aint an expert in this field, since it will be the first time i get involved with payment on my site.

So i need advise, tutorial , tricks on how can this be done, and if paypal is a good choice for it. if not, i would like to know why and if there is a better payment system for this.

Anyone got an idea?