Tag: wptables

Like many things that appear on a computer screen there is a long chain of events that need to happen successfully for what is on screen to be what is desired there. The various “Movies” tables on this website are one example.

I got some DVDs for Christmas. A very nice Seven Samurai DVD with extras. My movies database had it marked as movie I want. It appeared on this website in the Movies I Want tables that are on two pages of this website.

Change the setting in the database so it’s a movie I have and the title should now only be found in the two Movies I Have tables on the website and no longer found in the two Movies I Want tables on this website. One change to the source to trigger four changes on the website.

In the case of the Movies tables, for changes to the movies database to appear in the tables, the updated data must be exported to two files, one listing “Movies I Have” and the other “Movies I Want”. Those exported files update the source lists the Movies tables refer to. And finally a sync tool from the wpTables publisher must run against the source lists to update the Movies tables on the website.

Making changes to the movies database is infrequent, a few times a year at most. Remembering the process each time is a challenge but now the data extract step and the link refresh steps are automated which makes most of the process happen without need to remember anything (or look at the code if I wish to remember).

The link update code as a cron…

# m h  dom mon dow   command
*/15 * * * * wget -q -O – "https://wp.boba.org/wp-admin/admin-ajax.php?action=wdtable_update_cache&wdtable_cache_verify=<hex-number>"

Export updates for source lists …

<?php
 $server = "<host>"; 
 $username = "<user-name>";
 $password = "<pwd>"; 
 $database_name = "<movies>"; 
 $link_myMovies = mysqli_connect($server, $username, $password, $database_name);
 $Views = array("movies_i_want","movies_i_have");
 $out_path = "/var/tmp/";
 
 foreach ($Views as $view)
 {
        $query_result = null;
        $columns_total = null;
        $str_query = "SELECT * FROM $view";
        $query_result = mysqli_query($link_myMovies, $str_query);
        $columns_total = mysqli_num_fields($query_result);
 
        $col_names = array();
 
        for ($i = 0; $i < $columns_total; $i++) 
         {
                $Heading = mysqli_fetch_field_direct($query_result, $i);
                array_push($col_names,$Heading->name);
         }
 
         $fileOut = fopen("$out_path$view.csv", 'w') or die("Unable open ./$out_path$view.csv");
         fputcsv($fileOut, $col_names);

         while ($row = mysqli_fetch_array($query_result, MYSQLI_NUM)) 
         {
                fputcsv($fileOut, array_values($row)); 
         }
         
         fclose($fileOut) or die("Unable to close ./$view.csv");
}
?>