CSV uploading without browse button

Question

I want to upload the contents of a CSV while one PHP page is running. I don't want any browse button to upload the CSV. Whenever the page is running the page should find the CSV which the path is already defined in the PHP page and contents should be inserted into the table. Now I am getting error related with fopen.

Here is my code

<?php

//database connection details
$connect = mysql_connect('localhost', 'root', '');

if (!$connect) {
    die('Could not connect to MySQL: ' . mysql_error());
}

//your database name
$cid = mysql_select_db('test', $connect);

// path where your CSV file is located


define('CSV_PATH', 'D:/xamp/htdocs/test/');

// Name of your CSV file
$csv_file = CSV_PATH . "test.csv";

echo $csv_file;
if (($handle = fopen($csv_file, "r")) !== FALSE) {
    fgetcsv($handle);
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        for ($c = 0; $c < $num; $c++) {
            $col[$c] = $data[$c];
        }

        $col1 = $col[0];
        $col2 = $col[1];
        $col3 = $col[2];
        $col4 = $col[3];
        $col5 = $col[4];
        $col6 = $col[5];


        // SQL Query to insert data into DataBase
        $query = "INSERT INTO testcsv(Line,Part No,Make,Model,Year,Part Type) VALUES('" . $col1 . "','" . $col2 . "','" . $col3 . "','" . $col4 . "','" . $col5 . "','" . $col6 . "')";
        $s = mysql_query($query, $connect);
    }
    fclose($handle);
}

echo "File data successfully imported to database!!";
mysql_close($connect);
?>

I am getting this error

Warning: fopen(D:/xamp/htdocs/test/test.csv): failed to open stream: No such file or directory in D:\xamp\htdocs\test\test.php on line 22 File data successfully imported to database!!

Can anyone help me?

Answer 1

I'm not sure why you are getting that particular error - one might assume that the file does not exist or that the directory is not readable but you are using the now deprecated mysql_ functions and directly embedding variables in the sql - thus making it vulnerable to sql injection. However, as this looks to be only a test that is probably not an issue.

The preferred method for this type of thing would be to use either mysqli or PDO in conjunction with prepared statements - below is an example of how you might implement that - I tested this with different data and database details and it seemed to work fine.

define('CSV_PATH','D:/xamp/htdocs/test/');
$filepath = CSV_PATH . "test.csv"; 

/* database connection details */
$host   =   'localhost';
$uname  =   'xxx'; 
$pwd    =   'xxx'; 
$db     =   'xxx';

/* create db connection */
$con    =   new mysqli( $host, $uname, $pwd, $db );

/* construct required sql statement */
$sql='insert into `testcsv` (`Line`,`Part No`,`Make`,`Model`,`Year`,`Part Type`) values (?,?,?,?,?,?)';

/* create prepared statement */
$stmt=$con->prepare( $sql );


if( !$stmt ){

    echo 'error preparing sql statement!';
    $con->close();

} else {

    /* bind the columns to variables which will be populated later */
    /* use "i" for integer and "s" for string values */
    $stmt->bind_param( 'ssssss', $line,$part,$make,$model,$year,$type );

    /* access csv file */
    $file=new SplFileObject( $filepath );

    /* Process each row of the csv file */
    while( !$file->eof() ) {

        /* read the line into a variable */
        $data=$file->fgetcsv();

        if( !empty( $data ) ){
            /* assign a variable to each field value for this row */
            list( $line,$part,$make,$model,$year,$type )=$data;

            /* execute statement with the now defined variables */
            $stmt->execute();
        }
    }

    /* tidy up */
    $stmt->close();
    $con->close();

    echo 'database updated with new records from csv';
}