multi query

[thelee]

How i can delete record from table `order` and move it to `order2`

the table `order2` structure is exactly like table `order`.here is the code that i made. 

<?
	include("connection.php");
	$id_product=$_GET["pid"];
	
	$sql_delete="DELETE FROM `order` WHERE id_product='$id_product'";
	
				
	$result=mysql_query($sql_delete) or die("Error in sql due to ".mysql_error());
	
	if ($result)
	header("Location: order_list.php");
	
?>

[ginerjm]

1 - Stop using MySQL_* functions.  They are soon to be defunct.  Check the manual for proof and then research mysqlI or PDO for your db acitviity.

 

2 - You have a delele query already.  Only problem is you need to sanitize your input argument to be sure it is valid and does not contain any malicious data in it.  Or - when you switch to PDO (my preference) use a prepared query statement instead.

 

3 - How are you going to get the data from the deleted record to insert into the other table?  Personally, I would do a query to read the 'old' record, insert that data into the new table, and then go back and do the delete of the old record.  (Of course there is probably a cleaner more efficient way to accomplish this but since I've never had to do this, I haven't researched this.)

[CroNiX]

2 Steps:

INSERT INTO order2 SELECT * FROM `order` WHERE id_product='$id_product';

 

Then do your delete from order as you are already doing.

 

Note the above will only work if the columns are identical and in the same order in both the "order" and "order2" tables.

Edited August 11, 2014 by CroNiX

[Jacques1]

What is the purpose of the second table when it has the exact same structure of the first one?

[thelee]

i tried like this and its not working.

<?
	include("connection.php");
	$id_product=$_GET["pid"];
	
	$sql = "INSERT INTO `order2` SELECT * FROM `order` WHERE id_product='$id_product'";
	$sql_delete="DELETE FROM `order` WHERE id_product='$id_product'";
	
				
	$result=mysql_query or die("Error in sql due to ".mysql_error());
	
	if ($result)
	header("Location: order_list.php");
	
?>

the purpose of two tables is one for admin and one for staff. when staff delete the data,it will send to the admin.

[Psycho]

the purpose of two tables is one for admin and one for staff. when staff delete the data,it will send to the admin.

 

So, when a record is deleted, you really don't want it deleted since you want "admin" users to be able to view them. You do NOT want two tables. Instead create a new field in the table called "deleted" with a default value of 0. When a user deletes a record change the value of that field to a 1 (i.e. TRUE). make sure that any queries to display records to users exclude records that are "deleted". Then make your queries for the admins which will include the "deleted" records. Copying data from one table to another only creates more work with no benefit,

[thelee]

sorry pysco, i dont think your way is much better,its my system and i know how it works.dont post here if you just want to increase your posts. this forum getting sucks. seriously. lot of admin doesn't help.btw i already solve my problem by myself. thank you.

[Psycho]

sorry pysco, i dont think your way is much better,its my system and i know how it works.dont post here if you just want to increase your posts. this forum getting sucks. seriously. lot of admin doesn't help.btw i already solve my problem by myself. thank you.

 

Hey, it's your project. I'm not saying you can't do it how you think it should be done. Go for it. I'm only trying to provide guidance based upon experience and knowledge. There are some basic principles with respect to best practices regarding various things such as Database normalization, data abstraction, etc. Believe me, most of us providing help on this forum have made the same mistakes as everyone else. Sometimes people take our advice and sometimes they don't. The fact that you feel so strongly in your position yet do not have the experience or knowledge to know why it is better or worse is telling. There are many reasons why that approach is problematic which I won't waste my time going into since you are not receptive to it. But, just to highlight for others that might see this post, here is one. A delete operation is much more expensive (i.e. performance drag) because indexes have to be updated. Using a "soft delete" flag in tables is a tried and true methodology.

[mac_gyver]

another advantage of the method mentioned by Psycho, is you can just create a view to your table that only shows the non-deleted rows, then just use the view name as the table name in any query where you want to exclude the deleted rows.