Upload CSV

[iceblox]

Hi All,

 

My script has been working but since i have added more feilds in i keep getting an error message.. Can anyone see anything wrong that i am missing?

 

 

$file = 'http://www.urmob.co.uk/feed/single.php?partner=mpdu&selection=A&cashback=A&fields=Offer.ID-Offer.PhoneCost-Offer.TotalCost-Offer.MonthlyCost-Offer.FreeGift-Offer.OfferCashback-Offer.AutoCashback-Offer.OfferRental-Offer.OfferLength-Offer.Link-Model.ID-Model.Make-Model.Name-Model.ImageLarge-Model.ImageSmall-Model.Popularity-Tariff.Name-Tariff.ContractLength-Tariff.Rental-Tariff.NiceName-Tariff.FreeMins-Tariff.FreeMinsType-Tariff.FreeTxts-Tariff.Allowance-Tariff.CostLLP-Tariff.CostLLO-Tariff.CostSNP-Tariff.CostSNO-Tariff.CostONP-Tariff.CostONO-Tariff.CostVMP-Tariff.CostVMO-Tariff.CostSMS-Tariff.CostMMS-Network.Name-Network.Image-Merchant.Name-Merchant.Image-Deal.Popularity&track=URMOB-xmake-xmodelx-xtariffx'; //file name here
//we read the CSV file here
$lines = file($file);

//now we take each line and explode it then insert to DB
foreach ($lines as $line) {
$cols = explode('"', trim($line));
mysql_query("INSERT INTO deals (ID, PhoneCost, TotalCost, MonthlyCost, FreeGift, OfferCashback, AutoCashback, OfferRental, OfferLength, OfferLink, ModelID, MakeName, ModelName, ImageLarge, ImageSmall, ModelPop, TariffName, ContractLength, TariffRental, NiceName, FreeMins, FreeMinsType, FreeTxts, Allowance, LLP, LLO, SNP, SNO, ONP, ONO, VMP, VMO, SMS, MMS, NetworkName, NetworkImage, MerchantName, MerchantImage, DealPop) VALUES ('$cols[1]', '$cols[3]', '$cols[5]', '$cols[7]', '$cols[9]', '$cols[11]', '$cols[13]', '$cols[15]', '$cols[17]', '$cols[19]', '$cols[21]', '$cols[23]', '$cols[25]', '$cols[27]', '$cols[29]', '$cols[31]', '$cols[33]', '$cols[35]', '$cols[37]', '$cols[39]', '$cols[41]', '$cols[43]', '$cols[45]', '$cols[47]', '$cols[49]', '$cols[51]', '$cols[53]', '$cols[55]', '$cols[57]', '$cols[59]', '$cols[61]', '$cols[63]', '$cols[65]', '$cols[67]', '$cols[69]', $cols[71]', '$cols[73]', '$cols[75]', '$cols[77]')")or
die(mysql_error());
}

 

 

This is the error message I am getting

 

 

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '://www.urmob.co.uk/i/networks/Vodafone.gif', 'Mobile Shop', 'http://www.urmob.co' at line 1 

 

These relate to; NetworkImage, MerchantName, MerchantImage

 

Any help would be appreciated

[Kieran Menor]

I don't understand why you are exploding by double quotes. CSV files are comma/semicolon/whatever seperated. The double quotes enclose the column data, so that the program reading the file knows that it should treat the seperator like text until another double quote has been reached. Furthermore, the double quotes can be escaped to tell the program to treat it like text, as well.

 

Long story short, you will need a slightly more sophisticated reader for your CSV file.

 

One last thing, it would be reasonable to assume that the column data should be escaped with addslashes() before it is inserted.

[Kieran Menor]

You can try this. It should work, but I'm not entirely sure, as I took it from a data inserter I made a while ago.

 

<?php
// Establish database connection here

// Data
$file = 'http://www.urmob.co.uk/feed/single.php?partner=mpdu&selection=A&cashback=A&fields=Offer.ID-Offer.PhoneCost-Offer.TotalCost-Offer.MonthlyCost-Offer.FreeGift-Offer.OfferCashback-Offer.AutoCashback-Offer.OfferRental-Offer.OfferLength-Offer.Link-Model.ID-Model.Make-Model.Name-Model.ImageLarge-Model.ImageSmall-Model.Popularity-Tariff.Name-Tariff.ContractLength-Tariff.Rental-Tariff.NiceName-Tariff.FreeMins-Tariff.FreeMinsType-Tariff.FreeTxts-Tariff.Allowance-Tariff.CostLLP-Tariff.CostLLO-Tariff.CostSNP-Tariff.CostSNO-Tariff.CostONP-Tariff.CostONO-Tariff.CostVMP-Tariff.CostVMO-Tariff.CostSMS-Tariff.CostMMS-Network.Name-Network.Image-Merchant.Name-Merchant.Image-Deal.Popularity&track=URMOB-xmake-xmodelx-xtariffx';
$table = "deals"; // MySQL table to insert into
$fields = "ID, PhoneCost, TotalCost, MonthlyCost, FreeGift, OfferCashback, AutoCashback, OfferRental, OfferLength, OfferLink, ModelID, MakeName, ModelName, ImageLarge, ImageSmall, ModelPop, TariffName, ContractLength, TariffRental, NiceName, FreeMins, FreeMinsType, FreeTxts, Allowance, LLP, LLO, SNP, SNO, ONP, ONO, VMP, VMO, SMS, MMS, NetworkName, NetworkImage, MerchantName, MerchantImage, DealPop";
$s = file_get_contents($file);

$rs = "\n"; // Row seperator
$cs = ","; // Column seperator
$esc = "\\"; // Escape character
$quot = "\""; // Column data encloser

// Parse input
$row = 0;
$col = 0;
$data = array();
$escape = false;
$quote = false;
for($i = 0; $i < strlen($s); $i++) {
if(!empty($quot) && ($s{$i} == substr($quot, 0, 1)) && !$escape) {
	$quote = !$quote;
}
elseif(!empty($quot) && ($s{$i} == $esc) && !$escape) {
	$escape = true;
}
elseif(($s{$i} == $cs) && !$quote) {
	$col++;
	$escape = false;
}
elseif(($s{$i} == $rs) && !$quote) {
	$col = 0;
	$row++;
	$escape = false;
}
else {
	$data[$row][$col] .= $s{$i};
	$escape = false;
}
}

function prepare($v) {
$v = is_array($v) ? array_map("prepare", $v) : addslashes(trim($v));
return $v;
}

// Insert the data
foreach($data as $row) {
$row = prepare($row);
$query = "INSERT INTO ".$table." (".$fields.") VALUES ('".implode("', '", $row)."')";
echo "Executing \"".$query."\"... ";
if(mysql_query($query)) {
	echo "<span style=\"color: green;\">Success!</span><br>\r\n";
}
else {
	echo "<span style=\"color: red;\">Failed!</span> Reason: ".mysql_error()."<br>\r\n";
}
}

?>

[iceblox]

HI Boom.dk,

 

Thanks for your input, I have tried that script you put up and had no joy. I understand what your saying about my simple script. The most annoying thing is it works and still does as long as remove the 10 extra rows i added. Which would make me think i havent code it right but i have checked it so many times, I cant see any mistakes..

 

Im getting this error with the script you posted

 

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 35 bytes)

[mwasif]

Consider the use of fgetcsv().

[MadTechie]

<?php
$file = 'http://www.urmob.co.uk/feed/single.php?partner=mpdu&selection=A&cashback=A&fields=Offer.ID-Offer.PhoneCost-Offer.TotalCost-Offer.MonthlyCost-Offer.FreeGift-Offer.OfferCashback-Offer.AutoCashback-Offer.OfferRental-Offer.OfferLength-Offer.Link-Model.ID-Model.Make-Model.Name-Model.ImageLarge-Model.ImageSmall-Model.Popularity-Tariff.Name-Tariff.ContractLength-Tariff.Rental-Tariff.NiceName-Tariff.FreeMins-Tariff.FreeMinsType-Tariff.FreeTxts-Tariff.Allowance-Tariff.CostLLP-Tariff.CostLLO-Tariff.CostSNP-Tariff.CostSNO-Tariff.CostONP-Tariff.CostONO-Tariff.CostVMP-Tariff.CostVMO-Tariff.CostSMS-Tariff.CostMMS-Network.Name-Network.Image-Merchant.Name-Merchant.Image-Deal.Popularity&track=URMOB-xmake-xmodelx-xtariffx'; //file name here
//we read the CSV file here
$lines = file($file);

//now we take each line and explode it then insert to DB
foreach ($lines as $line) {
$line = addcslashes($line, "'"); //Add this to clean up quotes
$cols = explode('"', trim($line));
mysql_query("INSERT INTO deals (ID, PhoneCost, TotalCost, MonthlyCost, FreeGift, OfferCashback, AutoCashback, OfferRental, OfferLength, OfferLink, ModelID, MakeName, ModelName, ImageLarge, ImageSmall, ModelPop, TariffName, ContractLength, TariffRental, NiceName, FreeMins, FreeMinsType, FreeTxts, Allowance, LLP, LLO, SNP, SNO, ONP, ONO, VMP, VMO, SMS, MMS, NetworkName, NetworkImage, MerchantName, MerchantImage, DealPop) VALUES ('$cols[1]', '$cols[3]', '$cols[5]', '$cols[7]', '$cols[9]', '$cols[11]', '$cols[13]', '$cols[15]', '$cols[17]', '$cols[19]', '$cols[21]', '$cols[23]', '$cols[25]', '$cols[27]', '$cols[29]', '$cols[31]', '$cols[33]', '$cols[35]', '$cols[37]', '$cols[39]', '$cols[41]', '$cols[43]', '$cols[45]', '$cols[47]', '$cols[49]', '$cols[51]', '$cols[53]', '$cols[55]', '$cols[57]', '$cols[59]', '$cols[61]', '$cols[63]', '$cols[65]', '$cols[67]', '$cols[69]', $cols[71]', '$cols[73]', '$cols[75]', '$cols[77]')")or
die(mysql_error());
}

?>

 

 

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 35 bytes)

means out of memory maybe try increasing the memory ie

ini_set("memory_limit","32M");