Jump to content

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

Fluoresce

By Fluoresce
in PHP Coding Help

 Share
Go to solution Solved by PaulRyan,

Recommended Posts

Fluoresce Regular Member

Fluoresce

Posted
Posted

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. ;D

Link to comment
Share on other sites
requinix Prolific Member

requinix

Posted
Posted

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

Link to comment
Share on other sites
Fluoresce Regular Member

Fluoresce

Posted
  • Author
Posted

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 />";
                            }
                        }
                    ?>  
Link to comment
Share on other sites
vinny42 Newbie

vinny42

Posted
Posted

 


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.
Link to comment
Share on other sites
PaulRyan Member

PaulRyan

Posted
Posted

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.

Link to comment
Share on other sites
Fluoresce Regular Member

Fluoresce

Posted
  • Author
Posted

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 />";
    }  
    
?>
Link to comment
Share on other sites
TOA Member

TOA

Posted
Posted (edited)

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 by TOA
Link to comment
Share on other sites
Fluoresce Regular Member

Fluoresce

Posted
  • Author
Posted

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());
Link to comment
Share on other sites
  • Solution
PaulRyan Member

PaulRyan

Posted
  • Solution
Posted

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.';
  }
 
?>
Link to comment
Share on other sites
Fluoresce Regular Member

Fluoresce

Posted
  • Author
Posted

 

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. ;D

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.