Unable to save data in each category - PHP CSV

Question

I have a CSV file from which I want to insert data in the database.

There are some scenarios involved in it.

Like, If category exists in the database, the script simple insert all video of that category in the database without inserting the category in the category table.

And if I new category comes in, the script simple save that category once in the category table and then save all his respective videos in the respective videos table.

My problem is I am unable to do this because it keep saving category number of time when it should only once. Means inserting one category in database and save his respective videos in videos table.

Same for when category exists, it simply insert its videos without inserting its category.

The script I am working on is:

<?php 
ini_set('display_errors','On');
ini_set("memory_limit", -1);
set_time_limit(0);
include("includes/header.php");
include("conn.php");
?>
<form name="frm_coupon" id="frm_coupon" action="" method="post" enctype="multipart/form-data">
  <div class="element">
    <label for="name">Upload CSV File: <span class="red">(required)</span>Only File format files (.CSV)</label>
    <input  name="file" type="file" id="file"  class="text"/><br /><br />
    <input name="submitBtn" type="submit">
  </div>
</form>
<?php
if(isset($_POST['submitBtn']) && $_POST['submitBtn']!=""){
$today  = date("Y-m-d");
$file   = $_FILES["file"]["name"];
$source = $_FILES["file"]["tmp_name"];
$target = "../assets/styles/csvFile/".$file;
$asdss = array();
move_uploaded_file($source, $target);

$seletrec = mysqli_query("SELECT `catName` FROM `web_category`");

$ar = array();
while($rec = mysqli_fetch_array($seletrec))
{
    array_push($ar,$rec['catName']);
}

if (($handle = fopen($target, "r")) !== FALSE) 
{
    $i=0;
    while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) 
    {
        $i++;
        if($i==1) continue;
        /*echo "<pre>";
        print_r($data);
        echo "</pre>"; die;*/
        //var_dump($data);
        if(!in_array($data[1],$ar))
        {
            //echo " IF ";
            $vCode   = explode("=", $data[4]);
            $vidCode = $vCode[1]; 

            $sdsdsd     = mysqli_query("SELECT `catID` FROM `web_category` WHERE `catName` = '".$data[1]."'");
            $cscssc = mysqli_fetch_array($sdsdsd);
            $videocid     = $cscssc['catID'];

            $sdf = mysqli_query("SELECT * FROM `web_videos` WHERE catID = '".$data[1]."'");
            $ds = mysqli_fetch_array($sdf);
            $videoCode    = $ds['catID'];

            if($videocid!=$videoCode){

            $query  = mysqli_query("INSERT INTO `web_category` SET `catName` = '".$data[1]."'");
            $getid  = mysqli_insert_id();

            $insert = mysqli_query("INSERT `web_videos` SET
             `vName` = '".mysqli_real_escape_string($data[1])."',
             `catID` = '".$videocid."',
             `videoName` = '".mysqli_real_escape_string($data[2])."',
             `vCreated` = '".$today."',
             `videoLink` = '".$data[4]."',
             `videoCode` = '".$vidCode."', `videoUpload` = ''");
            }else{
            //$query    = mysqli_query("INSERT INTO `web_category` SET `catName` = '".$data[1]."'");
            //$getid    = mysqli_insert_id();

            $insert = mysqli_query("INSERT `web_videos` SET
             `vName` = '".mysqli_real_escape_string($data[1])."',
             `catID` = '".$videocid."',
             `videoName` = '".mysqli_real_escape_string($data[2])."',
             `vCreated` = '".$today."',
             `videoLink` = '".$data[4]."',
             `videoCode` = '".$vidCode."', `videoUpload` = ''");    

                }
        }
        else if(in_array($data[1],$ar))
        {

            $vCode   = explode("=", $data[4]);
            $vidCode = $vCode[1]; 
            $vidsCode = $data[2]; 

            $seletrec = mysqli_query("SELECT * FROM `web_category` WHERE catName = '".$data[1]."'");
            $ids      = mysqli_fetch_array($seletrec);
            $id       = $ids['catID'];

            $sdf = mysqli_query("SELECT * FROM `web_videos` WHERE vName = '".mysqli_real_escape_string($data[2])."'");
            $ds = mysqli_fetch_array($sdf);
            $videoCode    = $ds['vName'];

            if($videoCode!=$vidsCode){
            $insert = mysqli_query("INSERT `web_videos` SET
             `vName` = '".mysqli_real_escape_string($data[2])."',
             `catID` = '".$id."',
             `videoName` = '".mysqli_real_escape_string($data[3])."',
             `vCreated` = '".$today."',
             `videoLink` = '".$data[4]."',
             `videoCode` = '".$vidCode."', `videoUpload` = ''");
                }
            }
        }
    }
 }
include("includes/footer.php");

Here is the csv file how it look like:

Here is how category table look like:

Here is how video table look like:

EDIT 04-06-2015

Here is the CSV data:

No.,Category,Life in Qatar 2012+B280:D288284,Iframe Code,Video Link
1,Emir Sheikh Cars,Emir Hamad Bin Khalifa Al Thani in National Day 2010,"<iframe width=""740"" height=""4555"" src=""//www.youtube.com/embed/Ewksi3sNNFo"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=Ewksi3sNNFo
2,Emir Sheikh Cars,Arab Sheikh's car garage (2007),"<iframe width=""740"" height=""455"" src=""//www.youtube.com/embed/c0fNKdgUcCk"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=c0fNKdgUcCk
3,Emir Sheikh Cars,G63 AMG Mercedes-Benz No. 1 of Sheikh Mohammed bin Rashid Al Maktoum's,"<iframe width=""740"" height=""455"" src=""//www.youtube.com/embed/2qfIoQqjNpQ"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=2qfIoQqjNpQ
4,Cars,the cars of Dubai princes,"<iframe width=""740"" height=""315"" src=""//www.youtube.com/embed/dyr4SSlq4J8"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=dyr4SSlq4J8
5,Cars,UAE Unveils World's Most Expensive Car: Gold and diamond Lamborghini goes on show in Dubai,"<iframe width=""740"" src=""//www.youtube.com/embed/ihUOvVyr1DQ"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=ihUOvVyr1DQ
6,Cars,"BMW X6 gold.  UAE president's son, a car made ??of gold","<iframe width=""740"" height=""315"" src=""//www.youtube.com/embed/NfH2X1_pZyw"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=NfH2X1_pZyw
7,Cars,the cars of Dubai princes,"<iframe width=""740"" height=""315"" src=""//www.youtube.com/embed/dyr4SSlq4J8"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=dyr4SSlq4J8
8,Cars,Sheikh's Pyramid of Cars,"<iframe width=""740"" height=""315"" src=""//www.youtube.com/embed/LuDmCOFrId0"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=LuDmCOFrId0
9,Cars,"BMW X6 gold. UAE president son, a car made ??of gold","<iframe width=""740"" height=""315"" src=""//www.youtube.com/embed/NfH2X1_pZyw"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=NfH2X1_pZyw
10,Cars,the cars of Dubai princes,"<iframe width=""740"" height=""315"" src=""//www.youtube.com/embed/dyr4SSlq4J8"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=dyr4SSlq4J8
11,Cars,Rubix Car of Sheikh Hamad Bin Hamdan AL NAHYAN,"<iframe width=""740"" height=""455"" src=""//www.youtube.com/embed/b59mWQ90_k4"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=b59mWQ90_k4
12,Cars,MARIFE rent a car-EMIR DE QATAR,"<iframe width=""740"" height=""455"" src=""//www.youtube.com/embed/PeayBYn1W6M"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=PeayBYn1W6M
13,Cars,black spider designed by sheikh hamad bin hamdan bin mohamed al nahyan,"<iframe width=""740"" height=""455"" src=""//www.youtube.com/embed/V-3ArmHGyd0"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=V-3ArmHGyd0
14,Cars,Sheikh Hamad Bin Hamdan Al Nahyan,"<iframe width=""740"" height=""455"" src=""//www.youtube.com/embed/3QFNIJmsCK8"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=3QFNIJmsCK8
15,Cars,Shiekh Hamad Bin Hamdan Al Nahyan LARGE SPIDER,"<iframe width=""740"" height=""455"" src=""//www.youtube.com/embed/_lB9tXTqBZw"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=_lB9tXTqBZw
16,Emir Palace,Billionaire Prince Al Waleed bin Talal PALACE - INSIDE LOOK,"<iframe width=""740"" height=""455"" src=""//www.youtube.com/embed/QIdYRI4ci4A"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=QIdYRI4ci4A
17,Emir Palace,Qatar Sheikh's Beautiful Palace,"<iframe width=""740"" height=""455"" src=""//www.youtube.com/embed/PWAfLLLoJuo"" frameborder=""0"" allowfullscreen></iframe>",http://www.youtube.com/watch?v=PWAfLLLoJuo

Here is the table structure of category table:

-- ----------------------------
-- Table structure for web_category
-- ----------------------------
DROP TABLE IF EXISTS `web_category`;
CREATE TABLE `web_category` (
  `catID` int(8) NOT NULL AUTO_INCREMENT,
  `catName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`catID`)
) ENGINE=MyISAM AUTO_INCREMENT=2125 DEFAULT CHARSET=latin1;

Here is the table structure of video table:

-- ----------------------------
-- Table structure for web_videos
-- ----------------------------
DROP TABLE IF EXISTS `web_videos`;
CREATE TABLE `web_videos` (
  `vID` int(11) NOT NULL AUTO_INCREMENT,
  `catID` int(11) NOT NULL,
  `vName` varchar(100) NOT NULL,
  `videoName` varchar(255) NOT NULL,
  `videoUpload` varchar(255) NOT NULL,
  `videoLink` varchar(255) NOT NULL,
  `videoCode` varchar(50) NOT NULL,
  `vCreated` date NOT NULL,
  PRIMARY KEY (`vID`)
) ENGINE=InnoDB AUTO_INCREMENT=2580 DEFAULT CHARSET=latin1;

inside the `if(!in_array($data[1],$ar)){}else if(in_array($data[1],$ar)){}`, try printing the $ar value and check, the if loop works properly — logan Sarav, Jun 04 '15 at 12:08
ok from excel `$data[1]` is category name correct?, but inside `if(!in_array($data[1],$ar))` for the query `$sdf = mysqli_query("SELECT * FROM 'web_videos' WHERE catID = '".$data[1]."'");`, you have used it to find the data from videos table, based on provided images, i can only see the catID in it. how can you match the id[in DB] with name[from excel]? — logan Sarav, Jun 04 '15 at 12:17
Yes you are correct in saying that. I am only have catName from csv file so I am not matching category ID but his name from database and from csv file. Because once I insert it and then wants to insert videos based on last get id of category. But category should enter only once and same for videos. — Keep Coding, Jun 04 '15 at 12:22
ya i got that part "But category should enter only once and same for videos". will analyze the code you added and lets see what happens — logan Sarav, Jun 04 '15 at 12:39
Yes you are right. Means if category is already in the database, it should not add it to again in category table but videos of that in the video table. and again, if category is not in database, it should add that category once in the database and then add all of respective videos in the database against that category in video table. @loganSarav — Keep Coding, Jun 04 '15 at 12:51
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/79664/discussion-between-logan-sarav-and-keep-coding). — logan Sarav, Jun 04 '15 at 12:52

score 1 · Accepted Answer · edited Nov 12 '15 at 21:35

Could you please use this code and see if it helps. Have updated the code based on your requirements.

if(isset($_POST['submitBtn']) && $_POST['submitBtn']!=""){
    $today  = date("Y-m-d");
    $file   = $_FILES["file"]["name"];
    $source = $_FILES["file"]["tmp_name"];
    $target = "../assets/styles/csvFile/".$file;
    $asdss = array();
    move_uploaded_file($source, $target);
    if(($handle = fopen($target, "r")) !== FALSE){
      $i=0;
      while(($data = fgetcsv($handle, 10000, ",")) !== FALSE){
        $i++;
        if($i==1) continue;
        $sdsdsd     = mysqli_query("SELECT `catID` FROM `web_category` WHERE `catName` = '".$data[1]."'");
        $cscssc = mysqli_fetch_array($sdsdsd);
        $videocid     = $cscssc['catID'];
        if(empty($videocid)){
          $vCode   = explode("=", $data[4]);
          $vidCode = $vCode[1]; 

          $query   = mysqli_query("INSERT INTO `web_category` SET `catName` = '".$data[1]."'");

          $seletrec = mysqli_query("SELECT * FROM `web_category` WHERE catName = '".$data[1]."'");
          $ids      = mysqli_fetch_array($seletrec);
          $id       = $ids['catID'];

          $insert = mysqli_query("INSERT `web_videos` SET
         `vName` = '".mysqli_real_escape_string($data[2])."',
         `catID` = '".$id."',
         `videoName` = '".mysqli_real_escape_string($data[3])."',
         `vCreated` = '".$today."',
         `videoLink` = '".$data[4]."',
         `videoCode` = '".$vidCode."', `videoUpload` = ''");

        }else{

          $vCode   = explode("=", $data[4]);
          $vidCode = $vCode[1]; 
          $vidsCode = $data[2]; 

          $seletrec = mysqli_query("SELECT * FROM `web_category` WHERE catName = '".$data[1]."'");
          $ids      = mysqli_fetch_array($seletrec);
          $id       = $ids['catID'];

          $sdf = mysqli_query("SELECT * FROM `web_videos` WHERE vName = '".mysqli_real_escape_string($data[2])."'");
          $ds = mysqli_fetch_array($sdf);
          $videoCode    = $ds['vName'];

          if($videoCode!=$vidsCode){
            $insert = mysqli_query("INSERT `web_videos` SET
            `vName` = '".mysqli_real_escape_string($data[2])."',
            `catID` = '".$id."',
            `videoName` = '".mysqli_real_escape_string($data[3])."',
            `vCreated` = '".$today."',
            `videoLink` = '".$data[4]."',
            `videoCode` = '".$vidCode."', `videoUpload` = ''");
            }
         }
      }
   }
}

Thats really solved my problem. you really life saver. thanks for all help. — Keep Coding, Jun 04 '15 at 14:27

Unable to save data in each category - PHP CSV

1 Answers1