I see no reason for the disappearing rows, but I'd like to point a way to make the code easier to read, at least in my opinion, while also making it more efficient.
Instead of looping over the entire result set once and then looping over the stored data twice to store it on file and in database, why not do it all at the same time, using only the while loop which now contains no more than 6 statements.
if(isset($_POST['submitupload']))
{
$EmployeeID=$_SESSION['eid'];
$connection = connection script;
$connection->select_db($d['DOST']);
$fp = fopen('somefile', 'wt');
$filename = "AMZListings.txt";
$sql = "SELECT SubmissionID, ASIN, MinBid, SubxText, ItemCondition, SubxTitle ";
$sql .="FROM tblSubmissions ";
$sql .="WHERE Ready='YES'";
$result = $connection->query($sql) or die (mysql_error());
# Write header before starting the loop since it's only needed once.
$header=array
(
'sku','product-id','product-id-type','price','item-condition',
'quantity','add-delete','will-ship-internationally','expedited-shipping',
'item-note','fulfillment-center-id'
);
fputcsv($fp, $header, "\t");
# The query, apart from the values part, is the same every time of the loop.
# Thus, initiate it before the loop, not once per iteration.
$Aucsql = "INSERT INTO tblListings";
$Aucsql .= "(SubmissionID, AuctionSiteID, AuctionTitle, MinBidPrice, DateStarted, DateEnded, AuctionNumber, LastModifiedBy, LastModifiedTS, Quantity) ";
while($row=$result->fetch_row())
{
$row[3] = strip_tags($row[3]);
fputcsv
(
$fp,
array ($row[0], $row[1], '1', $row[2], $row[4], '1', 'a', '1', '', $row[3], ''),
"\t"
);
# Should you really quote SubmissionID? Isn't that an integer?
# Should you really quote MinBid? Isn't that int, float or double?
# Why do you have SubmissionID twice?
# Should you really quote EmployeeID?
# Should you really quote '1'? If you have defined Quantity as VARCHAR,
# then yes. But if that's the case, then you should change it to INT, and
# then you should drop the quotes.
$values = " VALUES('$row[0]', 'AMZ', '$row[5]', '$row[2]', NOW(), '0000-00-00', '$row[0]', '$EmployeeID', NOW(), '1')";
$connection->query($Aucsql . $values);
# Check if query was ok. If not, log error!
# Same here. (most likely) drop the quotes around the SubmissionID value.
$SubUpdate = "UPDATE tblSubmissions set Ready=' ' WHERE SubmissionID='$row[0]'";
$connection->query($SubUpdate);
# Check if query was ok. If not, log error!
}
if ($result->num_rows)
{
echo "Export done";
}
else
{
echo 'Nothing to export';
}
$result->free();
}
I would also recommend that you never ever use $row[0] ... $row[151]. Just what does that $row[23] contain again? Fetch data associatively and then access it as $row['SubmissionID'] (outside of strings) and as "$row[SubmissionID]" or "{$row['SubmissionID']}" inside string literals. Too long to type? Use SQL aliases.
SELECT SubmissionID as sid
=>
$row['sid']
Which is still a huge improvement in readability.
Just be aware that if you retrieve two columns with the same name that come from different tables, you will have to use alias to give them different names or one will overwrite the other in your associative array.
