Import CSV data into MySQL database fields

advertisements

I am trying to upload a CSV file which contains the fields stated in the link below [see CSV example] via a web form. The user can browse their computer by clicking the upload button, select their CSV file and then click upload which then imports the CSV data into the database in their corresponding fields. At the moment when the user uploads their CSV file, the row is empty and doesn't contain any of the data that the CSV file contains. Is there a way i could solve this so that the data inside the CSV file gets imported to the database and placed in its associating fields?

CSV example:
http://i754.photobucket.com/albums/xx182/rache_R/Screenshot2014-04-10at145431_zps80a42938.png

uploads.php

    <!DOCTYPE html>
<head>
    <title>MySQL file upload example</title>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
    <form action="upload_file.php" method="post" enctype="multipart/form-data">
        <input type="file" name="uploaded_file"><br>
        <input type="submit" value="Upload file">
    </form>
    <p>
        <a href="list_files.php">See all files</a>
    </p>
</body>
</html>

upload_file.php

    <?php
// Check if a file has been uploaded
if(isset($_FILES['uploaded_file'])) {
    // Make sure the file was sent without errors
    if($_FILES['uploaded_file']['error'] == 0) {
        // Connect to the database
        $dbLink = new mysqli('localhost', 'root', 'vario007', 'spineless');
        if(mysqli_connect_errno()) {
            die("MySQL connection failed: ". mysqli_connect_error());
        }

        // Gather all required data
        $filedata= file_get_contents($_FILES  ['uploaded_file']['tmp_name']); //this imports the entire file.

        // Create the SQL query
        $query = "
            INSERT INTO `Retail` (
                `date`, `order_ref`, `postcode`, `country`, `quantity`, `packing_price`, `dispatch_type`, `created`
            )
            VALUES (
                '{$date}', '{$order_ref}', '{$postcode}', '{$country}', '{$quantity}', '{$packing_price}', '{$dispatch_type}', NOW()
            )";

        // Execute the query
        $result = $dbLink->query($query);

        // Check if it was successfull
        if($result) {
            echo 'Success! Your file was successfully added!';
        }
        else {
            echo 'Error! Failed to insert the file'
               . "<pre>{$dbLink->error}</pre>";
        }
    }
    else {
        echo 'An error accured while the file was being uploaded. '
           . 'Error code: '. intval($_FILES['uploaded_file']['error']);
    }

    // Close the mysql connection
    $dbLink->close();
}
else {
    echo 'Error! A file was not sent!';
}

// Echo a link back to the main page
echo '<p>Click <a href="welcome.php">here</a> to go back</p>';
?>


try this

<?php
if(isset($_FILES['uploaded_file'])) {
    if($_FILES['uploaded_file']['error'] == 0) {
        $dbLink = new mysqli('localhost', 'root', 'vario007', 'spineless');
        if(mysqli_connect_errno()) {
            die("MySQL connection failed: ". mysqli_connect_error());
        }

        $file = $_FILES  ['uploaded_file']['tmp_name'];
        $handle = fopen($file, "r");
        $row = 1;
        while (($data = fgetcsv($handle, 0, ",","'")) !== FALSE)
        {
            if($row == 1)
            {
                // skip the first row
            }
            else
            {
                //csv format data like this
                //$data[0] = date
                //$data[1] = order_ref
                //$data[2] = postcode
                //$data[3] = country
                //$data[4] = quantity
                //$data[5] = packing_price
                //$data[6] = dispatch_type

                $query = "
                INSERT INTO `Retail` (
                `date`, `order_ref`, `postcode`, `country`, `quantity`, `packing_price`, `dispatch_type`, `created`
                )
                VALUES (
                '".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '".$data[4]."', '".$data[5]."', '".$data[6]."', NOW()
                )";
                $result = $dbLink->query($query);

                 // Check if it was successfull
                if($result) {
                    echo 'Success! Your file was successfully added!';
                }
                else {
                    echo 'Error! Failed to insert the file'
                    . "<pre>{$dbLink->error}</pre>";
                 }
            }
            $row++;
        }

    }
    else {
        echo 'An error accured while the file was being uploaded. '
           . 'Error code: '. intval($_FILES['uploaded_file']['error']);
    }

    // Close the mysql connection
    $dbLink->close();
}
else {
    echo 'Error! A file was not sent!';
}

// Echo a link back to the main page
echo '<p>Click <a href="welcome.php">here</a> to go back</p>';
?>

fgetcsv() in third argument in separator to you want to save in csv file. Ex: comma,semicolon or etc.