phpmysqlcsvfputcsv
Unable to save data in each category - PHP CSV
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;
Solution
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` = ''");
}
}
}
}
}
- Fixed shortcode download button for all digital product WooCommerce
- Laravel IN Validation or Validation by ENUM Values
- extra numbers attached to my values (PHP)
- Add shortcode below short description only for WooCommerce products having a specific tag
- Convert flat array of delimited key path strings to a multidimensional array with each path terminating at an empty array
- Routes works on localhost but not on production server
- Why are some checkout hooks not working in WooCommerce?
- Get user last purchase date from specific product in WooCommerce
- How to append data to file using file_put_contents()?
- Explode a string with two delimiters into an array
- Woocommerce custom order status with customized email notification
- In PHP, how to recurse through a Closure when the variable name holding the anonymous function is a variable-variable
- Using a slash in the beginning of the role makes any different?
- file_get_contents(): SSL operation failed with code 1, Failed to enable crypto
- Does trigger_error interrupt script?
- PHP artisan migrate not creating new table
- Parse array of strings composed of dot.separated.keys into a multidimensional array
- Explode each string in a flat array
- API code error 400 - when trying to call API in php
- Parse a string with three delimiters into an associative array of indexed arrays
- How do I host a static website inside a Wordpress multisite installation?
- wordpress media image showing in front page - pagination issue
- What does "write failed: No space left on device" mean?
- PHP league/csv Reader How to know which delimiter was used?
- Adding to (or overriding) a core class method in cakePHP
- What would cause %5B0%5D to be added to the url
- Laravel 11 weird undefined array key "name" when sending email
- Zend Engine and Php Ext Folder
- Sort an array of associative arrays by column value
- Parse a multiline string with two delimiters per line into an array of associative rows