Send null if key does not exists

Question

I have csv files what i parse to array and pass to mysql table. Some CSV files does not contains some columns what are in database and when i convert it to array than i have smaller number of columns that are in table and i get "syntax error".

From controller i call:

function sendHistoric(){
        $this->load->model('Historic_model');
        $this->load->library('csvreader');
        foreach($this->divisions as $div){
            $result =   $this->csvreader->parse_file("assets/csv/1516{$div}.csv");//path to csv file
            $this->Historic_model->loadCSVtoDB($result);
            //var_dump($result);
        }
     }

in model i have:

function loadCSVtoDB($data){
    $sql = "call ins_historic(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    foreach($data as $row){
        var_dump($row);
        $this->db->query($sql,$row);    
    }
    //echo $this->db->conn_id->error_message();
}

Can i somehow send NULL instead where he find that i have smaller number of data or i need to check if each element of array exists, if not that set it to null?

score 1 · Answer 1 · edited May 23 '17 at 12:06

1

One option to consider is not writing slower PHP looping but to harness the flexible code possibilties inside a LOAD DATA INFILE block, such as the processing seen in this answer link here. The functions, conversions possible are endless.

Maybe 6 of one, half dozen of another. But nice for simpler routines too, and I would suggest a fast data ingest.

Edit: (based on OP comment below)

create table t62
(   c1 int not null,
    c2 varchar(10) not null,
    c3 int not null,
    c4 char(5) not null,
    someOther int not null,
    -- an addtion 57 columns here
    primary key(c1,c2,c3,c4)
);

-- Mimic the LOAD DATA INFILE or PHP loop:
insert t62(c1,c2,c3,c4,someOther) values (1,'t',1,'01742',777);
insert t62(c1,c2,c3,c4,someOther) values (1,'t',1,'01742',777); -- error 1062: Dupe PK
insert t62(c1,c2,c3,c4,someOther) values (1,'t',2,'01742',777); -- happy

So I don't know, how would you suggest code would pick that composite PK for you? If so, how would that made-up-data play with its relationships with other data?

edited May 23 '17 at 12:06

Community

1
1

answered Sep 06 '15 at 11:30

Drew

24,851
10
43
78

I ll try like you describe, there 62 columns so i need to set each column for null if it is null... – DocNet Sep 06 '15 at 11:48
First 4 columns are primary key, can i set some default number or string intead null for just first 4 columns using this load ? – DocNet Sep 06 '15 at 11:55
I get Error Number: 28000/1045 Access denied for user 'user'@'localhost' (using password: YES) My server dont allows me to load data :S – DocNet Sep 06 '15 at 12:22
sounds like a plain-Jane access error, not sure what you are doing. You can issue the `LOAD DATA INFILE` from inside PHP after you authenticate, if that (LOAD DATA INFILE) is what you are doing. – Drew Sep 06 '15 at 12:25
SOLVED by using isset for each column, stupid but works :/ My server provider don't allows me to use LOAD FILE. – DocNet Sep 07 '15 at 10:55
right, it's a setting they can disable in ini / cnf – Drew Sep 07 '15 at 12:50

Send null if key does not exists

1 Answers1