CODEWITHSUNDEEP
phpmysql
AnchovyLegend
AnchovyLegend

Reputation: 12538

Fetch new table data daily with mySQL

The website I am working on provides a service, where only members can get full access to the content. I am trying to create a 'sample' on the homepage that fetches NEW mysql data (that is normally available for members only) every day and provides ALL users with a 'free sample' of what the site has to offer.

The database contains over one thousand entries and I would like to find a way to fetch new mysql data from the db every day.

Assuming my database looks like this:

id    cars
1     honda
2     volvo
3     ford
4     audi

I would like the 'car of the day' to be 'honda' for 24 hours, then 'ford' for 24 hours, then 'audi' for 24 hours, etc...

this is what I have so far:

<?php 
    //Create mysql connect variable
    $conn = mysql_connect('server', 'username', 'password');
    //kill connection if error occurs
    if(!$conn){
        die('Error: Unable to connect. <br/>' . mysql_error());
    }
    //connect to mysql database
    mysql_select_db("masdswe9", $conn);

    $results = mysql_query("SELECT * FROM cars");

    $name_array = Array();

    while($row = mysql_fetch_array($results)){
        $name_array[] = $row['name'];
    }
    shuffle($name_array);

   /* I would like for this to fetch NEW SQL data every 24 hours, 
   since I am shuffling names above, slot 0 will be different once I 
   come up with a way to fetch new mysql data every 24 hours */
    echo $name_array[0];
?>

Upvotes: 3

Views: 1468

Answers (2)

O. Jones
O. Jones

Reputation: 108651

@Explosion Pills has a good solution for your future. In your future, some car company is going to pay you big bucks to have their product be your car of the day.

For now, let's say you just want to run through your list of cars and make each one in turn the car of the day. Let's say your ID values are all present, with no gaps, starting at 1.

Then:

SELECT id, car
  FROM cars
 WHERE id >= 1+MOD(TO_DAYS(CURDATE()),(SELECT MAX(ID) FROM cars)+1)
 ORDER BY ID
 LIMIT 1

will give you a new car, in id sequence, every day, from all the cars in your table.

The trick here is this expression: 1+MOD(TO_DAYS(CURDATE()),(SELECT MAX(ID) FROM cars)+1)

It basically does this:

  1. turns today's date into a number
  2. computes the maximum ID number from the table
  3. takes today's date modulo that number
  4. looks up the row with that id.

There's some monkey business with 1+ and -1 because modulo arithmetic works 0 - n-1 rather than 1 - n.

Note that I used >= and LIMIT 1 instead of simply = so your query won't collapse in a heap on a random day should you happen to end up with a gap in your id sequence.

Edit This assumes your table is called "cars" and it has columns id and car. If that's not true you need to change the query accordingly. It's best to try to debug these things in a SQL client like phpmyadmin before programming them. I did test this on some dummy data of my own.

http://sqlfiddle.com/#!2/eb1bb/1/0

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191749

I would create a DB table called CarOfTheDay that you can select from (it will just have the ID of the car in the Cars table). Either that or you can have a column carOfTheDay, or change the order of the cars, but I think those are a bit more intrusive.

Create a cron that runs once a day ans writes a random car to CarOfTheDay. Then, you can do what you are doing above, but you don't have to worry about timing and you will only select one car:

SELECT
   cars
FROM
   CarOfTheDay
   JOIN Cars USING (id)
LIMIT 1

The LIMIT should not be necessary if you stick to one CarOfTheDay.

By the way, use PDO or mysqli rather than mysql_*. Only select columns you need (don't use SELECT * in production code) .. and only select rows you need (you can randomize in the query).

Upvotes: 2

Related Questions