I have a reviewfile.php whereby it reads in a file and process it by the following criteria by inserting the data into database:
- Duplicated results
- Results without comments
- Results of data with source or destination that is "Any".
I have SQL statement for each condition.
After printing results of the SQL statements, i would like delete all data of the database table so that if i upload a new file, insertion of data is fresh.
P.S: I do not want to drop table.
May i seek some help please:)
Below is my codes
if(isset($_POST['reviewFile'])){
if (isset($_POST['fileSelected'])){
$SelFile = $_POST['fileSelected'];
$query=$con->prepare("SELECT * from file where file_name = '$SelFile'");
$query->bind_result($file_id, $uid, $file_name, $file_size);
$query->execute();
$query->fetch();
$query->close();
$path = 'C:/xampp/htdocs/fwrule/fw/fileuploads/';
$file_to_get = $path.$SelFile;
$_SESSION['tmpfname'] = $file_to_get;
$_SESSION['excelReader'] = PHPExcel_IOFactory::createReaderForFile($_SESSION['tmpfname']);
$_SESSION['excelObj'] = $_SESSION['excelReader']->load($_SESSION['tmpfname']);
$_SESSION['worksheet'] = $_SESSION['excelObj']->getSheet(0);
$_SESSION['lastRow'] = $_SESSION['worksheet']->getHighestRow();
$_SESSION['highestColumn'] = $_SESSION['worksheet']->getHighestColumn();
//loop through each row of the worksheet in turn
for ($_SESSION['row'] = 3; $_SESSION['row'] <= $_SESSION['lastRow']; ++$_SESSION['row']) {
//read a row of data into an array
$rowData = $_SESSION['worksheet']->rangeToArray('A'.$_SESSION['row']. ':'.$_SESSION['highestColumn'] . $_SESSION['lastRow'], NULL, TRUE, FALSE);
//insert rows into database
$InsertData = $con->prepare("INSERT INTO file_data(id, file_id, no, name, source, destination, hit_counts, service, action, track, install_on, time, comment)
VALUES('', '$file_id', '".$rowData[0][0]."', '".$rowData[0][1]."', '".$rowData[0][2]."', '".$rowData[0][3]."', '".$rowData[0][4]."', '".$rowData[0][5]."', '".$rowData[0][6]."', '".$rowData[0][7]."', '".$rowData[0][8]."', '".$rowData[0][9]."', '".$rowData[0][10]."')");
$InsertData->execute();
$InsertData->store_result();
/*if (!$InsertData = $con->prepare("INSERT INTO file_data(id, file_id, no, name, source, destination, hit_counts, service, action, track, install_on, time, comment)
VALUES('', '$file_id', '".$rowData[0][0]."', '".$rowData[0][1]."', '".$rowData[0][2]."', '".$rowData[0][3]."', '".$rowData[0][4]."', '".$rowData[0][5]."', '".$rowData[0][6]."', '".$rowData[0][7]."', '".$rowData[0][8]."', '".$rowData[0][9]."', '".$rowData[0][10]."')"))
{
?>
<html>
<script>
window.alert("Error message:: %s\n", $con->error");
</script>
</html>
<?php
}*/
}
if ($DuplicatedRows = $con->prepare("SELECT id
from file_data a
join ( SELECT source, destination, hit_counts, service
from file_data
group by source, destination, hit_counts, service
having count(*) > 1 ) b
on a.source = b.source
and a.destination = b.destination
and a.hit_counts = b.hit_counts
and a.service = b.service")){
$DuplicatedRows->bind_result($id);
$DuplicatedRows->execute();
$DuplicatedRows->store_result();
while($DuplicatedRows->fetch()){
$rowNumforDuplicates = ($id+2);
$info1 = "Duplicated Rules";
echo "<tr>";
echo "<td>".$rowNumforDuplicates."</td>";
echo "<td>".$info1."</td>";
//echo "<td>".$source."".$destination."".$hit_counts."".$service."</td>";
echo "</tr>";
}
$DuplicatedRows->free_result();
$DuplicatedRows->close();
}
else{
?>
<html>
<script>
window.alert("Error message:: %s\n", $con->error");
</script>
</html>
<?php
}
if ($checkforAny = $con->prepare("SELECT id FROM file_data WHERE source collate latin1_swedish_ci = 'any' or destination collate latin1_swedish_ci ='any' "))
{
$checkforAny->bind_result($id);
$checkforAny->execute();
$checkforAny->store_result();
while($checkforAny->fetch()){
//row number in excel as the file is reviewed from line 3 onwards, hence 2 is added to the ID value
$rowNumforAny = ($id+2);
echo "<br>";
echo "The one with Any: $rowNumforAny";
}
$checkforAny->free_result();
$checkforAny->close();
}
else{
?>
<html>
<script>
window.alert("Error message:: %s\n", $con->error");
</script>
</html>
<?php
}
if ($checkforComments = $con->prepare("SELECT id FROM file_data WHERE comment = '' "))
{
$checkforComments->bind_result($id);
$checkforComments->execute();
$checkforComments->store_result();
while($checkforComments->fetch()){
$rowNumforComments = ($id+2);
echo "<br>";
echo "The one with with no comments:$rowNumforComments";
}
echo "</table>";
$checkforComments->free_result();
$checkforComments->close();
}
else {
?>
<html>
<script>
window.alert("Error message:: %s\n", $con->error");
</script>
</html>
<?php
}
if ($checkforHitcounts = $con->prepare("SELECT id FROM file_data ORDER BY hit_counts ASC|DESC")){
$checkforHitcounts->bind_result($id, $hit_counts);
$checkforHitcounts->execute();
$checkforHitcounts->store_result();
while($checkforHitcounts->fetch()){
echo "<br>";
echo "hitcounts:$id";
}
echo "</table>";
$checkforHitcounts->free_result();
$checkforHitcounts->close();
}
else{
?>
<html>
<script>
window.alert("Error message:: %s\n", $con->error");
</script>
</html>
<?php
}
if ($DuplicatedRows == true && $checkforAny == true && $checkforComments == true)
{
$delAllData = $con->prepare("DELETE * from file_data");
$delAllData->bind_result();
if ($delAllData->execute())
{
$delAllData->fetch();
echo "hi";
}
$delAllData->close();
}
}
}
The codes are working fine except for the part where I want to delete data from database table.
This is the part that is not working:
if ($DuplicatedRows == true && $checkforAny == true && $checkforComments == true)
{
$delAllData = $con->prepare("DELETE * from file_data");
$delAllData->bind_result();
if ($delAllData->execute())
{
$delAllData->fetch();
echo "hi";
}
$delAllData->close();
}
DELETE *is invalid and I'll find the duplicate for this; give me a minute. Edit: found and closed.TRUNCATE file_datainstead of DELETE FROM. It's faster, and clears the indexes.