Inserting ALOT of records into a DB at one time

Hello,

I have a CSV file which I need to insert into a DB for future needs, the csv at present contains 10k+ records.

If I comment out my insert SQL code, my row counter + csv file prints into my table with all 10k records.

The second I uncomment my code, the page prints (and inserts into SQL) about 350 rows of data and does not print out my footer or finishes rendering the table. It is like the connection dies or something says you are being greedy.

Is there a limit on how many records you can insert into mysql at one time and if so how can I fix my code?

I do not get any information in terms of mysql_error(), it just stops print “succcessful” records @ somewhere between 340 – 365

Any help would be greatly appreciated.

[code=php]
<?php
include ‘dbConnection.inc.php’;
dbConnection(“localhost”,”cms”,”root”);
$fileTempName = $_FILES[‘locationFile’][‘tmp_name’];
$fileOrigName = $_FILES[‘locationFile’][‘name’];
$fileSize = $_FILES[‘locationFile’][‘size’];
$fileType = $_FILES[‘locationFile’][‘type’];

$targetFileName = $_SERVER[‘DOCUMENT_ROOT’] . “ha-cms/” . $fileOrigName;

if (!copy($fileTempName, $targetFileName))
{
echo “failed to copy file.”;
}

$data = array(
‘col0’ => array(),
‘col1’ => array(),
‘col2’ => array()
);

$f = fopen($targetFileName, “r”);
$rowCtr = 1;
$emptyCtr = $closedCtr = $openCtr = 0;
while (($line = fgetcsv($f)) !== false)
{
$data[‘country’][] = $line[9];
$data[‘status’][] = $line[4];
$data[‘officeId’][] = $line[2];
}
echo “<table>”;
foreach ($data[‘country’] as $ix => $printCols)
{
$printCols = ucfirst($printCols);
switch ($printCols)
{
default:
echo “<tr>”;
echo “<td>$rowCtr</td>”;
echo “<td>” . $data[‘officeId’][$ix] . “</td>”;
echo “<td>$printCols</td>”;
echo “<td>” . $data[‘status’][$ix] . “</td>”;
echo “</tr>”;
$rowCtr++;
break;
case ‘Country’:
echo “<thead>”;
echo “<tr>”;
echo “<th>Row Number</th>”;
echo “<th>Office ID</th>”;
echo “<th>Country</th>”;
echo “<th>Status</th>”;
echo “</tr>”;
echo “</thead>”;
break;
case ”:
$emptyCtr++;
echo “<tr>”;
echo “<td>$rowCtr</td>”;
echo “<td>” . $data[‘officeId’][$ix] . “</td>”;
echo “<td>” . “BLANK” . “</td>”;
echo “<td>” . $data[‘status’][$ix] . “</td>”;
echo “</tr>”;
$rowCtr++;
break;
case $data[‘status’][$ix] == ‘open’:
$openCtr++;
echo “<tr>”;
echo “<td>$rowCtr</td>”;
echo “<td>” . $data[‘officeId’][$ix] . “</td>”;
echo “<td>$printCols</td>”;
echo “<td>” . $data[‘status’][$ix] . “</td>”;
echo “</tr>”;
$rowCtr++;
break;
case $data[‘status’][$ix] == ‘closed’:
$closedCtr++;
echo “<tr>”;
echo “<td>$rowCtr</td>”;
echo “<td>” . $data[‘officeId’][$ix] . “</td>”;
echo “<td>$printCols</td>”;
echo “<td>” . $data[‘status’][$ix] . “</td>”;
echo “</tr>”;
$rowCtr++;
break;
}
$dC = $data[‘country’][$ix];
$dC = trim($dC);
$dC = htmlspecialchars($dC);
$dO = $data[‘officeId’][$ix];
$insLocs = “insert into countrytable set officeId=$dO, countryname=’$dC'”;
if ($data[‘country’][$ix] != ‘Country’) {
if (@mysql_query($insLocs)) {
echo “<p>Successful: $rowCtr</p>”;
}
else
{
echo “<p>$insLocs ” . mysql_error() . “</p>”;
}
}
}
echo “</table>”;
$updateDate = date(‘ymd’);
$updateStatus = array(‘locationsTotal’ => $rowCtr,
‘blankCtry’ => $emptyCtr,
‘locationsClosed’ => $closedCtr,
‘locationsOpen’ => $openCtr,
‘locationsDate’ => $updateDate
);
foreach ($updateStatus as $printUpdateStatusK => $printUpdateStatusV)
{
echo “<p>”. $printUpdateStatusK . “: ” . $printUpdateStatusV . “</p>”;
}
$insLocUpdateInfo = “insert into cms set openLoc=$openCtr, closeLoc=$closedCtr, updateDate=$updateDate, totalLoc=$rowCtr”;
if (@mysql_query($insLocUpdateInfo))
{
echo “<p>Database updated.</p>”;
}
fclose($f);
?>
[/code]

Also in #PHP _↴