Wordpress, inserting table rows from csv-formatted txt file

115
December 19, 2018, at 10:50 PM

Currently working on a wordpress plugin that will allow the insertion of a txt file in a csv format into a custom table in the wordpress database.

I previously used a command to create the table and the columns with the appropriate datatypes. This is an example of a table I'm creating:

global $gtfs_db_version;
$gtfs_db_version = '1.0';
function routes_install() {
global $wpdb;
global $gtfs_db_version;
$table_name = 'routes_txt';
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE $table_name (
    id mediumint(9) NOT NULL AUTO_INCREMENT,
    route_long_name varchar(80) NOT NULL,
    route_type  varchar(80),
    route_text_color    varchar(80),
    route_color varchar(80),
    agency_id   varchar(80),
    route_id    varchar(80),
    route_url   varchar(80),
    route_short_name    varchar(80),
    PRIMARY KEY  (id)
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
add_option( 'gtfs_db_version', $gtfs_db_version );
};

Now, in the command where I am creating the table rows from the txt file, there is a chance the column order will be different every time a new txt file is available. This is where the problem can occur. Right now, I have to manually change the column assignment in the command. The first row does have the column names as it's being inserted, if that helps.

function insert_routes(){
global $wpdb;
$path_name =   get_option( "routes_txt" );
$table_name = 'routes_txt';
$firstline = array_shift(file($path_name));
$file_array = file($path_name);
$file_line_num = 1;
foreach ($file_array as $file_array_value) {
      $number_of_lines =  count($file_array); // number of lines in txt file
      $file_array_value = preg_replace('/\s/', '', $file_array_value);
      //if first line of file that has tables column names
      $stringArray = explode(",",$file_array_value);
      if($file_line_num == 1){
          echo '';
      }else{
                    $route_id = array_values($stringArray)[0];
                    $route_short_name = array_values($stringArray)[1];
                    $route_long_name = array_values($stringArray)[2];
                    $route_type = array_values($stringArray)[3];
                    $route_url = array_values($stringArray)[4];
                    $agency_id = array_values($stringArray)[5];
                    $route_color = array_values($stringArray)[6];
                    $route_text_color = array_values($stringArray)[7];
         //puts everything seperated by a comma in a array
            $wpdb->replace( 
                $table_name, 
                array( 
                    'id' => $file_line_num,
                    'route_long_name' => $route_long_name,
                    'route_type' => $route_type,
                    'route_text_color' => $route_text_color,
                    'route_color' => $route_color,
                    'agency_id' => $agency_id,
                    'route_id' => $route_id,
                    'route_url' => $route_url,
                    'route_short_name' => $route_short_name
                )    
            );
        }
        $file_line_num++;
      }
};

Is there a way with a SQL command to assign the correct columns to the correct pre-designated columns?

EDIT: as requested, the first couple of lines of two different versions of the routes_txt csv.

Version 1

route_long_name, route_type, route_text_color, route_color, agency_id, route_id, route_url, route_short_name
, 3, FFFFFF, 008C9B, NCTD, 318, http://www.gonctd.com/breeze, 318
, 3, FFFFFF, 008C9B, NCTD, 313, http://www.gonctd.com/breeze, 313

Version 2

route_id, route_short_name, route_long_name, route_type, route_url, agency_id, route_color, route_text_color
301, 101, ,3, http://www.gonctd.com/breeze, NCTD, 008C9B, FFFFFF
302, 302, ,3, http://www.gonctd.com/breeze, NCTD, 008C9B, FFFFFF

And before you ask, asking the people in charge of creating these CSV's to keep with a consistent column paradigm is not possible(I know, right? ARG!)

Rent Charter Buses Company
READ ALSO
MySQL last line challenge when using a cursor to assemble prepared statement

MySQL last line challenge when using a cursor to assemble prepared statement

I'm using a cursor to build a prepared statement and that works pretty good (actually better than expected)But i can't wrap my head around the "last line challenge" what i'd like to call it for now

119
Symfony console doctrine:schema:update --force SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO)

Symfony console doctrine:schema:update --force SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO)

I have a Symfony 4 application which connects to a local MySQL instanceIt is running totally fine in the application and also while using console commands for doctrine

246
How to find duplicate records from Sql from multiple columns

How to find duplicate records from Sql from multiple columns

I want to find rows from my MySql table, which contains same values on two columns

127
Populate HTML DIV with data from mySQL

Populate HTML DIV with data from mySQL

I have a MySQL DB "dorav3" with a table "dtab1" with the below column containing business card infoI also have a php script and HTML page to display the DB data in a HTML table

143