Delete selected and insert into database

[I-AM-OBODO]

Hi all.

 

I'm having an issue with my code. I dunno why it's not working as intended.

I want to delete selected rows and insert them into another table just in case i want to recall them. So far the delete is working but i'm having two issues with it.

 

1. when i select 5 rows and press delete, it echos result for the 5 rows instead of just one echo result.

2. I cannot insert it into another table.

 

Thanks

 

here's my code

 

 

 
<?php

$stmt = $pdo->prepare(
"SELECT * FROM $tbl_name
ORDER BY trans_date DESC"
);
$stmt->execute();

 <form action="" method="post">  
<table class='table-responsive table-condensed table-striped'>

<tr>
<td align="center" bgcolor="#444444"><font color='#fff'><strong>#</strong></font></td>
<td align='center' bgcolor="#444444"><font color='#fff'>Client Username</font></td>
<td align='center' bgcolor="#444444"><font color='#fff'>Loan Number</font></td>
<td align='center' bgcolor="#444444"><font color='#fff'>Amount</font></td>
<td align="center" bgcolor="#444444"><font color='#fff'><strong>Repayment Amount</strong></font></td>
<td align="center" bgcolor="#444444"><font color='#fff'><strong>Loan Date</strong></font></td>
<td align="center" bgcolor="#444444"><font color='#fff'><strong>Due Date</strong></font></td>
<td align="center" bgcolor="#444444"><font color='#fff'><strong>BTN</strong></font></td>
<td align="center" bgcolor="#444444"><font color='#fff'><strong>FIN</strong></font></td>
<td align="center" bgcolor="#444444"><font color='#fff'><strong>Bank Acct Number</strong></font></td>
<td align="center" bgcolor="#444444"><font color='#fff'><strong>Date Closed</strong></font></td>
<td align="center" bgcolor="#444444"><font color='#fff'><strong>Closed From</strong></font></td>
</tr>

<?php
// keeps getting the next row until there are no more to get
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
?>

<tr>
<td align="center"><input name="check_list[]" type="checkbox" value="<?php echo $row['id']; ?>" ></td>
<td><?php     echo $row['email']; ?></td>
<td><?php     echo $row['loan_number']; ?></td>
<td><?php     echo number_format($row['amount_borrow'],2); ?></td>
<td><?php     echo number_format($row['payback_amount'],2); ?></td>
<td><?php    echo $row['trans_date']; ?></td>
<td><?php    echo $row['payback_date']; ?></td>
<td><?php    echo $row['branch_transit_number']; ?></td>
<td><?php    echo $row['financial_institution_number']; ?></td>
<td><?php    echo $row['bank_account_number']; ?></td>
<td><?php    echo $row['date_closed']; ?></td>
<td><?php    echo $row['closed_from']; ?></td>
</tr>
<?php
}
?>
</table>
<input name="delete" type="submit" class="btn btn-default btn-delete" id="delete" value="DELETE SELECTED">
</form>


 <?php


if(isset($_POST['delete'])){
    
$table=$pdo->query("ALTER TABLE cash_user_transaction AUTO_INCREMENT = 1");
$table->execute();

    if(!empty($_POST['check_list'])){
        foreach($_POST['check_list'] as $selected){

$stmt = $pdo->prepare("SELECT * FROM $tbl_name WHERE id = '$selected'");
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {    
$email = $row['email'];
$loan_number = $row['loan_humber'];
$amount_borrow = $row['amount_borrow'];
$terms_agreement=$row['terms_agreement'];
$terms_agreement_days=$row['terms_agreement_days'];
$cost_borrow=$row['cost_borrow'];
$trans_date=$row['trans_date'];
$payback_date=$row['payback_date'];
$payback_amount=$row['payback_amount'];
$branch_transit_number=$row['branch_transit_number'];
$financial_institution_number=$row['financial_institution_number'];
$bank_account_number=$row['bank_account_number'];
$status=$row['status'];
$initial=$row['initial'];
$pay_frequency=$row['pay_frequency'];
$refinance_request=$row['refinance_request'];
$date_closed=$row['date_closed'];
$disbursed_status=$row['disbursed_status'];
$date_disbursed=$row['date_disbursed'];
$closed_from=$row['closed_from'];
}

    if($stmt->rowCount()){
$stmt = $pdo->prepare("INSERT INTO cash_user_deleted_transaction
        (email, loan_number, amount_borrow, terms_agreement, terms_agreement_days, cost_borrow, trans_date, payback_date, payback_amount, branch_transit_number, financial_institution_number, bank_account_number, status, initial, pay_frequency, refinance_request, date_closed, disbursed_status, date_disbursed, closed_from, date_deleted)
        
        VALUES(:email, :loan_number, :amount_borrow, :terms_agreement, :terms_agreement_days, :cost_borrow, :trans_date, :payback_date, :payback_amount, :branch_transit_number, :financial_institution_number, :bank_account_number, :status, :initial, :pay_frequency, :refinance_request, :date_closed, :disbursed_status, :date_disbursed, :closed_from, NOW())");
        
$stmt->execute(array(
':email'=>$email,
':loan_number'=>$loan_humber,
':amount_borrow'=>$amount_borrow,
':terms_agreement'=>$terms_agreement,
':terms_agreement_days'=>$terms_agreement_days,
':cost_borrow'=>$cost_borrow,
':trans_date'=>$trans_date,
':payback_date'=>$payback_date,
':payback_amount'=>$payback_amount,
':branch_transit_number'=>$branch_transit_number,
':financial_institution_number'=>$financial_institution_number,
':bank_account_number'=>$bank_account_number,
':status'=>$status,
':initial'=>$initial,
':pay_frequency'=>$pay_frequency,
':refinance_request'=>$refinance_request,
':date_closed'=>$date_closed,
':disbursed_status'=>$disbursed_status,
':date_disbursed'=>$date_disbursed,
':closed_from'=>$closed_from
));    

$stmt = $pdo->prepare("DELETE FROM $tbl_name WHERE id=:id");
$stmt->bindValue(':id', $selected, PDO::PARAM_STR);
$stmt->execute();
    echo "<div class='bg-success alert alert-success text-center'>RECORD DELETED</div>";
    
}else{
    echo "<div class='bg-warning alert alert-warning text-center'>RECORD NOT DELETED</div>";
    echo $pay_frequency;
    echo "<br>";
    echo $status;
    echo "<br>";
    echo $amount_borrow;

}
            
        }
    }
    
}

?>
 

[Ch0cu3r]

 

1. when i select 5 rows and press delete, it echos result for the 5 rows instead of just one echo result.

Because you are echo'ing the RECORD DELETED message inside the foreach loop, which is looping over each id from the submitted checkboxes. If you only want that messsage displayed once then you have echo it outside the foreach loop once it has deleted all records. Ofcourse you will need to check if all the selected records where deleted, for that you will have to check the affected rows

 

 

2. I cannot insert it into another table.

Rather than looping over each id, then a manually doing SELECT query, followed by an INSERT query you can do it all in one query using a INSERT SELECT

Example

$checkbox_ids = implode(',', $_POST['check_list']);
$sql = "INSERT INTO cash_user_deleted_transaction SELECT * FROM $tbl_name WHERE id IN($checkbox_ids)";

Or rather than copying the data to another table, why not add a new column which marks that row as deleted, then all you need to do is an update query? Eg

ALTER TABLE $table_name ADD `deleted` ENUM('0','1') NOT NULL DEFAULT '0';
$checkbox_ids = implode(',', $_POST['check_list']);
UPDATE $table_name SET deleted = 1 WHERE id IN($checkbox_ids);

To query records that are not deleted apply  delete = 0  to your where clause. To get deleted records you would use deleted = 1 in the where clause.

[I-AM-OBODO]

Because you are echo'ing the RECORD DELETED message inside the foreach loop, which is looping over each id from the submitted checkboxes. If you only want that messsage displayed once then you have echo it outside the foreach loop once it has deleted all records. Ofcourse you will need to check if all the selected records where deleted, for that you will have to check the affected rows

 

Rather than looping over each id, then a manually doing SELECT query, followed by an INSERT query you can do it all in one query using a INSERT SELECT

Example

$checkbox_ids = implode(',', $_POST['check_list']);
$sql = "INSERT INTO cash_user_deleted_transaction SELECT * FROM $tbl_name WHERE id IN($checkbox_ids)";

Or rather than copying the data to another table, why not add a new column which marks that row as deleted, then all you need to do is an update query? Eg

ALTER TABLE $table_name ADD `deleted` ENUM('0','1') NOT NULL DEFAULT '0';
$checkbox_ids = implode(',', $_POST['check_list']);
UPDATE $table_name SET deleted = 1 WHERE id IN($checkbox_ids);

To query records that are not deleted apply  delete = 0  to your where clause. To get deleted records you would use deleted = 1 in the where clause.

 

$checkbox_ids = implode(',', $_POST['check_list']);

$sql = "INSERT INTO cash_user_deleted_transaction SELECT * FROM $tbl_name WHERE id IN($checkbox_ids)";

 

Does this mean the insert statement here dont need the "values" attributes?

 

will give it a go,

 

thanks

[Barand]

 

Does this mean the insert statement here dont need the "values" attributes?

 

Yes, the values come from the select query.

 

Yours will work only if the structures of the tables are identical. If not, you need to specify the the destination and source columns, for example

INSERT INTO table1 (col1, col3, col2) SELECT x, y, z FROM table2 WHERE ...

[I-AM-OBODO]

I tried below but still wouldnt work

 

 

 
 
$checkbox_ids = implode(',', $_POST['check_list']);
$sql = "INSERT INTO cash_user_deleted_transaction SELECT * FROM $tbl_name WHERE id IN($checkbox_ids)";
$stmt=$pdo->prepare($sql);
$stmt->execute();
 
if($stmt-.rowCount()){
 
echo "inserted";
}else{
echo "not inserted";
}

[hansford]

As Barand stated, the structures of the table need to be identical in order to use the insert and select combination.

If there is a chance that you will bring these deleted rows back to life in the future then I believe Ch0cu3r suggestion of just adding another column which marks the row as "deleted" sounds more reasonable.

[Barand]

First thing you should do is turn on error reporting so you don't try running code with syntax errors

[I-AM-OBODO]

Thanks all. My Mistake. And i have implemented a different case similar to what Ch0cu3r suggested. What i did was create a column with default value as "NONE" so when the delete command is issued, instead of deleting it updates the "NONE" to "DELETED" so that should i want to issue a query of deleted records, i just use a where delete_status = 'DELETED' and to view all the records: where delete_status = "NONE".

It works fine but i dunno if it has any side effect.

 

What's the advantage one has over the other (moving to another table and updating/creating another column)?

 

Thanks all again!!!!!

[Jacques1]

Simply marking rows as deleted is obviously much easier to implement and allows you to get rid of the extra table. However, there are two problems you need to be aware of:

• Whenever you query the table, you have to remember to exclude deleted rows. This can be fixed with a view which automatically filters the rows.
• Hidden rows can lead to a lot of confusion when they cause constraint violations. For example, let's say one of the columns is UNIQUE. When a user deletes the rows, they obviously think they can just reuse the value. But instead the database system complains about a conflict with some phantom row which shouldn't even exist (from the user's perspective).

Your implementation also sounds weird. Why are you using strings? This is very confusing and error-prone. You should be using a BOOLEAN (this is actually an integer type, but MySQL knows how to interpret the values correctly). Use FALSE as the default value and TRUE to delete rows.

Edited September 22, 2015 by Jacques1

[I-AM-OBODO]

Simply marking rows as deleted is obviously much easier to implement and allows you to get rid of the extra table. However, there are two problems you need to be aware of:

• Whenever you query the table, you have to remember to exclude deleted rows. This can be fixed with a view which automatically filters the rows.
• Hidden rows can lead to a lot of confusion when they cause constraint violations. For example, let's say one of the columns is UNIQUE. When a user deletes the rows, they obviously think they can just reuse the value. But instead the database system complains about a conflict with some phantom row which shouldn't even exist (from the user's perspective).

Your implementation also sounds weird. Why are you using strings? This is very confusing and error-prone. You should be using a BOOLEAN (this is actually an integer type, but MySQL knows how to interpret the values correctly). Use FALSE as the default value and TRUE to delete rows.

 

Thanks. But you still did not mention which is best?

[Psycho]

Thanks. But you still did not mention which is best?

 

Because he doesn't know the full scope and purpose of your database. I agree with Jacques1 in that you should not delete rows from one table just to populate the same data into another table. There are some legitimate reasons why that might be appropriate, but I'm confident that is not the case here. If you need the data for historical purposes, just have a column in the table for a "deleted" flag.

 

Then, YOU need to analyze your table. Are there columns that need to be unique (other than an internal primary key)? If so, those constraints could be moved to the business logic. But, assuming there are no unique fields other than the primary key it is much simpler. Then you would just need to make one last decision.

 

It is assumed that most/all current queries should not return/operate on these "deleted" rows. depending on how many queries you have already built and/or the complexity you expect there will be you could 1) Modify the queries to exclude/include the deleted records as needed or 2) Create a VIEW with the records already filtered based on the deleted field and user that in the applicable queries.

[I-AM-OBODO]

Because he doesn't know the full scope and purpose of your database. I agree with Jacques1 in that you should not delete rows from one table just to populate the same data into another table. There are some legitimate reasons why that might be appropriate, but I'm confident that is not the case here. If you need the data for historical purposes, just have a column in the table for a "deleted" flag.

 

Then, YOU need to analyze your table. Are there columns that need to be unique (other than an internal primary key)? If so, those constraints could be moved to the business logic. But, assuming there are no unique fields other than the primary key it is much simpler. Then you would just need to make one last decision.

 

It is assumed that most/all current queries should not return/operate on these "deleted" rows. depending on how many queries you have already built and/or the complexity you expect there will be you could 1) Modify the queries to exclude/include the deleted records as needed or 2) Create a VIEW with the records already filtered based on the deleted field and user that in the applicable queries.

 

Got it. Thanks