5 Replies - 1048 Views - Last Post: 08 November 2011 - 08:28 AM Rate Topic: -----

#1 glen75  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 02-August 11

How to create a database structure for member traffic report

Posted 07 November 2011 - 07:44 AM

Hello guys,i want to create a database structure for CPC program members earning report.the problem is i don't know how to show the traffic for each member matching with it's date without require a plenty of tables,i want to looping the table :

Earning and Traffic Report

No | Date | Traffic | Click | Paid |
1 1/2/2011 300 20 50$
2 2/2/2011 500 45 100$
3 3/2/2011 400 30 70$
and so on..

there will be more than hundreds user/member and this required hundreds table too.so if i have 200 users then i must have 200 database tables to track their traffic report.and each user will have some websites to reported diferrently.
for example if there is 4 usernames : Jim,Ann,Glen and John.each user have to tracked for their different websites so i have to build 4 database tables : Jim,Ann,Glen and John :

table jim :
date | traffic | click | paid | website |

table ann :
date | traffic | click | paid | website |

and call the database table with :
$show = "SELECT * FROM .'".$_SESSION['user']."'. WHERE website='$website'";


looks like it's too much if i have stored over thousands database table if i have thousands user,how to minimize it ?

Is This A Good Question/Topic? 0
  • +

Replies To: How to create a database structure for member traffic report

#2 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: How to create a database structure for member traffic report

Posted 07 November 2011 - 07:48 AM

Create a member table:

member
------
member_id
first_name
last_name

And put a foreign key form it in your existing table

traffic_report
--------------
no
member_id
date
traffic
click
paid

Something like that.
Was This Post Helpful? 1
  • +
  • -

#3 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10486
  • View blog
  • Posts: 38,851
  • Joined: 27-December 08

Re: How to create a database structure for member traffic report

Posted 07 November 2011 - 07:54 AM

Moved to Databases.

Foreign keys are you friend here, which will allow you to use only a few tables rather than a few hundred.
users
user_id (PK) | username | password_hash

sites
site_id (PK) | url

users_to_site
user_id (PK, FK) | site_id (PK, FK)

site_traffic
site_id (FK, PK) | date (PK, FK) | traffic | clicks | paid


Was This Post Helpful? 2
  • +
  • -

#4 glen75  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 02-August 11

Re: How to create a database structure for member traffic report

Posted 08 November 2011 - 02:38 AM

How much the maximum number of rows i can handle for one table ?
Was This Post Helpful? 0
  • +
  • -

#5 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3718
  • View blog
  • Posts: 5,989
  • Joined: 08-June 10

Re: How to create a database structure for member traffic report

Posted 08 November 2011 - 03:14 AM

Depends on a lot of things. Most importantly, the type of database you are using.

But you can safely assume to be counting that number in millions, if not more. Requires some optimizing though if you are aiming high enough to be worrying about hitting a ceiling. You'll need a lot of in-depth knowledge of your chosen system to work with that amount of data.
Was This Post Helpful? 0
  • +
  • -

#6 glen75  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 02-August 11

Re: How to create a database structure for member traffic report

Posted 08 November 2011 - 08:28 AM

Quote

users
user_id (PK) | username | password_hash

sites
site_id (PK) | url

users_to_site
user_id (PK, FK) | site_id (PK, FK)

site_traffic
site_id (FK, PK) | date (PK, FK) | traffic | clicks | paid


I have call it with php instruction :
sign.html :
<form action=signup.php method=post enctype='multipart/form-data'>
Username   :<input type=text name=username><br>
Password   :<input type=password name=password><br>
<input type=submit value=SIGN>
</form>


signup.php :
mysql_query("INSERT INTO users(username,password) VALUES('$_POST[username]','$_POST[password]')");


sitepanel.html :
<form action='submitsite.php' method='post'>
<strong>Register more site :</strong>
<input type=text name=url size=50>
<input type=submit value='Submit'>
</form>

submitsite.php :
mysql_query("INSERT INTO sites(url) VALUES('$_POST[url]')");


the result is still not work,the users_to_site table is not inserted automatically otherwise the users and sites table has been inserted with signup.php and submitsite.php.i have set the database type to innoDB and the primary key with auto increment but i don't know how to activate the foreign key format in phpMyAdmin database ?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1