我正在将从不同表收集的数据插入SQLite3数据库。但问题是，无论我做什么，我都会在数据库中多出一行
<?php
include_once '../app/config.php';
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
}
function masterLoop($db)
{
$mainTickerFile = fopen("../tickerMaster.txt", "r");
while (!feof($mainTickerFile)) {
$companyTicker = fgets($mainTickerFile);
$companyTicker = trim($companyTicker);
if ($companyTicker) {
$nextDayIncrease = 0;
$nextDayDecrease = 0;
$nextDayNoChange = 0;
$total = 0;
$sumOfIncreases = 0;
$sumOfDecreases = 0;
$sql = "SELECT date, amount_change, percent_change FROM " . $companyTicker . " WHERE percent_change > 0 ORDER BY date ASC";
$ret = $db->query($sql);
$multiArray = array();
$count = 0;
while ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
foreach ($row as $i=>$value) {
$multiArray[$count][$i] = $value;
}
$count++;
}
foreach ($multiArray as $data) {
$date = $data['date'];
$amount_change = $data['amount_change'];
$percent_change = $data['percent_change'];
$sql2 = "SELECT date, amount_change, percent_change FROM " . $companyTicker . " WHERE date > '" . $date . "' ORDER BY date ASC LIMIT 1";
$ret2 = $db->query($sql2);
$row2 = $ret2->fetchArray(SQLITE3_ASSOC);
$tom_date = $row2['date'];
$tom_amount_change = $row2['amount_change'];
$tom_percent_change = $row2['percent_change'];
if ($tom_percent_change > 0) {
$nextDayIncrease++;
$sumOfIncreases += $amount_change;
} elseif ($tom_percent_change < 0) {
$nextDayDecrease++;
$sumOfDecreases += $amount_change;
} else {
$nextDayNoChange++;
}
$total++;
}
}
$nextDayIncreasePercent = ($nextDayIncrease/$total)*100;
$nextDayDecreasePercent = ($nextDayDecrease/$total)*100;
$averageIncrease = $sumOfIncreases/$nextDayIncrease;
$averageDecrease = $sumOfDecreases/$nextDayDecrease;
insertIntoResultTable($db, $companyTicker, $nextDayIncrease, $nextDayIncreasePercent, $averageIncrease, $nextDayDecrease, $nextDayDecreasePercent, $averageDecrease);
}
}
function insertIntoResultTable($db, $companyTicker, $nextDayIncrease, $nextDayIncreasePercent, $averageIncrease, $nextDayDecrease, $nextDayDecreasePercent, $averageDecrease)
{
$buyValue = $nextDayIncreasePercent * $averageIncrease;
$sellValue = $nextDayDecreasePercent * $averageDecrease;
$createTableQuery = "CREATE TABLE IF NOT EXISTS analysisA (
companyTicker TEXT PRIMARY KEY NOT NULL,
nextDayIncrease INT,
nextDayIncreasePercent FLOAT,
averageIncrease FLOAT,
nextDayDecrease INT,
nextDayDecreasePercent FLOAT,
averageDecrease FLOAT,
buyValue FLOAT,
sellValue FLOAT
)";
$db->query($createTableQuery);
$insertQuery = $db->prepare("INSERT OR REPLACE INTO analysisA (companyTicker, nextDayIncrease, nextDayIncreasePercent, averageIncrease, nextDayDecrease, nextDayDecreasePercent, averageDecrease, buyValue, sellValue) VALUES (
:companyTicker, :nextDayIncrease, :nextDayIncreasePercent, :averageIncrease, :nextDayDecrease, :nextDayDecreasePercent, :averageDecrease, :buyValue, :sellValue
)");
$insertQuery->bindValue(':companyTicker', $companyTicker, SQLITE3_TEXT);
$insertQuery->bindValue(':nextDayIncrease', $nextDayIncrease, SQLITE3_INTEGER);
$insertQuery->bindValue(':nextDayIncreasePercent', $nextDayIncreasePercent, SQLITE3_FLOAT);
$insertQuery->bindValue(':averageIncrease', $averageIncrease, SQLITE3_FLOAT);
$insertQuery->bindValue(':nextDayDecrease', $nextDayDecrease, SQLITE3_INTEGER);
$insertQuery->bindValue(':nextDayDecreasePercent', $nextDayDecreasePercent, SQLITE3_FLOAT);
$insertQuery->bindValue(':averageDecrease', $averageDecrease, SQLITE3_FLOAT);
$insertQuery->bindValue(':buyValue', $buyValue, SQLITE3_FLOAT);
$insertQuery->bindValue(':sellValue', $sellValue, SQLITE3_FLOAT);
$insertQuery->execute();
}
masterLoop($db);
?>
<?php include_once APP_ROOT . '/templates/header.php'; ?>
<link rel="shortcut icon" href="../app/images/favicon.ico" type="image/x-icon">
<title>Analysis A</title>
</head>
<body>
<div>
<h4>Analysis A has been performed on the available stocks!</h4>
</div>
<?php include_once APP_ROOT . '/templates/footer.php'; ?>
我想要的是5行，每行都有公司股票代码(存储在tickerMaster.txt中(作为主键。但我排了6排。第6行与第5行相同，只是它在companyTicker列中不包含任何内容。我不想要第六排，因为它和第五排一样。tickerMaster.txt包含以下项目(最后还有一行额外的新行(：-
AAPL
TSLA
NFLX
MSFT
ADBE
如何解决此问题？
您应该将插入(和相关计算(移动到if ($companyTicker) {段内，这样可以停止任何插入空格的行。。。
if ($companyTicker) {
$nextDayIncrease = 0;
$nextDayDecrease = 0;
$nextDayNoChange = 0;
$total = 0;
// ...
$nextDayIncreasePercent = ($nextDayIncrease/$total)*100;
$nextDayDecreasePercent = ($nextDayDecrease/$total)*100;
$averageIncrease = $sumOfIncreases/$nextDayIncrease;
$averageDecrease = $sumOfDecreases/$nextDayDecrease;
insertIntoResultTable($db, $companyTicker, $nextDayIncrease, $nextDayIncreasePercent, $averageIncrease, $nextDayDecrease, $nextDayDecreasePercent, $averageDecrease);
}
它目前没有进行任何主要处理，但仍在处理包括插入的最后5行。