On my site I'm creating a function so that people can see who the last 10 people that viewed their profile are, in my table I have UserID, ProfileID & DateTime. If 10 people have viewed a profile and an 11th person then views it, I want the oldest record in the table to be deleted and then the new record be inserted, but I don't know how to do this...

 

All my other functions such as update and insert are working but I can't find anywhere to learn this bit... I think it's a subquery that needs inserting... This is what I have so far;

 

if (isset($_SESS['id']) && ($_SESS['ProfileView'] == '1') && ($_SESS['id'] != $userID)) {

$check_ProfileView = mysql_query ("SELECT * FROM ProfileUserViews WHERE
UserID = $UID
AND ProfileID = $userID") or die(check_ProfileView);

if (mysql_num_rows($check_ProfileView) == 1) {
	$update_ProfileView = mysql_query ("UPDATE ProfileUserViews
	SET DateTime = NOW()
	WHERE UserID = $UID
	AND ProfileID = $userID") or die(update_ProfileView);
}
elseif (mysql_num_rows($check_ProfileView) == 0) {

/*
	$check_ammount = mysql_query("SELECT * FROM ProfileUserViews WHERE ProfileID = $UID") or die(check_ammount);

	if (mysql_num_rows($check_ammount) >= 10) {

	This is where the code needs to be to delete the oldest record

	}
*/

	$insert_ProfileView = mysql_query ("INSERT INTO ProfileUserViews (UserID, ProfileID, DateTime)
	VALUES
	('$UID', '$userID', NOW())") or die(insert_ProfileView);
}
}

On my site I'm creating a function so that people can see who the last 10 people that viewed their profile are, in my table I have UserID, ProfileID & DateTime. If 10 people have viewed a profile and an 11th person then views it, I want the oldest record in the table to be deleted and then the new record be inserted, but I don't know how to do this...

 

All my other functions such as update and insert are working but I can't find anywhere to learn this bit... I think it's a subquery that needs inserting... This is what I have so far;

 

if (isset($_SESS['id']) && ($_SESS['ProfileView'] == '1') && ($_SESS['id'] != $userID)) {

$check_ProfileView = mysql_query ("SELECT * FROM ProfileUserViews WHERE
UserID = $UID
AND ProfileID = $userID") or die(check_ProfileView);

if (mysql_num_rows($check_ProfileView) == 1) {
	$update_ProfileView = mysql_query ("UPDATE ProfileUserViews
	SET DateTime = NOW()
	WHERE UserID = $UID
	AND ProfileID = $userID") or die(update_ProfileView);
}
elseif (mysql_num_rows($check_ProfileView) == 0) {

/*
	$check_ammount = mysql_query("SELECT * FROM ProfileUserViews WHERE ProfileID = $UID") or die(check_ammount);

	if (mysql_num_rows($check_ammount) >= 10) {

	This is where the code needs to be to delete the oldest record

	}
*/

	$insert_ProfileView = mysql_query ("INSERT INTO ProfileUserViews (UserID, ProfileID, DateTime)
	VALUES
	('$UID', '$userID', NOW())") or die(insert_ProfileView);
}
}

I'm not sure how your database looks, so this confuses me abit.

 

But if you have a table that has info on the last visited profiles. You can just do a normal select and LIMIT the results to 10 rows.

 

Something like this:

 

SELECT my_table_column FROM `your_table` LIMIT 0, 10

That returns the first 10 rows.

 

If you want to return the last 10 rows, just add the ORDER BY keyword.

 

SELECT FROM `your_table` LIMIT 0, 10 ORDER BY my_table_column DESC

I'm not sure how your database looks, so this confuses me abit.

 

But if you have a table that has info on the last visited profiles. You can just do a normal select and LIMIT the results to 10 rows.

 

Something like this:

 

SELECT my_table_column FROM `your_table` LIMIT 0, 10

That returns the first 10 rows.

 

If you want to return the last 10 rows, just add the ORDER BY keyword.

 

SELECT FROM `your_table` LIMIT 0, 10 ORDER BY my_table_column DESC

Yeah I know but the only problem is it means my database is storing loads and loads and loads of redundant data

Not tested the query but something like this should delete the oldest record in the table each time the script runs

 

DELETE FROM `ProfileUserViews` WHERE  DateTime >'0' ORDER BY ASC LIMIT 1

 

 

The database could be weeded by date, in this example query rows older than a month are removed from the table

DELETE FROM `ProfileUserViews` WHERE  DATE_SUB(CURDATE(),INTERVAL 30 DAY)<= DateTime

After looking on the internet a lot, it seemed quite a few people were asking this question and no one seemed to know the correct answer, well after putting my head to if I've worked it out, thought I'd share it with you all for those that are interested.

 

$check_ammount = mysql_query("SELECT * FROM ProfileUserViews WHERE ProfileID = $userID") or die(check_ammount);

	$numrows = mysql_num_rows($check_ammount);

	if ($numrows > 10) {
		// If there happens to be more than 10 views, it will delete how ever many it needs too
		$toDelete = $numrows-9;
		mysql_query ("DELETE FROM ProfileUserViews WHERE ProfileID = $userID ORDER BY DateTime DESC LIMIT $toDelete");
	}
	elseif ($numrows == 10) {
		// If there are 10 views, it deletes the last view so that a new one can be inserted
		mysql_query ("DELETE FROM ProfileUserViews WHERE ProfileID = $userID ORDER BY DateTime DESC LIMIT 1");
	}

	// Inserts the new view data
	$insert_ProfileView = mysql_query ("INSERT INTO ProfileUserViews (UserID, ProfileID, DateTime)
	VALUES
	('$UID', '$userID', NOW())") or die(insert_ProfileView);

Edited November 16, 201114 yr by adamsmith