Import CSV to MySQL - PHP Code

Question

CarterLangley 0 Newbie Poster

12 Years Ago

Hi guys,

I have the following code that I just cannot get to work!
Can you please let me know where I have gone wrong?

    <?php
    //db_connect.php
    $con=mysqli_connect("localhost","root","password","database");

    if (mysqli_connect_errno())
    {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    function get_file_extension($file_name) {
        return end(explode('.',$file_name));
    }

    function errors($error){
        if (!empty($error))
        {
                $i = 0;
                while ($i < count($error)){
                $showError.= '<div class="msg-error">'.$error[$i].'</div>';
                $i ++;}
                return $showError;
        }// close if empty errors
    } // close function


    if (isset($_POST['upfile'])){
    // check feilds are not empty

    if(get_file_extension($_FILES["uploaded"]["name"])!= 'csv')
    {
    $error[] = 'Only CSV files accepted!';
    }

    if (!$error){

    $tot = 0;
    $handle = fopen($_FILES["uploaded"]["tmp_name"], "r");
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        for ($c=0; $c < 1; $c++) {

                //only run if the first column if not equal to firstname
                if($data[0] !='firstname'){
                    mysql_query("INSERT INTO company(
                    company_name,
                    address_street,
                    address_city,
                    address_province,
                    address_postalcode,
                    address_country,
                    main_tel,
                    second_tel,
                    main_email,
                    second_email
                    )VALUES(
                        '".mysql_real_escape_string($data[0])."',
                        '".mysql_real_escape_string($data[1])."',
                        '".mysql_real_escape_string($data[2])."',
                        '".mysql_real_escape_string($data[3])."'
                        '".mysql_real_escape_string($data[4])."'
                        '".mysql_real_escape_string($data[5])."'
                        '".mysql_real_escape_string($data[6])."'
                        '".mysql_real_escape_string($data[7])."'
                        '".mysql_real_escape_string($data[8])."'
                        '".mysql_real_escape_string($data[9])."'
                        '".mysql_real_escape_string($data[10])."'
                    )")or die(mysql_error());
                }

        $tot++;}
    }
    fclose($handle);
    $content.= "<div class='success' id='message'> CSV File Imported, $tot records added </div>";

    }// end no error
    }//close if isset upfile

    $er = errors($error);
    $content.= <<<EOF;
    <h3>Import CSV Data</h3>
    $er;
    <form enctype="multipart/form-data" action="" method="post">
        File:<input name="uploaded" type="file" maxlength="20" /><input type="submit" name="upfile" value="Upload File">
    </form>
    EOF;
    echo $content;

mysql php

4 Contributors
6 Replies
1K Views
5 Years Discussion Span
Latest Post 7 Years Ago Latest Post by Ajit_786

Dani AI

Generated 10 Months Ago

Nice thread — a few targeted fixes and a safe workflow will get this working and make it maintainable when your sheet grows into multiple related tables.

First, immediate problems to fix (what and others were hinting at): do not mix the old mysql_* calls with a mysqli connection; your INSERT in the original post is missing commas between some VALUES which makes the SQL invalid; and end(explode(...)) is brittle for detecting extensions. Also check $_FILES['uploaded']['error'], validate count($data) from fgetcsv() and explicitly skip the header row. pointed you at mysqli OOP — good — but for relationships you should use prepared statements and transactions so partial imports do not corrupt your data.

A practical pattern:

Use one DB API (mysqli or PDO). Prefer prepared statements.
Validate CSV shape (column count, encoding, remove BOM).
Start a transaction, INSERT parent row(s), get the inserted id, then INSERT child rows using that id; commit at the end or rollback on error.
Log errors and test with a small sample first.

Example outline (mysqli, prepared + transaction):

// assume $conn = new mysqli(...);
$conn->begin_transaction();
$stmt = $conn->prepare(
  "INSERT INTO company
   (company_name,address_street,address_city,address_province,address_postalcode,address_country,main_tel,second_tel,main_email,second_email)
   VALUES (?,?,?,?,?,?,?,?,?,?)"
);
$stmt->bind_param("ssssssssss",$c,$s1,$s2,$prov,$pc,$country,$t1,$t2,$e1,$e2);
// loop fgetcsv(), skip header, validate count, assign and trim variables, $stmt->execute()
// $company_id = $conn->insert_id; insert related rows using prepared statements
$conn->commit();
$stmt->close();

If you want, post a small sample CSV (5 rows) and the three table schemas and I can sketch the prepared statements and the parent/child insert flow.

All 6 Replies

pritaeas 2,276 Most Valuable Poster

12 Years Ago

You are starting a mysqli connection, yet use a mysql query.

gabrielcastillo 40 Web Developer

12 Years Ago

$db = new mysqli('','','','');

$results = $db->query("SQL STATEMENT");

while($row = $results->fetch_assoc()){
echo $row['field_name'];
}

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

CarterLangley 0 Newbie Poster · Answer 1 · 2014-02-14T15:46:00+00:00

Ok that makes sense. How do I change the rest to mysqli? Is it as simple as changing mysql to mysqli in the statements?

CarterLangley 0 Newbie Poster · Answer 2 · 2014-02-14T18:17:41+00:00

With you. Will try that tomorrow and see where I get. Thanks!

CarterLangley 0 Newbie Poster · Answer 3 · 2014-02-17T10:12:26+00:00

Unfortunately the spreadsheet has evolved somewhat and now it is reflecting three tables linked with a relationship! Far beyond my capabilities in PHP. But thank you anyway!

Ajit_786 0 Newbie Poster · Answer 4 · 2019-07-08T07:08:45+00:00

Try this

<?php 
    $connect = mysqli_connect("localhost", "root", "", "testing");
    if(isset($_POST["submit"]))
{
    if($_FILES['file']['name'])
    {
        $filename = explode(".", $_FILES['file']['name']);
        if($filename[1] == 'csv')
        {
            $handle = fopen($_FILES['file']['tmp_name'], "r");
            while($data = fgetcsv($handle))
            {
                $item1 = mysqli_real_escape_string($connect, $data[0]);  
                $item2 = mysqli_real_escape_string($connect, $data[1]);
                $query = "INSERT into excel(excel_name, excel_phone) values('$item1','$item2')";
                mysqli_query($connect, $query);
            }
            fclose($handle);
            echo "<script>alert('Import done');</script>";
        }
    }
}
?>
<!DOCTYPE html>
<html>
<head>
    <title></title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
    <style>
        table{
            border-collapse: collapse;
            width: 100%;
            color: #588c7e;
            font-family: monospace;
            font-size: 25px;
            text-align: left;
        }
        th{
            background-color: #588c7e;
            color: white;
        }
        tr:nth-child(even) {background-color: #f2f2f2}
    </style>
</head>
<body>
    <form method="post" enctype="multipart/form-data">
        <div align="center">
            <label>Select CSV File:</label>
            <input type="file" name="file" />
            <br />
            <input type="submit" name="submit" value="Import" class="btn btn-info" />
        </div>
    </form><br><br>
</body>
</html>

Import CSV to MySQL - PHP Code

Dani AI

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers