我是PHP和MySQL(和web编程)的新手。感谢stackoverflow，我已经设法解决了我的大部分问题，你们提供的答案非常棒!
这一次我得到一个500内部服务器错误，每次我运行我的脚本。该脚本获取86个csv文件，并将它们逐个加载到MySQL中。当我一次将它们分成15个文件时，我已经设法将它们全部加载，但这不是一个最佳解决方案，因为当试图一次自动加载15个文件时，它不起作用。
根据其他stackoverflow解决方案，"LOAD DATA INFILE"选项可以解决这个问题，但是我在共享主机上，这个选项不可用。
请您看看我的脚本，告诉我我可以改进尝试
//for troubleshooting
ini_set('error_reporting', E_ALL);
ini_set("display_errors","1");
ini_set('max_execution_time', 0);
ini_set('memory_limit', '-1');
//Create connection
$db = new Database();
$db->query( "Select `CustomerId`
FROM DimCustomer
WHERE CanManageClients<>'Y' and CurrentCustomer ='Y'
ORDER BY CustomerNumber") ; //and `CustomerId` = '1798561799'
$rows = $db->resultset() ;
$numrows = $db->rowCount();
if (!($numrows == 0))
{
for ($row = 0; $row < $numrows; $row++)
{
$client_cust_id = $rows[$row]["CustomerId"];
$filePath = dirname(__FILE__) . '/KP/KP'.$client_cust_id.'.csv';
$linecount =0;
$data = array();
$csvfile = fopen($filePath, 'rb');
$db->beginTransaction();
$db->query("INSERT IGNORE INTO STAGINGAwKeywordsPerformance
(`AccountCurrencyCode`,
`AccountDescriptiveName`,
`AccountTimeZoneId`,
`AdGroupId`,
`AdGroupName`,
`AdGroupStatus`,
`AdNetworkType1`,
`AdNetworkType2`,
`ApprovalStatus`,
`AverageCpc`,
`AverageCpm`,
`AveragePosition`,
`CampaignId`,
`CampaignName`,
`CampaignStatus`,
`Clicks`,
`ClickType`,
`ConversionRate`,
`Conversions`,
`ConversionValue`,
`Cost`,
`CostPerConversion`,
`Ctr`,
`CustomerDescriptiveName`,
`Date`,
`DestinationUrl`,
`Device`,
`ExternalCustomerId`,
`FirstPageCpc`,
`KeywordId`,
`Impressions`,
`IsNegative`,
`KeywordMatchType`,
`KeywordText`,
`MaxCpc`,
`MaxCpm`,
`OriginalMaxCpc`,
`PercentCpa`,
`PrimaryCompanyName`,
`PrimaryUserLogin`,
`QualityScore`,
`KeywordStatus`,
`TopOfPageCpc`,
`ValuePerConversion`,
`ViewThroughConversions`)
VALUES
(:AccountCurrencyCode ,
:AccountDescriptiveName ,
:AccountTimeZoneId ,
:AdGroupId ,
:AdGroupName ,
:AdGroupStatus ,
:AdNetworkType1 ,
:AdNetworkType2 ,
:ApprovalStatus ,
:AverageCpc ,
:AverageCpm ,
:AveragePosition ,
:CampaignId ,
:CampaignName ,
:CampaignStatus ,
:Clicks ,
:ClickType ,
:ConversionRate ,
:Conversions ,
:ConversionValue ,
:Cost ,
:CostPerConversion ,
:Ctr ,
:CustomerDescriptiveName ,
:Date ,
:DestinationUrl ,
:Device ,
:ExternalCustomerId ,
:FirstPageCpc ,
:KeywordId ,
:Impressions ,
:IsNegative ,
:KeywordMatchType ,
:KeywordText ,
:MaxCpc ,
:MaxCpm ,
:OriginalMaxCpc ,
:PercentCpa ,
:PrimaryCompanyName ,
:PrimaryUserLogin ,
:QualityScore ,
:KeywordStatus ,
:TopOfPageCpc ,
:ValuePerConversion ,
:ViewThroughConversions )");
while(!feof($csvfile))
{
$data[] = fgetcsv($csvfile);
if ($linecount > 1 )
{
$db->bind(':AccountCurrencyCode',$data[$linecount][0], PDO::PARAM_STR, 20);
$db->bind(':AccountDescriptiveName',$data[$linecount][1], PDO::PARAM_STR, 20);
$db->bind(':AccountTimeZoneId',$data[$linecount][2], PDO::PARAM_STR, 20);
$db->bind(':AdGroupId',$data[$linecount][3], PDO::PARAM_STR, 20);
$db->bind(':AdGroupName',$data[$linecount][4], PDO::PARAM_STR, 20);
$db->bind(':AdGroupStatus',$data[$linecount][5], PDO::PARAM_STR, 20);
$db->bind(':AdNetworkType1',$data[$linecount][6], PDO::PARAM_STR, 20);
$db->bind(':AdNetworkType2',$data[$linecount][7], PDO::PARAM_STR, 20);
$db->bind(':ApprovalStatus',$data[$linecount][8], PDO::PARAM_STR, 20);
$db->bind(':AverageCpc',$data[$linecount][9], PDO::PARAM_STR, 20);
$db->bind(':AverageCpm',$data[$linecount][10], PDO::PARAM_STR, 20);
$db->bind(':AveragePosition',$data[$linecount][11], PDO::PARAM_STR, 20);
$db->bind(':CampaignId',$data[$linecount][12], PDO::PARAM_STR, 20);
$db->bind(':CampaignName',$data[$linecount][13], PDO::PARAM_STR, 20);
$db->bind(':CampaignStatus',$data[$linecount][14], PDO::PARAM_STR, 20);
$db->bind(':Clicks',$data[$linecount][15], PDO::PARAM_STR, 20);
$db->bind(':ClickType',$data[$linecount][16], PDO::PARAM_STR, 20);
$db->bind(':ConversionRate',$data[$linecount][17], PDO::PARAM_STR, 20);
$db->bind(':Conversions',$data[$linecount][18], PDO::PARAM_STR, 20);
$db->bind(':ConversionValue',$data[$linecount][19], PDO::PARAM_STR, 20);
$db->bind(':Cost',$data[$linecount][20], PDO::PARAM_STR, 20);
$db->bind(':CostPerConversion',$data[$linecount][21], PDO::PARAM_STR, 20);
$db->bind(':Ctr',$data[$linecount][22], PDO::PARAM_STR, 20);
$db->bind(':CustomerDescriptiveName',$data[$linecount][23], PDO::PARAM_STR, 20);
$db->bind(':Date',$data[$linecount][24], PDO::PARAM_STR, 20);
$db->bind(':DestinationUrl',$data[$linecount][25], PDO::PARAM_STR, 20);
$db->bind(':Device',$data[$linecount][26], PDO::PARAM_STR, 20);
$db->bind(':ExternalCustomerId',$data[$linecount][27], PDO::PARAM_STR, 20);
$db->bind(':FirstPageCpc',$data[$linecount][28], PDO::PARAM_STR, 20);
$db->bind(':KeywordId',$data[$linecount][29], PDO::PARAM_STR, 20);
$db->bind(':Impressions',$data[$linecount][30], PDO::PARAM_STR, 20);
$db->bind(':IsNegative',$data[$linecount][31], PDO::PARAM_STR, 20);
$db->bind(':KeywordMatchType',$data[$linecount][32], PDO::PARAM_STR, 20);
$db->bind(':KeywordText',$data[$linecount][33], PDO::PARAM_STR, 20);
$db->bind(':MaxCpc',$data[$linecount][34], PDO::PARAM_STR, 20);
$db->bind(':MaxCpm',$data[$linecount][35], PDO::PARAM_STR, 20);
$db->bind(':OriginalMaxCpc',$data[$linecount][36], PDO::PARAM_STR, 20);
$db->bind(':PercentCpa',$data[$linecount][37], PDO::PARAM_STR, 20);
$db->bind(':PrimaryCompanyName',$data[$linecount][38], PDO::PARAM_STR, 20);
$db->bind(':PrimaryUserLogin',$data[$linecount][39], PDO::PARAM_STR, 20);
$db->bind(':QualityScore',$data[$linecount][40], PDO::PARAM_STR, 20);
$db->bind(':KeywordStatus',$data[$linecount][41], PDO::PARAM_STR, 20);
$db->bind(':TopOfPageCpc',$data[$linecount][42], PDO::PARAM_STR, 20);
$db->bind(':ValuePerConversion',$data[$linecount][43], PDO::PARAM_STR, 20);
$db->bind(':ViewThroughConversions',$data[$linecount][44], PDO::PARAM_STR, 20);
$db->execute();
}
$linecount++;
}
$db->endTransaction();
fclose($csvfile);
}
}
$db = NULL;
die("END");
?>
大家好
try{
$db->execute(....);
}catch( 'PDOException $e ){
if($e->getCode() != 23000){ //you'll have to check this code, but pretty sure it's the correct one for duplicate key, basically you don't want to do the insert when the row is in the database right? this way you re-throw any other errors, as I believe IGNORE ignores all errors not just the duplicate key.
throw new 'PDOException($e->getMessage(), $e->getCode(), $e); //rethrow other errors.
}
}
我要做的另一个大的改变是为csv数组分配键，这样做的方法是按照它们在csv文件中的顺序创建一个db头数组。
$fields = array(
'AccountDescriptiveName',
'AccountTimeZoneId',
'AdGroupId',
'AdGroupName',
'AdGroupStatus',
.....
$placeholders = array_map(function( $item ){
//just adds the ':' for the placeholders.
return ':'.$item;
}, $fields);
while(false !== ( $data = fgetcsv($csvfile) ) ) //combine this all
{
//remove the nested array I don't think you need the line count bit, what you are doing there with the $data = array() is saving it all into an array and then writing it out, if you don't need the data later ( assuming this based on the die statement ) you can potentially save a lot of memory by recycling $data on each loop .
$data = array_combine($fields, $data);
.....
//note you might need to check this if you have blank lines. Otherwise it can blow up
if( count( $data ) == $num_fields ) //count fields outside the loop $num_fields = count( $fields );
$data = array_combine( $placeholders, $data);
那么你可以在你的查询
中这样做$sql = "INSERT INTO STAGINGAwKeywordsPerformance ( ".implode(",", $fields) ." )VALUES( ".implode(",", $placeholders)." ) ";
$stmt = $db->prepare( $sql ); //fix these. you had just query() which executes the query here and the you execute the query on the PDOStatement object no the PDO object
$stmt->execute ( $data );
也改变这个或重命名相同的变量在我的例子中(你会有问题，如果他们是相同的名称)，我总是使用像$ I或类似的增量。
for ($row = 0; $row < $numrows; $row++)
{
解释:
现在你写每个字段，所以如果你改变一个，有大约4个地方，你将不得不更新代码，也有很多空间拼写错误等，这种方式你只是改变字段，只要csv匹配你的好。
我还没有测试过这段代码，但我整天都在做同样的事情，基本上你想要的是这个数组结构的$data，它将更容易处理…
$data = array(
':AccountDescriptiveName' => 'my name',
':AccountTimeZoneId' => 62,
':AdGroupId' => 1,
':AdGroupName' => 'group1',
':AdGroupStatus' => 1,
.....