iceblox Posted March 23, 2008 Share Posted March 23, 2008 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 Quote Link to comment Share on other sites More sharing options...
Kieran Menor Posted March 23, 2008 Share Posted March 23, 2008 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. Quote Link to comment Share on other sites More sharing options...
Kieran Menor Posted March 23, 2008 Share Posted March 23, 2008 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"; } } ?> Quote Link to comment Share on other sites More sharing options...
iceblox Posted March 23, 2008 Author Share Posted March 23, 2008 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) Quote Link to comment Share on other sites More sharing options...
mwasif Posted March 23, 2008 Share Posted March 23, 2008 Consider the use of fgetcsv(). Quote Link to comment Share on other sites More sharing options...
MadTechie Posted March 23, 2008 Share Posted March 23, 2008 <?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"); Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.