How do You Echo a Different List Every Day, Automatically?

[Fluoresce]

I want a "Featured Videos" list on my home page that shows a different list of videos, randomly selected from a database, every day.  How can this be done? As a PHP novice, I don't even know where to begin!

 

I'm not asking for you guys to do this for me. I want to learn how to do things like this for myself. All I'm asking for is a push in the right direction.

 

As always, I appreciate you guys' help. I've learnt loads here.

[requinix]

0. Assume that your ID numbers in the database are uniformly distributed without many large gaps in the sequence. They very likely are.

1. Determine the highest ID number and subtract one.

2. srand() using today's date as YYYYMMDD.

3. Pick however many random numbers you want between 0 and that ID-1, then SELECT video FROM table WHERE id > the random number LIMIT 1

[Fluoresce]

0. Assume that your ID numbers in the database are uniformly distributed without many large gaps in the sequence. They very likely are.

1. Determine the highest ID number and subtract one.

2. srand() using today's date as YYYYMMDD.

3. Pick however many random numbers you want between 0 and that ID-1, then SELECT video FROM table WHERE id > the random number LIMIT 1

 

Thanks, requinix, but I'm not sure I understood all of that.

 

My database currently has no gaps.

 

In more detail, here's what I want to do:

 

Every day, I want my home page to display 3 featured videos.  The same 3 videos have to be displayed all day.  Then, the next day, I want 3 new videos to be displayed.

 

Below is what I've got so far.  Remember, I'm a complete novice, so it probably looks ridiculous to many of you!  You won't be surprised to hear that it doesn't work.  It always prints 3 different records.

<?php                        
                        // Select and print 3 records if $todaysdate is not today's actual date
                        if($todaysdate != date("d-m-y")) {                        
                            $todaysdate = date("d-m-y");
                           
                            // Connect to database
                            $conn = mysql_connect("localhost", "", "") or die(mysql_error());  
                            mysql_select_db("database");
                    
                            // Find number of records
                            $maxid = mysql_query("SELECT MAX(id) AS max FROM `vids`");
                            $row = mysql_fetch_array($maxid);
                            $largestNum = $row['max'];
                
                            // Generate 3 different random IDs
                            $arr = array();
                            while(count($arr) < 3) {
                                $x = mt_rand(0, $largestNum);
                                if (!in_array($x, $arr)) {
                                    $arr[] = $x;
                                }
                            }                            
                            $randid1 = $arr[0];
                            $randid2 = $arr[1];
                            $randid3 = $arr[2];

                            // Select and print results                        
                            $sql = "SELECT record FROM `vids` WHERE (id=$randid1 OR id=$randid2 OR id=$randid3)";
                            $results = mysql_query($sql) or die(mysql_error());
                            while($row = mysql_fetch_array($results)) {
                                print $row['record'] . "<br />";
                            }                                                                        
                        }                        
                        else {
                            $sql = "SELECT record FROM `vids` WHERE (id=$randid1 OR id=$randid2 OR id=$randid3)";
                            $results = mysql_query($sql) or die(mysql_error());
                            while($row = mysql_fetch_array($results)) {
                                print $row['record'] . "<br />";
                            }
                        }
                    ?>   

[vinny42]

 

Every day, I want my home page to display 3 featured videos.  The same 3 videos have to be displayed all day.  Then, the next day, I want 3 new videos to be displayed.

 

Given that you can re-use the selection all day, I'd run a query and save the result somewhere.

 

The query can be as simple as:

 

SELECT * FROM table ORDER BY RANDOM() LIMIT 3;

 

and the three results you get, you can save as a normal PHP array using serialize().

Then your script can load them again using file_get_contents() and unserialize();

 

like this:

 $filename = 'saved_randoms.txt'; // Create a new set of values$arrRandoms = array(10,45,88);$strRandoms = serialize($arrRandoms);file_put_contents($filename, $strRandoms); echo 'new randoms:</br>';var_dump($arrRandoms);echo '</br>'; // Load the set of arrays.$strLoadedRandoms = file_get_contents($filename);$arrLoadedrandoms = unserialize($strLoadedRandoms); echo 'loaded randoms:</br>';var_dump($arrRandoms);echo '<br/>'.$filename. ' was last modified: ' . date ("F d Y H:i:s.", filemtime($filename)).'<br/>';

 

You can do this once a day using a cronjob, or you can use filemtime() at  every pageview to see if the last-modified date is older than one day, and save a new list of arrays.

[PaulRyan]

Alternatively, you could add a new column to your database, something like "show_today" and set it as "tinyint" and a length of "1", then default the value to "0".

 

Then create a cron job, to run each day, to firstly select however many videos you'd like displayed that were not shown yesterday I.E. have "show_today" set to 0.

 

Then when you have chosen the 3 videos, reset all rows to have "show_today" set to 0, and then update the table and set the chosen videos to "show_today" to 1.

 

Then when displaying the page, you just need to select the videos where "show_today" is set to 1.

[vinny42]

 

Alternatively, you could add a new column to your database, 

 

I'd rather do a new table, because this information is purely for presentation on the website and it's not part of the video's data.

Other than that; yes a database would work, sure.

[PaulRyan]

I'd rather do a new table, because this information is purely for presentation on the website and it's not part of the video's data.

Other than that; yes a database would work, sure.

 

I was thinking the same, but it seems like over kill for 3 videos?

[Fluoresce]

Thanks for your help, guys.

 

Okay, here's what I've got so far.  Will it work (I can't check it right now)? How could I improve it?

 

1. Create a file called once-a-day.php, to run once a day, with this code:

<?php

    // Connect to database
    $conn = mysql_connect("localhost","", "") or die(mysql_error());
    mysql_select_db("database");
                    
    // Find number of records    
    $sql = "SELECT MAX(id) AS max FROM `video_tbl`";
    $maxid = mysql_query($sql, $conn) or die(mysql_error());
    $row = mysql_fetch_array($maxid);
    $largestNum = $row['max'];
                
    // Generate 5 different random video IDs
    $arr = array();
    while(count($arr) < 5) {
        $x = mt_rand(0, $largestNum);
        if(!in_array($x, $arr)) {
            $arr[] = $x;
        }
    }                            
    $randid1 = $arr[0];
    $randid2 = $arr[1];
    $randid3 = $arr[2];
    $randid4 = $arr[3];
    $randid5 = $arr[4];
    
    // Update feature table with new video IDs    
    $sql = "UPDATE `featuretbl` SET `featuredvid` = $randid1 WHERE `featuretblid` = 1";
    mysql_query($sql, $conn) or die(mysql_error());
    $sql = "UPDATE `featuretbl` SET `featuredvid` = $randid2 WHERE `featuretblid` = 2";
    mysql_query($sql, $conn) or die(mysql_error());
    $sql = "UPDATE `featuretbl` SET `featuredvid` = $randid3 WHERE `featuretblid` = 3";
    mysql_query($sql, $conn) or die(mysql_error());
    $sql = "UPDATE `featuretbl` SET `featuredvid` = $randid4 WHERE `featuretblid` = 4";
    mysql_query($sql, $conn) or die(mysql_error());
    $sql = "UPDATE `featuretbl` SET `featuredvid` = $randid5 WHERE `featuretblid` = 5";
    mysql_query($sql, $conn) or die(mysql_error());
        
?>

2. On my home page, where I want the featured videos to appear, have something like this:

<?php

    $conn = mysql_connect("localhost", "", "") or die(mysql_error());  
    mysql_select_db("");
    
    $sql = "SELECT `featuredvid` FROM `featuretbl`";                        
    $results = mysql_query($sql, $conn) or die(mysql_error());
    $arr = array();
    while($row = mysql_fetch_assoc($results)) {
        $arr[] = $row['featuredvid'];                        
    }
    $randid1 = $arr[0];
    $randid2 = $arr[1];
    $randid3 = $arr[2];
    $randid4 = $arr[3];
    $randid5 = $arr[4];
    
    // Select and print videos
    $sql = "SELECT `vid` FROM `video_tbl` WHERE (id = $randid1 OR id = $randid2 OR id = $randid3 OR id = $randid4 OR id $randid5)";                        
    $results = mysql_query($sql, $conn) or die(mysql_error());
    while($row = mysql_fetch_array($results)) {
        print $row['vid'] . "<br />";
    }  
    
?>

[PaulRyan]

It can be optimized to be more efficient than you have it, could you show me the table structure of the "featuretbl"

[TOA]

I was thinking the same, but it seems like over kill for 3 videos?

 

I would normally agree, but there's great potential with the historical data this would generate. You could see what you showed on Jan 24th for example.

 

OP: I'd do it with a cron and a simple query on the page.

CREATE TABLE `videos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `url` varchar(255) NOT NULL, // assuming these are actually stored in a file structure
  PRIMARY KEY (`id`)
)

CREATE TABLE `video_views` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vid` varchar(50) NOT NULL, // FK from video table
  `date` timestamp NOT NULL,
  PRIMARY KEY (`id`)
)

// CRON
// query for 3 videos from videos table - this is where you'd add your additional business rules for which videos to display ie: per week, per month, etc.
// insert them into video_views table with a timestamp/date

// PAGE
// query for all videos with today's date
// show them

Edited September 20, 2013 by TOA

[Fluoresce]

It can be optimized to be more efficient than you have it, could you show me the table structure of the "featuretbl"

 

Sure. It's like this:

CREATE TABLE `featuretbl` (
`featuretblid` tinyint(1) NOT NULL UNSIGNED AUTO_INCREMENT,
`featuredvid` tinyint(4) NOT NULL UNSIGNED,
PRIMARY KEY (`featureid`)
)

Surely, there's a way of optimising this:

    // Update feature table with new video IDs    
    $sql = "UPDATE `featuretbl` SET `featuredvid` = $randid1 WHERE `featuretblid` = 1";
    mysql_query($sql, $conn) or die(mysql_error());
    $sql = "UPDATE `featuretbl` SET `featuredvid` = $randid2 WHERE `featuretblid` = 2";
    mysql_query($sql, $conn) or die(mysql_error());
    $sql = "UPDATE `featuretbl` SET `featuredvid` = $randid3 WHERE `featuretblid` = 3";
    mysql_query($sql, $conn) or die(mysql_error());
    $sql = "UPDATE `featuretbl` SET `featuredvid` = $randid4 WHERE `featuretblid` = 4";
    mysql_query($sql, $conn) or die(mysql_error());
    $sql = "UPDATE `featuretbl` SET `featuredvid` = $randid5 WHERE `featuretblid` = 5";
    mysql_query($sql, $conn) or die(mysql_error());

[PaulRyan]

Here is how I would do it:

 

Feature Videos Table

 

CREATE TABLE IF NOT EXISTS `featured_videos` (
  `video_id` int(11) NOT NULL,
  `featured_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Cron Job

 

<?PHP

  //### Connect to database
  $conn = mysql_connect("127.0.0.1", "root", "") or die(mysql_error());
  mysql_select_db("test");
    
  //### Set how many to select
  $videos = 5;
    
  //### Select max ID
  $selectMaxQuery = "SELECT MIN(`id`) AS `min`, MAX(`id`) AS `max` FROM `video_tbl`";
  $selectMax      = mysql_query($selectMaxQuery, $conn) or die(mysql_error());
    
  //### Check to see if rows are returned
  if(mysql_num_rows($selectMax)) {
    
    //### Assign record to variable
    $selected = mysql_fetch_assoc($selectMax);
    
    //### Select videos randomly
    $start = mt_rand($selected['min'], $selected['max']-($videos));
      
    //### Now select videos from database
    $selectVideosQuery = "SELECT `id` FROM `video_tbl` LIMIT {$start}, {$videos}";
    $selectVideos      = mysql_query($selectVideosQuery, $conn) or die(mysql_error());
      
    //### Check for returned rows
    if(mysql_num_rows($selectVideos)) {
      
      //### Start query
      $insertVideosQuery = "INSERT INTO `featured_videos` (`video_id`, `featured_date`) VALUES ";
      
      //###Iterate over each row
      while($video = mysql_fetch_assoc($selectVideos)) {
        $videosArray[] = "({$video['id']}, NOW())";
      }
        
      //### Add to end of query
      $insertVideosQuery .= implode(',', $videosArray);
        
      //### Execute query
      $insertVideos = mysql_query($insertVideosQuery, $conn) or die(mysql_error());
      
    }
  }
 
?>

 

And to select the videos

 

<?PHP

  //### Connect to database
  $conn = mysql_connect("127.0.0.1", "root", "") or die(mysql_error());
  mysql_select_db("test");
    
  //### Select videos from featured table
  $selectVideosQuery = "SELECT `vt`.`id`, `vt`.`vid`
                        FROM `featured_videos` AS `fv`
                        LEFT JOIN `video_tbl` AS `vt` ON `vt`.`id` = `fv`.`video_id`
                        WHERE `fv`.`featured_date` = CURDATE()";
                        
  $selectVideos = mysql_query($selectVideosQuery, $conn) or die(mysql_error());
 
  //### Check for videos
  if(mysql_num_rows($selectVideos)) {
 
    //### Iterate over each row
    while($video = mysql_fetch_assoc($selectVideos)) {
      echo '<pre>';
      print_r($video);
      echo '</pre>';
    }
 
  } else {
    echo 'No videos to show.';
  }
 
?>

[Fluoresce]

 

Here is how I would do it:

 

Feature Videos Table

CREATE TABLE IF NOT EXISTS `featured_videos` (
  `video_id` int(11) NOT NULL,
  `featured_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Cron Job

<?PHP

  //### Connect to database
  $conn = mysql_connect("127.0.0.1", "root", "") or die(mysql_error());
  mysql_select_db("test");
    
  //### Set how many to select
  $videos = 5;
    
  //### Select max ID
  $selectMaxQuery = "SELECT MIN(`id`) AS `min`, MAX(`id`) AS `max` FROM `video_tbl`";
  $selectMax      = mysql_query($selectMaxQuery, $conn) or die(mysql_error());
    
  //### Check to see if rows are returned
  if(mysql_num_rows($selectMax)) {
    
    //### Assign record to variable
    $selected = mysql_fetch_assoc($selectMax);
    
    //### Select videos randomly
    $start = mt_rand($selected['min'], $selected['max']-($videos));
      
    //### Now select videos from database
    $selectVideosQuery = "SELECT `id` FROM `video_tbl` LIMIT {$start}, {$videos}";
    $selectVideos      = mysql_query($selectVideosQuery, $conn) or die(mysql_error());
      
    //### Check for returned rows
    if(mysql_num_rows($selectVideos)) {
      
      //### Start query
      $insertVideosQuery = "INSERT INTO `featured_videos` (`video_id`, `featured_date`) VALUES ";
      
      //###Iterate over each row
      while($video = mysql_fetch_assoc($selectVideos)) {
        $videosArray[] = "({$video['id']}, NOW())";
      }
        
      //### Add to end of query
      $insertVideosQuery .= implode(',', $videosArray);
        
      //### Execute query
      $insertVideos = mysql_query($insertVideosQuery, $conn) or die(mysql_error());
      
    }
  }
 
?>

And to select the videos

<?PHP

  //### Connect to database
  $conn = mysql_connect("127.0.0.1", "root", "") or die(mysql_error());
  mysql_select_db("test");
    
  //### Select videos from featured table
  $selectVideosQuery = "SELECT `vt`.`id`, `vt`.`vid`
                        FROM `featured_videos` AS `fv`
                        LEFT JOIN `video_tbl` AS `vt` ON `vt`.`id` = `fv`.`video_id`
                        WHERE `fv`.`featured_date` = CURDATE()";
                        
  $selectVideos = mysql_query($selectVideosQuery, $conn) or die(mysql_error());
 
  //### Check for videos
  if(mysql_num_rows($selectVideos)) {
 
    //### Iterate over each row
    while($video = mysql_fetch_assoc($selectVideos)) {
      echo '<pre>';
      print_r($video);
      echo '</pre>';
    }
 
  } else {
    echo 'No videos to show.';
  }
 
?>

 

Thanks, mate! I appreciate it very much.

 

Now, I just have to understand it.

[Fluoresce]

Thanks, PaulRyan! The code worked well. Learnt loads from it, too.