Updating the database with crontab

Help me make a script and get the cron working so that:

  1. Downloaded every 6 hours

    http://download.forum3.ru/download/forum_csv.zip
    
  2. Unpacked (forum.csv);

  3. Updating the table tablein the database.


Answer 1, authority 100%

A sketch of the crutch script. Crooked, knee-deep, but it can work if nothing intentionally evil is thrown into the CSV:

#!/bin/sh
curl http://download.forum3.ru/download/forum_csv.zip \
    | funzip \
    | perl -e 'while(<>) {
          chomp;
          my @r = map { s/\x27/\\\x27/; "\x27$_\x27" } split /;/;
          print "INSERT INTO table VALUES (".join(", ", @r).");\n";}' \
    | mysqlimport ...

This is a “shell” script, specifically the Bourne shell (/bin/sh). Essentially a plain text file. The first line is a hashbang (“#!“) indicating the interpreter, and then the text of the script. If you put chmod +x(say, 0770aka rwxrwx---), then the file will be “executable”, the system will run by hashbang interpreter, etc. Oh, yes, and it only works on *unix systems. On Windows®, only if under Cygwin.

In crontab: 0 */6 * * * ///. It is also possible to add after the last “*” another field – username, if crontab is a system one.

The format is described in crontab(5).

  • Minutes (0-59)
  • Hours (0-23)
  • Day of month (1-31)
  • Month (1-12)
  • Day of the week (0-7; 0 = 7 = Sunday)

You can use “*” (any value), ranges (1-10), lists (1,2,4-6,12) or set the step (*/6).

Update:

As promised, the PHP version. I haven’t tested it, but if it’s not printed anywhere, it should work.

<?php
    $filename = "forum_csv.zip";
    //    cURL: http://ru2.php.net/manual/ru/book.curl.php
    $fp = fopen($filename, "w+");
    $ch = curl_init("http://download.forum3.ru/download/forum_csv.zip");
    curl_setopt($ch, CURLOPT_TIMEOUT, 50);
    curl_setopt($ch, CURLOPT_FILE, $fp);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
    curl_exec($ch);
    curl_close($ch);
    fclose($fp);
    //    ZIP-.
    //      
    //    ZIP-: http://ru2.php.net/manual/ru/book.zip.php
    $zip = zip_open($filename);
    if ($zip) {
        //    ZIP-,      "forum.csv"
        // (     )
        do {
            $entry = zip_read($zip);
        } while ($entry && zip_entry_name($entry) != "forum.csv");
        if ($entry) {
            //  forum.csv ,   .   .
            //    (   ),  .
            zip_entry_open($zip, $entry, "r");
            $csv = zip_entry_read($entry, zip_entry_filesize($entry));
            //   MySQL.  ,  .
            // . http://ru2.php.net/manual/ru/function.mysql-connect.php
            mysql_connect();
            //     "\n".
            foreach(explode("\n", $csv) as $line) {
                //     CSV:
                // http://ru2.php.net/manual/ru/function.str-getcsv.php
                //  'escape'    .
                $items = array_map("mysql_real_escape_string", str_getcsv($line, ";"));
                // ,  . -.
                //        .
                //  ,  "mysql_query"  "echo".
                mysql_query("INSERT INTO table VALUES ('" .
                            implode("','", $items) . "');");
            }
            zip_entry_close($entry);
        }
        zip_close($zip);
    }
?>

In crontab write a line like this:

0 */6 * * * /usr/bin/php /home/username/import-csv.php

Somehow.

Update2:

As it turns out, pohape cannot read a ZIP archive via http://. Fixed the script to work with a temporary file.