How to check the condition using xls file in php

Question

Here i am using xls file reader in php it is working fine,i have make one logic,xls file i am geting the badge number(4565) and month(01-2017) now i want check the if the badge number and month already data exist or not , suppose data is exixts means i should not allow to insert the new record,suppose not exits means i want allow to insert the new record, how can do this , i am confusing because of xls sheet.

<?php
include 'excel_reader.php';// include the class
$groupfile ='test.xls';
$excel = new PhpExcelReader;
$excel->read($groupfile);
function sheetData($sheet) {
    $result = ''; 
    $row = 2;
    while($row <= $sheet['numRows']) {
        $id = isset($sheet['cells'][$row][1]) ? $sheet['cells'][$row][1] : ''; 
        $loan_month = isset($sheet['cells'][$row][2]) ? $sheet['cells'][$row][2] : ''; 
        $badge_number = isset($sheet['cells'][$row][3]) ? $sheet['cells'][$row][3] : '';
        $name = isset($sheet['cells'][$row][4]) ? $sheet['cells'][$row][4] : '';
        $pf_amount = isset($sheet['cells'][$row][5]) ? $sheet['cells'][$row][5] : '';
        $loan_amount = isset($sheet['cells'][$row][6]) ? $sheet['cells'][$row][6] : '';
        $date_format=date('d/m/Y');
        $status=0;
        $explode_date=explode("/",$loan_month) ;
        $month = $explode_date[1].'-'.$explode_date[2] ;
        $sql = mysql_query("INSERT INTO loan_history(pf_month,badge_number,first_name,pf_amount,loan_amount,reg_date,status)VALUES('$month','$badge_number','$name','$pf_amount','$loan_amount','$date_format','$status')");
        if($sql){
            echo "success" ;
        }else{
            echo mysql_error();
        }  
        $row++;
    }
}
$nr_sheets = count($excel->sheets);
for($i=0; $i<$nr_sheets; $i++) { 
    sheetData($excel->sheets[$i]);
}
?>

So there is a table in the DB where "badge_number" and "month_year" are primary columns? — Borjante, Feb 03 '17 at 09:07
My requirement is same month and same badge_number then only i want to stop ,suppose same badge_number but month is different means i want to insert the values how can do this — subikshan M, Feb 03 '17 at 09:20

score 0 · Answer 1 · edited Feb 04 '17 at 23:24

0

Correct me if wrong, but I understand that the constraint you have makes it perfect to make a multiple column primary key on the table.

You make a new constraint on the table:

ALTER TABLE foo
ADD CONSTRAINT pk_badge_month PRIMARY KEY (badge_number, month)

Now you can do what we call an upsert, it's much better performing than reading and deciding if we need to update or insert.
```
INSERT INTO foo (badge_number,month,other_column) VALUES (1,'one','won'), (2,'two','too')
```

edited Feb 04 '17 at 23:24

Nisse Engström

4,738
23
27
42

answered Feb 03 '17 at 09:16

Borjante

9,767
6
35
61

My requirement is same month and same badge_number then only i want to stop ,suppose same badge_number but month is different means i want to insert the values how can do this – subikshan M Feb 03 '17 at 09:20
See edited, basically if you have de DB manage this for you your life will be easier. That's in case you have access to the DB and can manage it – Borjante Feb 03 '17 at 09:22
Can u please update my answer,i am not able to get you please – subikshan M Feb 03 '17 at 09:31
Can you modify the database table structure or constraints? – Borjante Feb 03 '17 at 09:33
I didn't change anything because unique badge_number can insert multiple times in different month – subikshan M Feb 03 '17 at 09:39
It's pretty hard to help if you can't understand what I'm saying to you. Sorry – Borjante Feb 03 '17 at 09:43

How to check the condition using xls file in php

1 Answers1