Confirmation before database update.

Hello,

I have made a program that can extract excel file then save it to mysql. However, is there a way to get confirmation first before adding it to the database?

Here is my simple code that I made. I just do not know how to please the confirmation button.

[php]<?php

require_once ‘Classes/PHPExcel/IOFactory.php’;
include ‘conn_db.php’;

$objPHPExcel = PHPExcel_IOFactory::load(‘includes/upload/’. $_FILES[“file”][“name”]);
$exists = $objPHPExcel->sheetNameExists(‘billing’);

if ($exists) {

$worksheet = $objPHPExcel->setActiveSheetIndexByName(‘billing’);

$worksheetTitle     = $worksheet->getTitle();
$highestRow         = $worksheet->getHighestRow(); // e.g. 10
$highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
echo "<br>The worksheet ".$worksheetTitle." has ";
echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
echo ' and ' . $highestRow . ' row.';
echo '<br>Data: <table border="1"><br><tr>';

//Update batch table and increment/update the record.
$result = mysqli_query($con,"SELECT batch_no FROM batches");
$row = mysqli_fetch_array($result);
$batch_no = $row['batch_no'];
$batch_no += 1;
mysqli_query($con,"UPDATE batches SET batch_no='$batch_no'");
	
for ($row = 2; $row <= $highestRow; ++ $row) {
	echo '<tr>';
    for ($col = 0; $col < $highestColumnIndex; ++ $col) {
        $cell = $worksheet->getCellByColumnAndRow($col, $row);
        $val = $cell->getFormattedValue();//getValue();
		$it[] = $val;
        $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
	    echo '<td>' . $val . '</td>'; //echo '<td>' . $val . '<br>(Typ ' .  $dataType . ')</td>';
	}
	
	//Check if the next cell is empty
	if ($val == '') {break;}

	//add data into the database
	
	$sql = "INSERT INTO `ExcelData` 
	(`batch_no`,`DELIVERY_DATE`,`CUSTOMER`,`PART_NUMBER`,`PO_NO`,`QTY`,`UP`,`TOTAL`,`SBU`) 
	VALUES 
	('$batch_no',STR_TO_DATE('$it[0]','%d-%b-%y'),'$it[1]','$it[2]','$it[3]','$it[4]','$it[5]','$it[6]','P1')";
	// adding date link http://www.techonthenet.com/mysql/functions/str_to_date.php
	$result = mysqli_query($con, $sql) or die('Error querying database.');
	//echo "<td>Added</td>";
	
	//reset the array
	foreach ($it as $i => $value) {unset($it[$i]);}
	$it = array_values($it);
	echo '</tr>';
	}

echo ‘’;
} else {
echo “

ERROR: billing sheet cannot be located on your Excel file! Please check

”;
}
?>[/php]

Do you have a form page the you use before you access this page?
You could use javascript to confirm

<form ... return:false>

<input type="submit" onclick="return confirm('are you sure')"/>
</form>

Hello grwebguy
Thank you for your reply. I do have a form but this form is only purpose is to upload a file.

The confirmation should be after showing the data to the user.
I am thinking of an array save the data first then if confirmation click ok button then it will process the “INSERT INTO” but I cannot seems to express it in code. I cannot figure out how to put all the data into an array first before applying INSERT INTO command.

Please check all the file link to my PHP.
p1_upload_form.php
[php]


<?php include('includes/header.php'); ?>
<?php include('includes/nav.php'); ?>
		<div id="content">
			<h3>Upload Invoice Record</h3>
			<p>
				Please select the file to upload. This will be the information of the invoice that will be created.
			</p>
			<hr>
			<p>
				<form action="p1_filesave_form.php" method="post"
				enctype="multipart/form-data">
				<label for="file">Filename:</label>
				<input type="file" name="file" id="file"><br>
				<input type="submit" name="submit" value="Submit">
				</form>
			</p>
			<hr>
			
		</div> <!-- end #content -->

		<?php include('includes/p1_sidebar.php'); ?>
		<?php include('includes/footer.php'); ?>

	</div> <!-- End #wrapper -->
</body>[/php]

p1_filesave_form.php
[php]


<?php include('includes/header.php'); ?>
<?php include('includes/nav.php'); ?>
		<div id="content">
			<h3>Upload Invoice Record</h3>
			<p>
				Please verify information if these are correct. Any error please advise MIS immediately.
			</p>
			<hr>
			<p>

			<?php include('includes/file_save.php'); ?>
			
			</p>
			<hr>
			
		</div> <!-- end #content -->

		<?php include('includes/p1_sidebar.php'); ?>
		<?php include('includes/footer.php'); ?>

	</div> <!-- End #wrapper -->
</body>[/php]

file_save.php
[php]<?php
$allowedExts = array(“xls”, “xlsx”);
$temp = explode(".", $_FILES[“file”][“name”]);
$extension = end($temp);

if ((($_FILES[“file”][“type”] == “application/vnd.ms-excel”)
|| ($_FILES[“file”][“type”] == “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”))
&& ($_FILES[“file”][“size”] < 2000000)
&& in_array($extension, $allowedExts)) {
if ($_FILES[“file”][“error”] > 0) {
echo "Return Code: " . $_FILES[“file”][“error”] . “
”;
} else {
echo "Upload: " . $_FILES[“file”][“name”] . “
”;
echo "Type: " . $_FILES[“file”][“type”] . “
”;
echo “Size: " . ($_FILES[“file”][“size”] / 1024) . " kB
”;
echo "Temp file: " . $_FILES[“file”][“tmp_name”] . “
”;
if (file_exists(“includes/upload/” . $_FILES[“file”][“name”])) {
echo $_FILES[“file”][“name”] . " already exists. ";
unlink(“includes/upload/” . $_FILES[“file”][“name”]);
} else {
move_uploaded_file($_FILES[“file”][“tmp_name”],
“includes/upload/” . $_FILES[“file”][“name”]);
echo "Stored in: " . “includes/upload/” . $_FILES[“file”][“name”];
require_once ‘file_convert.php’;

	if (!unlink("includes/upload/" . $_FILES["file"]["name"]))
	  {
	  echo ("Error deleting the file");
	  }
	else
	  {
	  echo ("File Deleted");
	  }
	  
}

}
} else {
echo “Invalid file”;
}
?>[/php]

file_convert.php
[php]<?php

require_once ‘Classes/PHPExcel/IOFactory.php’;
include ‘conn_db.php’;

$objPHPExcel = PHPExcel_IOFactory::load(‘includes/upload/’. $_FILES[“file”][“name”]);
$exists = $objPHPExcel->sheetNameExists(‘billing’);

if ($exists) {

$worksheet = $objPHPExcel->setActiveSheetIndexByName(‘billing’);

$worksheetTitle     = $worksheet->getTitle();
$highestRow         = $worksheet->getHighestRow(); // e.g. 10
$highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
echo "<br>The worksheet ".$worksheetTitle." has ";
echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
echo ' and ' . $highestRow . ' row.';
echo '<br>Data: <table border="1"><br><tr>';

//Update batch table and increment/update the record.
$result = mysqli_query($con,"SELECT batch_no FROM batches");
$row = mysqli_fetch_array($result);
$batch_no = $row['batch_no'];
$batch_no += 1;
mysqli_query($con,"UPDATE batches SET batch_no='$batch_no'");
	
for ($row = 2; $row <= $highestRow; ++ $row) {
	echo '<tr>';
    for ($col = 0; $col < $highestColumnIndex; ++ $col) {
        $cell = $worksheet->getCellByColumnAndRow($col, $row);
        $val = $cell->getFormattedValue();//getValue();
		$it[] = $val;
        $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
	    echo '<td>' . $val . '</td>'; //echo '<td>' . $val . '<br>(Typ ' .  $dataType . ')</td>';
	}
	
	//Check if the next cell is empty
	if ($val == '') {break;}

	//add data into the database
	
	$sql = "INSERT INTO `ExcelData` 
	(`batch_no`,`DELIVERY_DATE`,`CUSTOMER`,`PART_NUMBER`,`PO_NO`,`QTY`,`UP`,`TOTAL`,`SBU`) 
	VALUES 
	('$batch_no',STR_TO_DATE('$it[0]','%d-%b-%y'),'$it[1]','$it[2]','$it[3]','$it[4]','$it[5]','$it[6]','P1')";
	// adding date link http://www.techonthenet.com/mysql/functions/str_to_date.php
	$result = mysqli_query($con, $sql) or die('Error querying database.');
	//echo "<td>Added</td>";
	
	//reset the array
	foreach ($it as $i => $value) {unset($it[$i]);}
	$it = array_values($it);
	echo '</tr>';
	}

echo ‘’;
} else {
echo “

ERROR: billing sheet cannot be located on your Excel file! Please check

”;
}
?>[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service