Insert into database from text file, with special characters like , and \

user_6611693 0
Viewed 147

I'm struggling to fix a problem with a php system that someone created for me, when I try to import a text file it has errors on fields that contain a comma or a backslash.

this is the import row, is there a way to have it remove or allow the characters?

$sql = "INSERT INTO `tbl_fines` (`id`,`la_code`,`notice_no`,`date`,`time`,`tran_type`,`due_date`,`easypay_no`,`amount`,`user_id`,`file_name`,`veh_reg_no`,`offence_date`,`id_no`,`offence_type`,`status`,`location`,`offence_time`,`effective_date`,`effective_amt`,`rep_date`,`rep_result_date`,`courtesy_date`,`summons_date`,`court_date`,`woa_date`,`notice_type`,`veh_make`,`veh_type`,`film_no`,`frame_no`,`woa_auth_date`,`notice_status`,`notice_stage`,`notice_number`,`speed`,`surname`,`name`,`court_code`,`court_description`,`zone_speed`) VALUES (Null,".implode(',', $dt).");";

and a line of data that it is importing is:

"520","5672712660146111","20190514","13780590","0942","20190416","0000000000000000","0000000","JOE","52004471.APS","BLV547DD\","20190226","9306245559088","BRAKES:PARK, STATIONARY    M/V","02","SOUTH/NORTH","1740","20190524","00150","00000000","00000000","00000000","00000000","20190430","20190516","S56","T05","12","0000000","0000000","20190516","WIT","WOA","56/72712/660/146111","","BILL","THOB\","152019","EAST LONDON",""

In that data it does not like any of the fields that end in a backslash and it does not like any of the fields that contain a comma.

Is there a way to change that INSERT INTO row to allow or remove those characters?

Any help will be greatly appreciated.

Here is the upload code:

    public function upload_file()
{
    if (!$this->session->userdata('admin_logged_in')) {
        return redirect(base_url('admin/login'));
    }

    $config['upload_path']          = './uploads/';
    $config['allowed_types']        = '*';
    $config['overwrite']            = TRUE;

    $this->load->library('upload', $config);
    $bit = false;
    if ( ! $this->upload->do_upload('file'))
    {
        $error = array('error' => $this->upload->display_errors());
        return redirect(base_url('admin/fines'));
    }
    else
    {
        $data = $this->upload->data();
        $file_name = $data['file_name'];
        if($file_name != ""){
            $handle = fopen(base_url('uploads/'.$file_name.''), "r");
            if ($handle) {
                $insert_count = 0;
                $update_count = 0;
                while (($line = fgets($handle)) !== false) {
                    $string = explode(",", $line);
                    // $i = 0;
                    // foreach ($string as $s) {
                    //     $i++;
                    // }
                    if (count($string) == 40) {
                        $j = 0;
                        $query = "";
                        foreach ($string as $st) {
                            $dt[$j] = $st;
                            $j++;
                        }
                        $chk = $this->admin_model->getRecordsByTable('tbl_fines', array('tbl_fines.notice_no' => str_replace('"', '', $dt[1])));
                        if (empty($chk)) {
                            $sql = "INSERT INTO `tbl_fines` (`id`,`la_code`,`notice_no`,`date`,`time`,`tran_type`,`due_date`,`easypay_no`,`amount`,`user_id`,`file_name`,`veh_reg_no`,`offence_date`,`id_no`,`offence_type`,`status`,`location`,`offence_time`,`effective_date`,`effective_amt`,`rep_date`,`rep_result_date`,`courtesy_date`,`summons_date`,`court_date`,`woa_date`,`notice_type`,`veh_make`,`veh_type`,`film_no`,`frame_no`,`woa_auth_date`,`notice_status`,`notice_stage`,`notice_number`,`speed`,`surname`,`name`,`court_code`,`court_description`,`zone_speed`) VALUES (Null,".implode(',', $dt).");";
                            $insert_count = $insert_count + 1;
                        }else{
                            $la_code = str_replace('"', '', $dt[0]);
                            $notice_no = str_replace('"', '', $dt[1]);
                            $date = str_replace('"', '', $dt[2]);
                            $time = str_replace('"', '', $dt[3]);
                            $tran_type = str_replace('"', '', $dt[4]);
                            $due_date = str_replace('"', '', $dt[5]);
                            $easy_pay_no = str_replace('"', '', $dt[6]);
                            $amount = str_replace('"', '', $dt[7]);
                            $user_id = str_replace('"', '', $dt[8]);
                            $file_name = str_replace('"', '', $dt[9]);
                            $veh_reg_no = str_replace('"', '', $dt[10]);
                            $offence_date = str_replace('"', '', $dt[11]);
                            $offence_type = str_replace('"', '', $dt[13]);
                            $status = str_replace('"', '', $dt[14]);
                            $location = str_replace('"', '', $dt[15]);
                            $offence_time = str_replace('"', '', $dt[16]);
                            $effective_date = str_replace('"', '', $dt[17]);
                            $effective_amt = str_replace('"', '', $dt[18]);
                            $rep_date = str_replace('"', '', $dt[19]);
                            $rep_result_date = str_replace('"', '', $dt[20]);
                            $courtesy_date = str_replace('"', '', $dt[21]);
                            $summons_date = str_replace('"', '', $dt[22]);
                            $court_date = str_replace('"', '', $dt[23]);
                            $woa_date = str_replace('"', '', $dt[24]);
                            $notice_type = str_replace('"', '', $dt[25]);
                            $veh_make = str_replace('"', '', $dt[26]);
                            $veh_type = str_replace('"', '', $dt[27]);
                            $film_no = str_replace('"', '', $dt[28]);
                            $frame_no = str_replace('"', '', $dt[29]);
                            $woa_auth_date = str_replace('"', '', $dt[30]);
                            $notice_status = str_replace('"', '', $dt[31]);
                            $notice_stage = str_replace('"', '', $dt[32]);
                            $notice_number = str_replace('"', '', $dt[33]);
                            $speed = str_replace('"', '', $dt[34]);
                            $surname = str_replace('"', '', $dt[35]);
                            $name = str_replace('"', '', $dt[36]);
                            $court_code = str_replace('"', '', $dt[37]);
                            $court_description = str_replace('"', '', $dt[38]);
                            $zone_speed = str_replace('"', '', $dt[39]);

                            $sql = "UPDATE `tbl_fines` SET 
                            la_code = '".$la_code."',notice_no = '".$notice_no."',date = '".$date."',time = '".$time."',tran_type = '".$tran_type."',due_date = '".$due_date."',easypay_no = '".$easy_pay_no."',amount = '".$amount."',user_id = '".$user_id."',file_name = '".$file_name."',veh_reg_no = '".$veh_reg_no."',offence_date = '".$offence_date."',offence_type = '".$offence_type."',status = '".$status."',location = '".$location."',offence_time = '".$offence_time."',effective_date = '".$effective_date."',effective_amt = '".$effective_amt."',rep_date = '".$rep_date."',rep_result_date = '".$rep_result_date."',courtesy_date = '".$courtesy_date."',summons_date = '".$summons_date."',court_date = '".$court_date."',woa_date = '".$woa_date."',notice_type = '".$notice_type."',veh_make = '".$veh_make."',veh_type = '".$veh_type."',film_no = '".$film_no."',frame_no = '".$frame_no."',woa_auth_date = '".$woa_auth_date."',notice_status = '".$notice_status."',notice_stage = '".$notice_stage."',notice_number = '".$notice_number."',speed = '".$speed."',surname = '".$surname."',name = '".$name."',court_code = '".$court_code."',court_description = '".$court_description."',zone_speed = '".$zone_speed."' 
                            WHERE id = ".$chk[0]['id']." ";
                            $update_count = $update_count + 1;
                        }
                        $this->db->query($sql);
                        $bit = true;
                    }
                }
                fclose($handle);
            } else {
                echo "Not Open";
            }
            if($bit){
                $log['file_name'] = $file_name;
                $log['created_records'] = $insert_count;
                $log['updated_records'] = $update_count;
                $log['created_by'] = $this->session->userdata('admin_id');
                $this->admin_model->insertRecordByTable("tbl_upload_logs", $log);
            }
        }
        return redirect(base_url('admin/fines'));
    }
}
codeigniter mysql php
1 Answers

You should be using prepared statements, I use PDO as it was the easiest for me to understand but there are other methods.

This is how I start my connection:

    function dbc(){
        $DB_HOST = "localhost";
        $DB_NAME = "database name";
        $DB_USER = "username";
        $DB_PASSWORD = "password";
    
        try{
            $dbconn = new PDO("mysql:host=$DB_HOST;dbname=$DB_NAME", $DB_USER, $DB_PASSWORD);
            $dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }catch(PDOException $e){
            die("Error; $e");
        }
        return $dbconn;
    }

Then to execute a prepared statement, you do this (I started a little, but there are a lot of columns lol, you would also need to update the inputs (myinput_0-7) as i'm not sure if you have them as variables):

    try{
        $query = dbc()->prepare("INSERT INTO `tbl_fines` (id, la_code, notice_no, date, time, tran_type, etc) 
                                            VALUES (:id, :la_code, :notice_no, :date, :time, :tran_type, :etc)");
        $query->bindValue(':id', "myinput_0", PDO::PARAM_INT);
        $query->bindValue(':la_code', "myinput_1", PDO::PARAM_INT);
        $query->bindValue(':notice_no', "myinput_3", PDO::PARAM_INT);
        $query->bindValue(':date', "myinput_4", PDO::PARAM_INT);
        $query->bindValue(':time', "myinput_5", PDO::PARAM_INT);
        $query->bindValue(':tran_type', "myinput_6", PDO::PARAM_INT);
        $query->bindValue(':etc', "myinput_7", PDO::PARAM_STR);
        $query->execute();

        if($query->rowCount() > 0){
            echo "success";
        }else{
            echo "error";
        }
    }catch(Exception $e){ 
        echo "Error: $e"; 
    }
user_132319040
Related