Soft Delete 2

[bigcat48]

Is there anyway to soft delete an entry. Let me further explain.

Add to one table and remove from another table.

Could this be done with one action or script?

Please assist if so.

Thank you.

 

[jpadie]

with two queries.

insert into table2 (select * from table1 where uniqueid=xxx)
delete from table1 where uniqueid=xxx

 

[bigcat48]

I'm thinking that I am not properly concatenating the two strings.

This is what I have so far:

<?php
	// Redirect browser
	header("location:[URL unfurl="true"]http://localhost/profiles/list_companies-test.php");[/URL]

	include("includes/connection.php");
	
	$id  = $_GET[id];
	
	$query1 = "INSERT into tblCompanies1 (SELECT * FROM tblCompanies WHERE id=$id)";
	$query2 = "DELETE from tblCompanies WHERE uniqueid = $id";
	$sql = ("$query1" . "$query2");
	
	$rs  = odbc_exec($conn,$sql);
		
	if (!$rs)
	  {exit("Error Occured: No record deleted");}
	  {echo "<p>Record has been deleted.</p>";}
	
	odbc_close($conn);
?>

How should this be written?

 

[jpadie]

$query[] = "INSERT into tblCompanies1 (SELECT * FROM tblCompanies WHERE id=$id)";
    $query[] = "DELETE from tblCompanies WHERE uniqueid = $id";
   
    
    foreach ($query as $sql){ 
     $rs  = odbc_exec($conn,$sql);
        
    if (!$rs)
      {exit("Error Occured: No record deleted");} else 
      {echo "<p>Record has been deleted.</p>";}
    }
    odbc_close($conn);

 

[johnwm]

If you want to do 2 interrelated queries AND you are using an InnoDB database you should look at

http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-model.html

which gives guidance on Start Transaction/Commit/Rollback.

The other possibility (if your tables are not over-large) is to design with an isActive field set to 1 by default and reset to 0 to 'remove' the record from current use simply with a WHERE clause

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[bigcat48]

Thank you jpadie!

This is what worked for me

<?php
	// Redirect browser
	header("location:[URL unfurl="true"]http://localhost/profiles/list_companies-test.php");[/URL]

	include("includes/connection.php");
	
	$id  = $_GET[id];

	$query[] = "INSERT INTO tblCompanies1 (companyName,  corpAddress1, corpAddress2, city, state, zip, country, officePhone, emailAddress, website, dateCreated, createdBy, dateUpdated, updatedBy) 
	SELECT * 
	FROM tblCompanies 
	WHERE id = $id";
    $query[] = "DELETE FROM tblCompanies WHERE id = $id"; /* This line works */
    
    foreach ($query as $sql){
     $rs  = odbc_exec($conn,$sql);
    
    if (!$rs)
      {exit("Error Occured: No record deleted");} else
      {echo "<p>Record has been deleted.</p>";}
    }
    odbc_close($conn);
?>

 

[MichealC4]

<?php
    // Redirect browser
    header("location:[URL unfurl="true"]http://localhost/profiles/list_companies-test.php");[/URL]

    include("includes/connection.php");
    
    $id  = $_GET[id];

    $query[] = "INSERT INTO tblCompanies1 (companyName,  corpAddress1, corpAddress2, city, state, zip, country, officePhone, emailAddress, website, dateCreated, createdBy, dateUpdated, updatedBy)
    SELECT *
    FROM tblCompanies
    WHERE id = $id";
    $query[] = "DELETE FROM tblCompanies WHERE id = $id"; /* This line works */
    
    foreach ($query as $sql){
     $rs  = odbc_exec($conn,$sql);
    
    if (!$rs)
      {exit("Error Occured: No record deleted");} else
      {echo "<p>Record has been deleted.</p>";}
    }
    odbc_close($conn);
?>

Please, please, please, please do not use this code!

You have the following:

$id = $_GET[id];

Then in your two SQL queries, you use $id unsanitized.

$query[] = "INSERT INTO tblCompanies1 (companyName,  corpAddress1, corpAddress2, city, state, zip, country, officePhone, emailAddress, website, dateCreated, createdBy, dateUpdated, updatedBy)
    SELECT *
    FROM tblCompanies
    WHERE id = $id";
    $query[] = "DELETE FROM tblCompanies WHERE id = $id"; /* This line works */

That is begging for SQL injection to happen, which is very, very bad.

----------------------------
"Will work for bandwidth" - Thinkgeek T-shirt

 

[MichealC4]

Instead of leaving $id unsanitized, here's a few basic suggestions to get you started. They are by no means complete, but will help.

For example, here since you are expecting an int (?), you could typecast it to an int:

...

$id = (int) $_GET[id];

...

Here's something I did:

...
// Get the query string for the id
$id = intval($_GET['id']);

if(empty($id))
{
        // Tell the user that they need a valid id
        echo 'Please check your link. You need a valid identifier.';
}

else
...

This code isn't the best example, but it works for what I'm suggesting. Keep in mind that if $id is NULL, intval() will return a 0, possibly leading to things you don't want. Another suggestion is to use strspn and compare it to strlen. For example:

...
$id = $_GET[id];

$length = strspn($id, "1234567890");

if($length != strlen($id))
{
    echo 'Something bad just happened';
}

else
...

This is incomplete advice as I could literally spend days on this subject, but it should push you in the right direction.

----------------------------
"Will work for bandwidth" - Thinkgeek T-shirt