[RESOLVED] PHP MySQL code Problems and Formating

[fnp]

I have most of this script working. The only part that is not working is this section:

$SubCheck = "SELECT SUM(SubmissionID='$SubmissionID') AS num FROM tblListings";
								$result1 = $connection2->query($SubCheck);
								$row1=$result1->fetch_row();
								$num_rows = $row1['num'];
								if (!$result1) {
    									$message  = 'SQL query: ' . $connection2->error . "<br ><br >\n";
									$message .= 'Whole query: ' . $SubCheck;
									die($message);
								}
								echo $SubmissionID;
								echo $num_rows;
								if($num_rows=='3' || $num_rows=='6' || $num_rows=='9'){
									//$DateReturned=date('Ymd');
									//$RTSReason='NOSALE';
									echo 'IT WORKS';
								}
								else{
									$DateReturned='0000-00-00';
									$RTSReason='';
									echo 'NO WORKY';
								}

It will echo the SubmissionID but won't echo the num_rows, which is why the next if/else statements don't work.
Otherwise the rest of the script works great except that it is extremely slow. For a csv that has 60 entries it takes about 3 minutes. The whole script is:

						if(isset($_POST['endEB'])){
							$filename=$_POST['filenameEndEB'];
    							$handle = fopen("$filename", "r");
     							while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE){

							$connection2 = new mysqli($h['DOST'],$u['DOST'],$p['DOST'],$d['DOST']);
							$connection2->select_db($d['DOST']);
							$query = "SELECT DATE_FORMAT(DateStarted, '%m/%d/%Y'), IntendedDuration, SubmissionID FROM tblListings WHERE AuctionNumber='$data[0]'";
							$result = $connection2->query($query);
							$row=$result->fetch_row();
							$DateStarted = $row[0];
							$IntendedDuration = $row[1];
							$SubmissionID = $row[2];
							if (!$result) {
									$message  = 'SQL query: ' . $connection2->error . "<br ><br >\n";
								$message .= 'Whole query: ' . $query;
								die($message);
							}

							$SubCheck = "SELECT SUM(SubmissionID='$SubmissionID') AS num FROM tblListings";
							$result1 = $connection2->query($SubCheck);
							$row1=$result1->fetch_row();
							$num_rows = $row1['num'];
							if (!$result1) {
									$message  = 'SQL query: ' . $connection2->error . "<br ><br >\n";
								$message .= 'Whole query: ' . $SubCheck;
								die($message);
							}
							echo $SubmissionID;
							echo $num_rows;
							if($num_rows=='3' || $num_rows=='6' || $num_rows=='9'){
								//$DateReturned=date('Ymd');
								//$RTSReason='NOSALE';
								echo 'IT WORKS';
							}
							else{
								$DateReturned='0000-00-00';
								$RTSReason='';
								echo 'NO WORKY';
							}
							$SubReturn = "UPDATE tblSubmissions SET DateReturned='$DateReturned', RTSReason='$RTSReason' WHERE SubmissionID='$SubmissionID'";
							$result2 = $connection2->query($SubReturn);
							if (!$result2) {
									$message  = 'SQL query: ' . $connection2->error . "<br ><br >\n";
								$message .= 'Whole query: ' . $SubReturn;
								die($message);
							}
							$DateEnded = strtotime ( '+'.$IntendedDuration.' day' , strtotime ( $DateStarted ) ) ;
							$DateEnded = date ( 'Ymd' , $DateEnded );

							$ENDListings = "UPDATE tblListings set DateEnded='$DateEnded', HTMLAuctionText='' WHERE AuctionNumber='$data[0]'";
							$result3 = $connection2->query($ENDListings);
							if (!$result3) {
									$message  = 'SQL query: ' . $connection2->error . "<br ><br >\n";
								$message .= 'Whole query: ' . $ENDListings;
								die($message);
							}
							$connection2->close();
						}
						fclose($handle);
						echo "Auctions Have Been Ended";
					}
					else {
 							print "<form action='download.php' method='POST'>";
 							print "<ul><li>Type file name to end auctions:<br>";
						print "<li><input type='text' name='filenameEndEB' size='20' value='uploads/ebay.csv'><br>";
						print "<li><input type='submit' name='endEB' value='End eBay'></form></ul>";
					}
					

Any ideas with the speed of the script or the num_rows problems would be great.

[fnp]

Done, fixing the num_rows problem also fixed the speed issue. Here is final code:

if(isset($_POST['endEB'])){
							$filename=$_POST['filenameEndEB'];
    							$handle = fopen("$filename", "r");
     							while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE){

							$connection2 = new mysqli($h['DOST'],$u['DOST'],$p['DOST'],$d['DOST']);
							$connection2->select_db($d['DOST']);
							$query = "SELECT DATE_FORMAT(DateStarted, '%m/%d/%Y'), IntendedDuration, SubmissionID FROM tblListings WHERE AuctionNumber='$data[0]'";
							$result = $connection2->query($query);
							$row=$result->fetch_row();
							$DateStarted = $row[0];
							$IntendedDuration = $row[1];
							$SubmissionID = $row[2];
							if (!$result) {
									$message  = 'SQL query: ' . $connection2->error . "<br ><br >\n";
								$message .= 'Whole query: ' . $query;
								die($message);
							}

							$SubCheck = "SELECT * FROM tblListings WHERE SubmissionID='$SubmissionID'";
							$result1 = $connection2->prepare($SubCheck);
							$result1->execute();
							$result1->store_result();
							$num_rows = $result1->num_rows;
							if (!$result1) {
									$message  = 'SQL query: ' . $connection2->error . "<br ><br >\n";
								$message .= 'Whole query: ' . $SubCheck;
								die($message);
							}

							echo $SubmissionID;

							echo $num_rows;
							if($num_rows=='3' || $num_rows=='6' || $num_rows=='9'){
								$DateReturned=date('Ymd');
								$RTSReason='NOSALE';
								//echo 'IT WORKS';
							}

							else{
								$DateReturned='0000-00-00';
								$RTSReason='';
								//echo 'NO WORKY';
							}

							$result1->close;
							$SubReturn = "UPDATE tblSubmissions SET DateReturned='$DateReturned', RTSReason='$RTSReason' WHERE SubmissionID='$SubmissionID'";
							$result2 = $connection2->query($SubReturn);
							if (!$result2) {
									$message  = 'SQL query: ' . $connection2->error . "<br ><br >\n";
								$message .= 'Whole query: ' . $SubReturn;
								die($message);
							}

							$DateEnded = strtotime ( '+'.$IntendedDuration.' day' , strtotime ( $DateStarted ) ) ;
							$DateEnded = date ( 'Ymd' , $DateEnded );
							$ENDListings = "UPDATE tblListings set DateEnded='$DateEnded', HTMLAuctionText='' WHERE AuctionNumber='$data[0]'";
							$result3 = $connection2->query($ENDListings);
							if (!$result3) {
									$message  = 'SQL query: ' . $connection2->error . "<br ><br >\n";
								$message .= 'Whole query: ' . $ENDListings;
								die($message);
							}

							$connection2->close();
						}

						fclose($handle);
						echo "Auctions Have Been Ended";
					}

					else {
 							print "<form action='download.php' method='POST'>";
 							print "<ul><li>Type file name to end auctions:<br>";
						print "<li><input type='text' name='filenameEndEB' size='20' value='uploads/ebay.csv'><br>";
						print "<li><input type='submit' name='endEB' value='End eBay'></form></ul>";
					}

Thanks, hope this helps somebody else, too.