How to insert file_name as value in each row of mysql?

Question

I have this structure in my trend_table, I'm uploading a csv file to mysql server. I want to insert my file_name into date_sourced for every rows. How can I put file name as values for my date_sourced column

Example 2018-10-02_trx_result.csv is the name of my file so 2018-10-02 should be put for every rows when I upload my csv

date_sourced : date
sha1: varchar(255)
vsdt: varchar(255)
trendx:varchar(255)
notes:varchar(255)

PHP code for uploading my CSV.

<?php
if(isset($_POST['submit'])) {
    $host = 'localhost';
    $user = 'root';
    $password = '';
    $db = 'jeremy_db';
    ini_set('max_execution_time', 500);
    $con = mysqli_connect($host,$user,$password) or die('Could not' .mysqli_error($con));

    mysqli_select_db($con, $db) or die ('Could not' .mysqli_error($con));

    $file = $_FILES['file']['tmp_name'];
    $handle = fopen($file, "r");
    ini_set ('memory_limit', filesize ($file) + 4000000);
    $c = 0;

    while(($csvdata = fgetcsv($handle,10000,","))!== FALSE){
        $sha1 = $csvdata[0];
        $vsdt = $csvdata[1];
        $trendx  = $csvdata[2];

        $sql = "INSERT INTO jeremy_table_trend (sha1,vsdt,trendx) VALUES ('$sha1','$vsdt','$trendx')";
        $query = mysqli_query($con , $sql);

        $c = $c+1;
    }
    if($query){
        echo '<script type="text/javascript">'; 
        echo '      alert("CSV uploaded to server");'; 
        echo '      window.location.href = "trendx.php";';
        echo '</script>';
    }
    else { 
        echo "SLAM";
    }
}
?>

HTML code:

<form id = "myForm" class="ui input" enctype="multipart/form-data" method = "POST" action="trend_upload_csv.php" role = "form">
     <input type = "file" name ="file" id="file" size = "150">
     <input id="myBtn"  class="ui small red button" type = "submit" class = "btn btn-default"  name ="submit" onclick = "myFunction();" value = "Upload CSV"  disabled  /> 
</form>

Please help me I have no idea for trimming file name and inserting file name to mysql :(

so what problem you are facing exactly? – Alive to die - Anant Nov 05 '18 at 06:36 — Alive to die - Anant, Nov 05 '18 at 06:36
how can I put file name as values for my date column – Nov 05 '18 at 06:38 — , Nov 05 '18 at 06:38

score 1 · Accepted Answer · answered Nov 05 '18 at 06:46

1

If your filename is of the form 2018-10-02_trx_result.csv (i.e. a date followed by _ and the rest of the name), you can extract the date part using explode:

list($date) = explode('_', $_FILES['file']['name']);

Then you can just edit your query to include the value of $date:

$sql = "INSERT INTO jeremy_table_trend (date,sha1,vsdt,trendx) 
        VALUES ('$date','$sha1','$vsdt','$trendx')";

Note that you should really use prepared statements to guard against (for example) having data values with single quotes in them as well as other possible SQL injection attacks. See this answer.

answered Nov 05 '18 at 06:46

Nick

138,499
22
57
95

this gives me 0000-00-00 in my every rows – Nov 05 '18 at 06:57
That sounds like your filename is not in the form you mention - or perhaps there is a path on it? What happens if you try `list($date) = explode('_', basename($_FILES['file']['name']));` Can you echo `$_FILES['file']['name']` to see what is in it? – Nick Nov 05 '18 at 06:59
(T_ENCAPSED_AND_WHITESPACE), expecting '-' or identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) – Nov 05 '18 at 07:05
oh sorry my bad thanks! I was using other path i forgot :) – Nov 05 '18 at 07:08
Good to see it's working - did you need the `basename`? If so I'll update the answer. – Nick Nov 05 '18 at 07:09
no need my friend, Thankyou very much! appreciated it :) – Nov 05 '18 at 07:11

score 0 · Answer 2 · answered Nov 05 '18 at 06:52

0

You can do something like this,

$date = explode("_", !real filename here!)[0];

You can insert $date directly into your sql statement. Also make sure the date format in the file is always YYYY-MM-DD.

answered Nov 05 '18 at 06:52

kks21199

1,116
2
10
29

How to insert file_name as value in each row of mysql?

2 Answers2