How to keep the data of a user's 5 recently viewed pages?

[hotdog1983]

Hi, I have a mysql table like this

-----------------------------------------------

UserID    Email    Joined    Country

-----------------------------------------------

Justin    jj@a.com 110815    USA

-----------------------------------------------

 

What I want to achieve is to keep the 5 recent pages viewed by each user.

For example,

---------------------------------------------------------------------------------------------------

UserID    Email    Joined    Country    Act5      Act4      Act3      Act2      Act1     

---------------------------------------------------------------------------------------------------

Justin  jj@a.com 110815    USA        8.php    6.php    5.php    3.php    1.php   

---------------------------------------------------------------------------------------------------

 

So only the 5 recent pages are inserted and when the user views the 6th page,

the oldest record gets dropped and the 4 last pages moves to the right by 1 column.

---------------------------------------------------------------------------------------------------

UserID    Email    Joined    Country    Act5      Act4      Act3      Act2      Act1     

---------------------------------------------------------------------------------------------------

Justin  jj@a.com 110815    USA                      8.php    6.php    5.php    3.php   

---------------------------------------------------------------------------------------------------

 

And then the last viewed page gets inserted.

---------------------------------------------------------------------------------------------------

UserID    Email    Joined    Country    Act5      Act4      Act3      Act2      Act1     

---------------------------------------------------------------------------------------------------

Justin  jj@a.com 110815    USA        11.php    8.php    6.php    5.php    3.php   

---------------------------------------------------------------------------------------------------

 

 

I know how to use php to take the page name value to mysql query but I don't know

how to use the SQL commands to do this.

 

Does anyone know how to do this? or is there a better way to do it?

 

Some help would be great, thank you.

[joel24]

i would setup a relational database-

users; userid (auto_increment - primary key), email, joined, country

pageViews; pageView (auto_increment - primary key), page, datetime, userid

 

link users table to the pageViews table and insert the pageViews into the pageViews table - with added information such as datetime so you know when they viewed that page.

 

If you want to limit this to 5, then you can run a

$check = @mysql_query("SELECT pageView FROM pageViews WHERE user_id='{$_SESSION['user_id']}'");
if (mysql_num_rows($check) >= 5) {
@mysql_query("DELETE FROM pageViews ORDER BY `datetime` LIMIT 1");
}

//insert page view

 

I would be inclined to store all the pageviews and not commit to deleting, also I've assumed you have the user id kept in the session somewhere? if not, you can change the session value to a $_GET value or however you determine the userID you're searching for.

I would be

[The Little Guy]

I would create a table with 3 columns:

id, user_id, page

 

Then every time they view a page, insert it into the database, then to get the last 5 pages I would do this:

 

select * from history where user_id = 123 order by id desc limit 5;

[hotdog1983]

Thank you for the replies. I'll go look into table linking.

 

Both of you recommended keeping all the pageview data instread of deleting them,

I was just worried if it's going to slow down the server when the database gets big.

But it's better if I can keep them.

 

@The Little Guy, I don't understand that method. If I make 3 columns like that,

would I be able to put more than 1 data into the page column? Sorry if I got it wrong.

[dodgeitorelse]

I think The Little Guy is saying to make a table named "History" with the 3 fields of id, user_id and page. Then when a user go to a page it logs that user with new id so it would be similar to:

 

id            user_id            page

------------------------------------------

1                123                  6.php

2                123                  3.php

3                123                  1.php

4                123                  2.php

5                234                  2.php

6                345                  12.html

7                123                  15.php

8                123                  11.php

[joel24]

i think you should go with the pageviews / history table - all three of us seem to agree on the one solution;

and don't worry about slowing the database with too many records - you can set up some cronjobs later to delete any records over a certain amount for each user.

pageViews; pageView (auto_increment - primary key)' date=' page, datetime, userid[/quote']

I would create a table with 3 columns:

id, user_id, page

table named "History" with the 3 fields of id' date=' user_id and page[/quote']

[The Little Guy]

and don't worry about slowing the database with too many records

 

A good indexed table of this size should run fairly quickly with 500,000,000 rows. You should be able to get a count in less than 1 sec with that many rows.

[fenway]

i would setup a relational database-

users; userid (auto_increment - primary key), email, joined, country

pageViews; pageView (auto_increment - primary key), page, datetime, userid

 

link users table to the pageViews table and insert the pageViews into the pageViews table - with added information such as datetime so you know when they viewed that page.

 

If you want to limit this to 5, then you can run a

$check = @mysql_query("SELECT pageView FROM pageViews WHERE user_id='{$_SESSION['user_id']}'");
if (mysql_num_rows($check) >= 5) {
@mysql_query("DELETE FROM pageViews ORDER BY `datetime` LIMIT 1");
}

//insert page view

 

I would be inclined to store all the pageviews and not commit to deleting, also I've assumed you have the user id kept in the session somewhere? if not, you can change the session value to a $_GET value or however you determine the userID you're searching for.

I would be

Actually, it's much better to use INSERT ON DUPLICATE KEY UPDATE, or REPLACE with a UNIQUE KEY, if you're actually going to maintain a stack.

[joel24]

Actually, it's much better to use INSERT ON DUPLICATE KEY UPDATE, or REPLACE with a UNIQUE KEY, if you're actually going to maintain a stack.

I was thinking that though I didn't know how you could use insert, on duplicate to check that there are 5 values and then update/replace the last?

[fenway]

Read this.