Reputation: 3223

How to store Profile Views to show each user who's viewed their profile?

If we wanted to show each user which users of the opposite sex have viewed their profile, what would be the best way to keep track of all those views in MySQL?

Each user has a unique userid from the main Users table, which also stores their sex.

We would want to show each user the users that viewed them in order of most recent view to oldest view.

We would obviously not want to show the user themselves if they happened to view their own profile.

We would want to show guys only the girls that viewed them, and the girls only the guys that viewed them.

How would we setup the table of ProfileViews to do that?
What indexes would we use?
What would be the query we would need to show each user who has viewed them?

Upvotes: 1

Answers (4)

Oscar Jara

Reputation: 14187

This is a simple example that I will make for you, hope this helps.

SQL:

CREATE TABLE user
(
    user_id BIGINT NOT NULL AUTO_INCREMENT,
    sex VARCHAR(10) NOT NULL,
    CONSTRAINT PK_USER PRIMARY KEY (user_id)
) ENGINE=INNODB;


CREATE TABLE profileview
(
    profileview_id BIGINT NOT NULL AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    visitor_user_id BIGINT NOT NULL,
    date_time DATETIME NOT NULL,
    CONSTRAINT PK_PROFILEVIEW PRIMARY KEY (profileview_id)
) ENGINE=INNODB;

ALTER TABLE profileview
ADD FOREIGN KEY FK_PROFILEVIEW_USER(user_id) 
REFERENCES user (user_id);

ALTER TABLE profileview
ADD FOREIGN KEY FK_PROFILEVIEW_VISITOR(visitor_user_id) 
REFERENCES user (user_id);

PHP:

This is a simple example of the user profile page - www.domain.com/profile.php?id=xxx. At this point you need to define two variables in session when the user logs into the site: $_SESSION['user_id'] (int) / $_SESSION['user_logged'] (boolean)

<?php
if ($_GET && isset($_GET['id']){

    if(isset($_SESSION['user_id']){
       $profile_user_id = $_GET['id'];

       // the user that visits the profile has a session with his/her id on it.
       session_start();
       $visitor_user_id = $_SESSION['user_id'];
    } else {
       // if visitor specified an id but there is no session, redirect to login.
       header("location: login.php");
    }
} else {
    // if no id passed redirect to index
    header("location: index.php");
}
?>
<html>
<head>
<title>Your title</title>
</head>
<script src="scripts/jquery.js" type="text/javascript"></script>
<script type="text/javascript">
//here you will store the visit with jquery.
$(document).ready(function(){
  // store the values from php.
  var profile_user_id = <?php echo $profile_user_id ?>;
  var visitor_user_id = <?php echo $visitor_user_id ?>;

  // here, the user information goes to the visit.php file.
  $.post('visit.php' { profile_user_id:profile_user_id, visitor_user_id:visitor_user_id } );
});
</script>
<body>
Here print user information from a SQL select or something of the id passed in the GET.
</body>
</html>

Now, the visit.php file to store data:

<?php
if ($_POST && isset($_POST['profile_user_id']) && isset($_POST['visitor_user_id'])) {

    session_start();

    // this will end the execution of the script if there is no session from user logged
    if ($_SESSION['user_logged'] != true) {
      exit();
    }

    // everything is ok, do the process:
    // functions.php contains your SQL connection, I suppose you know how to do it.
    include('../cgi-bin/functions.php');
    $link = dbconn();

    $profile_user_id = mysql_real_escape_string($_POST['profile_user_id']);
    $visitor_user_id = mysql_real_escape_string($_POST['visitor_user_id']); 

    // this will store the data in profileview including date and time if id's are not equal.
    if($profile_user_id != $visitor_user_id){
       $sql = "INSERT INTO profileview (user_id, visitor_user_id, date_time) VALUES ($profile_user_id, $visitor_user_id, NOW())";
       mysql_query($sql, $link);
    }
}
?>

EXTRA: if you don't know what functions.php do, here it is:

<?php

function dbconn() { 
if(!include_once('db.php')) { 
  die('Error include file.'); 
}

if (!$link = mysql_connect($db['hostname'],$db['username'],$db['password'])) { 
  die('Error connecting.'); 
}

if (!mysql_select_db($db['database'])) { 
  die('Error selecting.'); 
}

return $link; 
}
?>

The above file will need this file too: setup here your connection parameters to your db.

db.php

<?php
  $db = array( 
   'hostname' => 'localhost', 
   'username' => 'root', 
   'password' => 'mysql', 
   'database' => 'mydb' 
  );
?>

I suggest you to put this in the cgi-bin folder of your hosting for better practices as you can see in visit.php file code.

Now, create another file called visitors.php?id=xxx and do a select * from of your profile views according to the user_id. At this point you will be able to:

Get the user_id information and if it is men (for example)...
- Select visitors by sex and do a rule to list only female visitors.
- List visitors according to the time stored in profileview table.

Upvotes: 3

David Bélanger

Reputation: 7438

UsersTable
UserID      Sex
1           Boy
2           Girl
3           Girl


UsersViewsTable
UserID      View    Unixtimestamp
1           2       342143243432
1           3       142143243432
2           3       242143243432
3           1       442143243432

When you visite the user profile, you'll use this :

IF CurrentUserSex != UserProfileSex
    INSERT INTO UsersViewsTable (UserID, View, Unixtimestamp) VALUES (CurrentUser, CurrentProfileUserID, time)

Now, you want to fetch this on a page to see last seen from opposite sex ?

SELECT * FROM UsersViewsTable LEFT JOIN UsersTable USING (UserID) WHERE Sex != CurrentUserSex GROUP BY View ORDER BY Unixtimestamp DESC

EDIT :

IF CurrentUserSex != UserProfileSex {
    $Res = SELECT CurrentProfileUserID FROM UsersViewsTable WHERE UserID = CurrentUserID AND View = UserProfileID LIMIT 1

    if($Res['Count'] == 1){
        // Exist
        UPDATE UsersViewsTable SET Unixtimestamp = time WHERE UserID = CurrentUserID AND View = UserProfileID LIMIT 1
    } else {
        // Doesnt exist
        INSERT INTO UsersViewsTable (UserID, View, Unixtimestamp) VALUES (CurrentUser, CurrentProfileUserID, time)
    }

}

Upvotes: 1

Tuhin Subhra Dey

Reputation: 980

Just check n compare for each user profile page with the visitor id and profile id. If two are different store in a visit table with date and time and your required info. Before inserting just check the table row if prof id, vistor id already exists then update the time else just insert the data.

Thanks.

Upvotes: 0

TaoJoannes

Reputation: 594

profileviews:

profile
userwhoviewed
timestamp

Index the profile column.

So when your user views the page, check if it's the profile owner, get the sex of the profile owner, check the sex of the viewer, if different, update the table with the viewer and the timestamp.

When querying the results, just select all rows matching the target profile, ordered by timestamp desc, and iterate to build your links back to those profiles.

I normally use INT data types in these fields (keeps the rows smaller and speeds up the lookups), then have a user table that generates those UID's as an auto_increment primary key. That will hold your gender and preference fields, too, as well as any other ancillary user data, and makes it easier to change login names, if desired.

But you're leaving out your gay users. Better to just log them all and let the user filter based on their preferences. :)

Upvotes: 1

How to store Profile Views to show each user who&#39;s viewed their profile?

Answers (4)

Related Questions

How to store Profile Views to show each user who's viewed their profile?