How to import excel file to 2 tables using php

Question

Sophia_1 0 Junior Poster in Training

8 Years Ago

Hi everyone, am trying to import excel file to 2 tables using php but unable to do so. The 1st table is Kra (kraid,kra) and 2nd table Kpi (kpiid,kpi,kraid). The foreign key for Kpi is kraid. When i import the file the following message appears "Invalid File:Please upload CSV file.", eventhough have imported the correct csv file. Please advise. Thanks a lot.

            index.php
            <!DOCTYPE html> <?php 
            //  include 'db.php';
             $con=mysqli_connect("localhost","user","","pq");
            // Check connection
            if (mysqli_connect_errno()) {
              echo "Failed to connect to MySQL: " . mysqli_connect_error();
            }
            ?> <html lang="en"> <head> <meta charset="utf-8"> <title>Import Excel Using PHP </title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <meta name="description" content="Import Excel File Using php"> <link rel="stylesheet" href="css/bootstrap.min.css"> <link rel="stylesheet" href="css/bootstrap-responsive.min.css"> <link rel="stylesheet" href="css/bootstrap-custom.css"> </head> <body> <!-- Navbar
                ================================================== --> <div class="navbar navbar-inverse navbar-fixed-top"> <div class="navbar-inner"> <div class="container"> <a class="btn btn-navbar" data-toggle="collapse" data-target=".nav-collapse"> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </a> <a class="brand" href="#">Import Excel Using PHP</a> </div> </div> </div> <div id="wrap"> <div class="container"> <div class="row"> <div class="span3 hidden-phone"></div> <div class="span6" id="form-login"> <form class="form-horizontal well" action="import.php" method="post" name="upload_excel" enctype="multipart/form-data"> <fieldset> <legend>Import CSV/Excel file</legend> <div class="control-group"> <div class="control-label"> <label>CSV/Excel File:</label> </div> <div class="controls"> <input type="file" name="file" id="file" class="input-large"> </div> </div> <div class="control-group"> <div class="controls"> <button type="submit" id="submit" name="Import" class="btn btn-primary button-loading" data-loading-text="Loading...">Upload</button> </div> </div> </fieldset> </form> </div> <div class="span3 hidden-phone"></div> </div> <table class="table table-bordered"> <thead> <tr> <th>Kra</th> <th>Kpi</th> </tr> </thead> <?php
                            $SQLSELECT = "SELECT kra,kpi FROM Kra,Kpi ";
                            $result_set =  mysql_query($SQLSELECT, $conn);
                            while($row = mysql_fetch_array($result_set))
                            {
                            ?> <tr> <td><?php echo $row['kra']; ?></td> <td><?php echo $row['kpi']; ?></td> </tr> <?php
                            }
                        ?> </table> </div> </div> </body> </html>
            -------------------------------------------------------------------------------------------------------------------------------------------------------------------

            import.php
            <?php
            if ($_FILES["file"]["error"] > 0)
            {
                echo "Error: " . $_FILES["file"]["error"] . "<br>";
            }
            else
            {
                echo "Upload: " . $_FILES["file"]["name"] . "<br>";
                echo "Type: " . $_FILES["file"]["type"] . "<br>";
                echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br>";
                //echo "Stored in: " . $_FILES["file"]["tmp_name"];
                $a=$_FILES["file"]["tmp_name"];
                //echo $a;  
                $connect = mysql_connect('localhost','user','');
            if (!$connect) {
            die('Could not connect to MySQL: ' . mysql_error());
            }   
            //your database name
            $cid =mysql_select_db('pq',$connect);

            // path where your CSV file is located
            //define('CSV_PATH','C:/xampp/htdocs/');
            //<!-- C:\\xampp\\htdocs -->
            // Name of your CSV file
            $csv_file = $a;

            if (($getfile = fopen($csv_file, "r")) !== FALSE) {
                     $data = fgetcsv($getfile, 1000, ",");
               while (($data = fgetcsv($getfile, 1000, ",")) !== FALSE) {
                 //$num = count($data);
                   //echo $num;
                    //for ($c=0; $c < $num; $c++) {
                        $result = $data;
                        $str = implode(",", $result);
                        $slice = explode(",", $str);

                        $kraid = $slice[0];
                        $kra = $slice[1];
                        $kpiid = $slice[2];
                        $kpi = $slice[3];


            $query = "INSERT INTO Kra(kraid, kra) VALUES('".$kraid."','".$kra."')";
            $s=mysql_query($query, $connect );

            $query1 = "INSERT INTO Kpi(kpiid,kpi,kraid) VALUES('".$kpiid."','".$kpi."','".$kraid."')";
            $s1=mysql_query($query1, $connect );
            }
            }
            echo "<script>alert('Record successfully uploaded.');window.location.href='index.php';</script>";
            //echo "File data successfully imported to database!!";
            mysql_close($connect);
            }
            ?>

mysql php

3 Contributors
5 Replies
1K Views
1 Week Discussion Span
Latest Post 8 Years Ago Latest Post by Sophia_1

diafol

8 Years Ago

Still using dead (mysql) functions?

For every record do a mysql_insert_id(): http://php.net/manual/en/function.mysql-insert-id.php

That gives you the Primary Key (autoincrement value). Just use that in your subsequent queries.

diafol

8 Years Ago

She asked here not stackexchange.

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.

Sophia_1 0 Junior Poster in Training · Answer 1 · 2016-01-22T06:51:20+00:00

Hi everyone, managed to import records in 2 tables using php code below. However, i was unable to connect the user and detail table with the correct foreign key. Example user1 (id=1) supposed to have relationship with
post1 (fk id=1) & post2 (fk id=1), but the foreign key is autoincrement in the mysql after import. Please advise. Thanks a lot.

        <?php
        mysql_connect("localhost", "user", "") 
        or die(mysql_error());   
        mysql_select_db("test") or die(mysql_error());    

        //Upload File
        if (isset($_POST['submit'])) {
            if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
                echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
                echo "<h2>Displaying contents:</h2>";
                readfile($_FILES['filename']['tmp_name']);
            }
            //Import uploaded file to Database
            $handle = fopen($_FILES['filename']['tmp_name'], "r");
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $import1="INSERT into user(name) values('$data[0]')";
                mysql_query($import1) or die(mysql_error());
                 $id = mysql_insert_id();

            $import2="INSERT into detail(post,id) values('$data[1]','$id')";
                mysql_query($import2) or die(mysql_error());


        }
            fclose($handle);
            print "Import done";
            //view upload form
        }else
         {
            print "Upload new csv by browsing to file and clicking on Upload<br />\n";
            print "<form enctype='multipart/form-data' action='import_test.php' method='post'>";
            print "File name to import:<br />\n";
            print "<input size='50' type='file' name='filename'><br />\n";
            print "<input type='submit' name='submit' value='Upload'></form>";
        }
        ?>

IT-INDIA.com 7 Newbie Poster · Answer 2 · 2016-01-22T09:48:26+00:00

Solution 1: Save the excel file as .csv file and then try to do the coding thing.

Solution 2: Stackexchange is best place for asking code fixes like this

Sophia_1 0 Junior Poster in Training · Answer 3 · 2016-01-26T01:11:25+00:00

Hi, thanks for your replies. Have changed the code as below but now it only display the 1st foreign key for all the records,where it does not display the correct foreign key for officer3 & officer4 (fk supposed to be id=201 but now fk id=199). Attached is the output. Please advise.

            <?php
            mysql_connect("localhost", "user", "") 
            or die(mysql_error());   
            mysql_select_db("test") or die(mysql_error());    

            //Upload File
            if (isset($_POST['submit'])) {
                if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
                    echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
                    echo "<h2>Displaying contents:</h2>";
                    readfile($_FILES['filename']['tmp_name']);
                }
                //Import uploaded file to Database
                $handle = fopen($_FILES['filename']['tmp_name'], "r");
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

                $import="INSERT into user(name) values('$data[0]')";
                mysql_query($import) or die(mysql_error());     

                $sql1 = mysql_query("SELECT * FROM user");
                $result = mysql_fetch_assoc($sql1);
                $id = $result['id'];

            $import="INSERT into detail(post,id) values('$data[1]','$id')";
                mysql_query($import) or die(mysql_error());                 
            }
                fclose($handle);
                print "Import done";
                //view upload form
            }else
             {
                print "Upload new csv by browsing to file and clicking on Upload<br />\n";
                print "<form enctype='multipart/form-data' action='import_test.php' method='post'>";
                print "File name to import:<br />\n";
                print "<input size='50' type='file' name='filename'><br />\n";
                print "<input type='submit' name='submit' value='Upload'></form>";
            }
            ?>