1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MYSQL Help Needed

Discussion in 'PHP & Perl' started by bl4zed, Dec 5, 2011.

  1. bl4zed

    bl4zed Newbie

    Joined:
    Aug 28, 2009
    Messages:
    13
    Likes Received:
    0
    Im working on a project right now but cant seem to figure this out. So heres what I want it to do, User goes to video.php, it checks if they already seen the video, and here where i need help, how do i get a new video that they have not seen. Thanks for the help.
     
  2. gimme4free

    gimme4free Executive VIP Jr. VIP Premium Member

    Joined:
    Oct 22, 2008
    Messages:
    1,884
    Likes Received:
    1,932
    The easiest way is to use cookies, E.g. Store the ID of the video in a cookie then create an SQL query:
    PHP:
    <?php
    $custom 
    "";
    if(isset(
    $_COOKIE['watched'])){
        
    $cookies explode(",",$_COOKIE['watched']);
        foreach(
    $cookies as $cookie){
            
    $custom.=" AND videoid!'=".."'";
            }
        
    $sql mysql_query("SELECT * FROM videos WHERE ".$custom." LIMIT 1");
        if(
    mysql_num_rows($sql)>1){
            
    $fetch mysql_fetch_array($sql);
            
    // Send user to other video
            
    header("location:http://yoursite.com/videos/".$fetch['videoid'].".html");
            exit;
            }
        }
    ?>
     
  3. bl4zed

    bl4zed Newbie

    Joined:
    Aug 28, 2009
    Messages:
    13
    Likes Received:
    0
    Is there a way to do it without cookies and just with mysql?
     
  4. hameem

    hameem Elite Member

    Joined:
    Jul 21, 2011
    Messages:
    1,880
    Likes Received:
    985
    Location:
    221B Baker Street
    Are the users registered members?

    Are you talking about videos specific to users?
     
  5. Quintessence

    Quintessence Newbie

    Joined:
    Dec 5, 2011
    Messages:
    14
    Likes Received:
    0
    Yes simply add a row in mysql like "Watched" then add a CronJob or a Timer.
     
  6. xpwizard

    xpwizard Junior Member

    Joined:
    Nov 6, 2010
    Messages:
    198
    Likes Received:
    122
    Yes. But to do this you would only be able to determine members by IP address (unless you already have a members DB) which isn't good for users sharing the same connection. Not to mention that this would make your database far too cluttered/bloated for no reason.
     
    Last edited: Dec 6, 2011
  7. ijof9

    ijof9 Power Member

    Joined:
    Mar 27, 2010
    Messages:
    536
    Likes Received:
    594
    Occupation:
    CTO
    Location:
    Western Europe
    have int primary keys for both videos & registered users
    create a memory table with two columns: user_id, video_id
    Code:
    CREATE TABLE user_view_history ( user_id int(10) unsigned DEFAULT NULL, video_id int(10) unsigned DEFAULT NULL, UNIQUE KEY uid_vid (user_id,video_id) ) ENGINE= MEMORY
    when a user watches a video do
    Code:
    INSERT IGNORE INTO user_view_history SET user_id = ..., video_id=...
    to retrieve unwatched videos
    Code:
    SELECT videos.id FROM videos, user_view_history WHERE user_view_history.user_id != ' current user id '  AND videos.id = user_view_history.video_id ORDER BY RAND() LIMIT 1 
    order by rand shuffles order but trashes performance so make sure you don't select *; do another query instead