Random quote - on a timer

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 1870 Views - Last Post: 13 January 2014 - 07:05 PM Rate Topic: -----

#1 chris98  Icon User is offline

  • D.I.C Lover

Reputation: 40
  • View blog
  • Posts: 1,100
  • Joined: 06-July 13

Random quote - on a timer

Posted 12 January 2014 - 04:39 AM

I have already prepared the database, and created the following table with 100 quotes in:

CREATE TABLE `quotes` (
  `id` int(10) NOT NULL auto_increment,
  `quote` text NOT NULL,
  `unit` varchar(50) NOT NULL,
  `game` varchar(50) default NULL,
   PRIMARY KEY  (`id`)
  );



I have the SQL correct, and it will randomly select a row.

        <div class="header_01">Random Quote:</div>
    <?php
				$getquote = "SELECT quote, unit, game FROM `quotes`
ORDER BY RAND()
LIMIT 1";
$quotes = $shn_conf->query($getquote);
foreach ($quotes as $row)
{
 echo ''.$row['quote'].'<br />'.$row['unit'].'<br />'.$row['game'].'<br />';
?>
			<div class="margin_bottom_20 horizontal_divider"></div>



But, every time I refresh the page, it changes. I want it to only change every "X" hours, but really don't know how. How could I do it?

This post has been edited by chris98: 12 January 2014 - 04:40 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Random quote - on a timer

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4154
  • View blog
  • Posts: 13,146
  • Joined: 08-June 10

Re: Random quote - on a timer

Posted 12 January 2014 - 04:44 AM

Quote

But, every time I refresh the page, it changes.

of course. you request a new random quote each time.


Quote

I want it to only change every "X" hours

per user or for all users?
Was This Post Helpful? 0
  • +
  • -

#3 chris98  Icon User is offline

  • D.I.C Lover

Reputation: 40
  • View blog
  • Posts: 1,100
  • Joined: 06-July 13

Re: Random quote - on a timer

Posted 12 January 2014 - 04:44 AM

For all users.

This post has been edited by chris98: 12 January 2014 - 04:45 AM

Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4154
  • View blog
  • Posts: 13,146
  • Joined: 08-June 10

Re: Random quote - on a timer

Posted 12 January 2014 - 04:47 AM

you need an intermediate data container. that could be another table or a file (to mention what I know off the top of my head).
Was This Post Helpful? 0
  • +
  • -

#5 chris98  Icon User is offline

  • D.I.C Lover

Reputation: 40
  • View blog
  • Posts: 1,100
  • Joined: 06-July 13

Re: Random quote - on a timer

Posted 12 January 2014 - 04:49 AM

Would I need any other fields (I.E. Date or time) or would they just be the same fields I already have?
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is online

  • Dreaming Coder
  • member icon


Reputation: 7056
  • View blog
  • Posts: 14,734
  • Joined: 16-October 07

Re: Random quote - on a timer

Posted 12 January 2014 - 06:13 AM

I would create another table:
CREATE TABLE current_quote (
    id int(10) NOT NULL,
    loadedOn date,
    PRIMARY KEY  (`id`)
);



Now, when you cough up your quote, you check the current_quote. If the date is too old, you delete it and load a new one using a rand select. Then you grab the quote, joining on the current_quote table.

Note, this is truly random. You may want to show all your quotes before you start randomly showing them again?

In that case, you could put a field on your quote table with exactly when the quote will be shown. You randomly populate that with times. This will make for less database traffic for each page. However, it will require you to refresh the times at some point.

Hmmm... you could just do the query with the date. If the record you get is too old, then you've run through all your queued quotes and you regen your time table.
Was This Post Helpful? 1
  • +
  • -

#7 andrewsw  Icon User is online

  • blow up my boots
  • member icon

Reputation: 6500
  • View blog
  • Posts: 26,286
  • Joined: 12-December 12

Re: Random quote - on a timer

Posted 12 January 2014 - 06:41 AM

Just my 2 cents, but if you already have 100 quotes you could perhaps just cycle through them. It would take a very long time for anyone to realise they weren't random (if they were sad enough to try to work this out :dontgetit: ).

But.. I suppose it is slightly more satisfying to achieve a random output.
Was This Post Helpful? 1
  • +
  • -

#8 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3732
  • View blog
  • Posts: 13,569
  • Joined: 08-August 08

Re: Random quote - on a timer

Posted 12 January 2014 - 06:44 AM

You could use a field "already_shown" where you'd set them all to false and then as they're shown set them to true. When ever the count of already_shown being false is zero you'd reset them all to false. Obviously, you'd select randomly from those that are false when choosing a new quote id.
Was This Post Helpful? 0
  • +
  • -

#9 chris98  Icon User is offline

  • D.I.C Lover

Reputation: 40
  • View blog
  • Posts: 1,100
  • Joined: 06-July 13

Re: Random quote - on a timer

Posted 12 January 2014 - 06:59 AM

Quote

if you already have 100 quotes you could perhaps just cycle through them


The problem is there that I have several all from the same game at the beginning, and all of a sudden you have 50 (or so) all from a different game, so it would get a bit boring seeing very similar ones.

I think that giving them a field "already_shown" would be a good idea, but I think it would be complex than just inserting the id and using the JOIN() statement below.

I've created the second table and added a random value in, and now I can use the following to get the quote.

SELECT quotes.quote, quotes.unit, quotes.game
FROM quotes
INNER JOIN current_quote ON quotes.id = current_quote.id



But I still think that the date will be tricky - would it require some kind of javascript timing function to switch every x hours, ad I don't fully understand how I would check if the quote should finished being shown.

I've also tried to create the queries but it says there is a

Quote

Fatal error: Call to a member function execute() on a non-object in /home/*directories*/sidebar_links.php on line 27


I know this means that the DB variable isn't correct (or something similar), but I was trying what Dormilich said here and don't know why it isn't working.

View PostDormilich, on 03 January 2014 - 08:51 PM, said:

oh, that’s simple.
$ps1->execute(array(':id'=>$user_id));
$res = $ps1->fetch();
mail($res['email'], $subject, $message, $headers);





    <?php
//Get the random quote
$getquote = "SELECT `id` FROM `quotes`
ORDER BY RAND()
LIMIT 1";
$quotes = $shn_conf->query($getquote);
$shn_conf = $quotes->fetch();

$date = date('Y-m-d H:i:s');

//Insert it into the "current_quote" table
$insertquote = "INSERT INTO `current_quote` (id,loaded_on) VALUES (:id,:date)";
$insertquote->execute(array(':id'=>$shn_conf['id'], 
                  ':date'=>$date)); 

//Retreive it from the table
$getcurrent = "SELECT quotes.quote, quotes.unit, quotes.game
FROM quotes
INNER JOIN current_quote ON quotes.id = current_quote.id";
$ps = $shn_conf->query($getcurrent);
foreach ($ps as $row)
{
?>
<?php echo $row['quote']; ?><br />
<?php echo $row['unit']; ?><br />
<?php echo $row['game']; ?><br />
<?php } ?>



This post has been edited by chris98: 12 January 2014 - 07:00 AM

Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • blow up my boots
  • member icon

Reputation: 6500
  • View blog
  • Posts: 26,286
  • Joined: 12-December 12

Re: Random quote - on a timer

Posted 12 January 2014 - 07:05 AM

View PostCTphpnwb, on 12 January 2014 - 01:44 PM, said:

When ever the count of already_shown being false is zero you'd reset them all to false.

You would have to count them each time though, or perhaps count them when you encounter a true value. (Or store the count separately.)

I suppose you could store an integer 0,1,2,.. When this number is 2, meaning you are about to show the same quote for the third time, this could be the trigger to reset them back to 0. I think I'm over-thinking this :)

This post has been edited by andrewsw: 12 January 2014 - 07:06 AM

Was This Post Helpful? 0
  • +
  • -

#11 chris98  Icon User is offline

  • D.I.C Lover

Reputation: 40
  • View blog
  • Posts: 1,100
  • Joined: 06-July 13

Re: Random quote - on a timer

Posted 12 January 2014 - 09:08 AM

OK, I've managed to partially get it working. I've changed the coding in the /include/sidebar_links.php to the following:

        <div class="header_01">Random Quote:</div>
<?php
// Retrieve quote from the table
$getquote = "SELECT quotes.quote, quotes.unit, quotes.game
FROM quotes
INNER JOIN current_quote ON quotes.id = current_quote.id LIMIT 1";
$ps = $shn_conf->query($getquote);
foreach ($ps as $row)
{
?>
<div class="quote"><?php echo $row['quote']; ?></div>
<p class="unit"><?php echo $row['unit']; ?></p>
<p class="game"><?php echo $row['game']; ?></p>
<?php } ?>
<div class="margin_bottom_20 horizontal_divider"></div>




And I've created a separate file purely for demonstration purposes until I get it working (I hope) which will switch the quote - however, I have to manually visit this in the URL for it to work, and I still would rather have a timer.

<?php require_once('include/include.php');

// Delete everything from the database
$sql = "DELETE FROM `current_quote`";
$stmt = $shn_conf->prepare($sql);   
$stmt->execute();

// Get a new quote
$ranquote = "SELECT `id` FROM `quotes` ORDER BY RAND() LIMIT 1";
$quotes = $shn_conf->query($ranquote);
foreach ($quotes as $row)
{
$id = $row['id'];
}
$date = date('Y-m-d H:i:s');
// Insert the new quote in the database
$insertquote = "INSERT INTO `current_quote` (id,loaded_on) VALUES (:id,:date)";
$ps = $shn_conf->prepare($insertquote);
$ps->execute(array(
':id'=>$id, 
':date'=>$date
)); 

?>



I also don't know how to stop the
$id = $row['id'];
because every time I try Dormilich's way whenever the next query after that one is executed it comes back with the

Quote

Fatal error: Call to a member function execute() on a non-object in /home/*directories*/sidebar_links.php on line "X"


What sort of thing would be required to make a timer for it? As baavgai suggested, I have made a date field in the second table, with the year, month, day, hour, minute and second all in them but I still don't know how to check?
Was This Post Helpful? 0
  • +
  • -

#12 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4154
  • View blog
  • Posts: 13,146
  • Joined: 08-June 10

Re: Random quote - on a timer

Posted 12 January 2014 - 01:19 PM

View Postchris98, on 12 January 2014 - 05:08 PM, said:

I also don't know how to stop the
$id = $row['id'];
because every time I try Dormilich's way whenever the next query after that one is executed it comes back with the

Quote

Fatal error: Call to a member function execute() on a non-object in /home/*directories*/sidebar_links.php on line "X"

time to turn on PDO’s error reporting.
Was This Post Helpful? 0
  • +
  • -

#13 baavgai  Icon User is online

  • Dreaming Coder
  • member icon


Reputation: 7056
  • View blog
  • Posts: 14,734
  • Joined: 16-October 07

Re: Random quote - on a timer

Posted 12 January 2014 - 02:20 PM

Ok, you inspired me to try a proof of concept. I'm assuming mysql here.

First, a table and a couple of views. Note, the need for sub view because mysql is too stupid use a subquery in a view.

CREATE TABLE quote (
    quote_id int NOT NULL auto_increment,
    txt text NOT NULL,
    show_dt datetime,
    PRIMARY KEY  (quote_id)
);

create view vw_current_quote_sub
as
select min(show_dt) as show_dt from quote where show_dt>=now();

create view vw_current_quote
as
select a.*
from quote a
inner join vw_current_quote_sub b on a.show_dt=b.show_dt;

-- some seed stuff, just for fun
insert into quote(txt) values('A bad workman quarrels with his tools.');
insert into quote(txt) values('A bird in hand is safer than one overhead.');
insert into quote(txt) values('A hacker does for love what others would not do for money.');
insert into quote(txt) values('A rolling stone gathers momentum.');
insert into quote(txt) values('A truly wise man never plays leapfrog with a Unicorn.');
insert into quote(txt) values('A witty saying means nothing.  -Voltaire');



Here show_dt will contain a starting date. We use the internal clock for the query. When the query returns nothing, it's time to reset the show_dt value in all records.

Now some quick PHP:
<?php

echo getQuote();

function getQuote() {
    $row = getQuoteRaw();
    if (!$row) {
        loadQuoteTimes();
        $row = getQuoteRaw();
    }
    return $row['txt'] . "\n";
}

function getQuoteRaw() {
    $db = getDb();
    $q = $db->prepare("select txt from vw_current_quote");
    $q->execute();
    return $q->fetch();
}

// this is deep MySql voodoo, I'm afraid
// basically, randomly choose the entire table
// use an internal variable starting now
// apply that value to the first random row
// increment that value by the interval and apply that to the next row
// The final result will be show_dt filled in randomly, interval apart,
// starting now
function loadQuoteTimes() {
    $db = getDb();
    $db->exec("
        UPDATE quote,(
            SELECT @dt:=DATE_ADD(@dt, INTERVAL 2 HOUR) dt, t.quote_id
               FROM 
                  (SELECT @dt:=NOW()) r,
                  (select quote_id from quote order by rand()) t
             ) t
             SET quote.show_dt=t.dt
             WHERE quote.quote_id=t.quote_id;
       ");
}
?>



The nice part about this design is that there is no real checking overhead. You pull the row, as you have to anyway. If the row is null, it's time to refresh your random list.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#14 no2pencil  Icon User is online

  • Professor Snuggly Pants
  • member icon

Reputation: 6595
  • View blog
  • Posts: 30,789
  • Joined: 10-May 07

Re: Random quote - on a timer

Posted 12 January 2014 - 03:47 PM

Rather than using php, I would use system tools for this.

1.) Place all your quotes into a text file
2.) Using cron, every hour cat that file to word could (wc -l) to get the max number of lines
3.) Random between zero & that number minus one
4.) Re-cat the file | tail that line & output to a 2nd test file (quote_of_the_day.txt) or something
5.) Read that file with php

php isn't going to be the best tool (imo) for 'hourly' jobs. You can use cron to curl on an hourly basis...

Not saying the suggestions here are wrong, but this is how I would do it :)
Was This Post Helpful? 1
  • +
  • -

#15 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3732
  • View blog
  • Posts: 13,569
  • Joined: 08-August 08

Re: Random quote - on a timer

Posted 12 January 2014 - 05:18 PM

I was thinking of doing something similar but not using cron. I think it gets over-used and can add more complication than it's worth. I was thinking of using a text file with each quote taking a line (read using file() ) and calculating the index number based on the number of hours from a fixed starting date/time. You could randomize the text file every hundred hours to keep it random indefinitely.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2