0

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);
            }
            ?>
3
Contributors
5
Replies
23
Views
1 Year
Discussion Span
Last Post by Sophia_1
0

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>";
        }
        ?>
-1

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

0

She asked here not stackexchange.

0

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>";
            }
            ?>
Attachments user_table.png 22.59 KB detail_table.png 25.06 KB
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.