Jump to content

multi query


thelee

Recommended Posts

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");
	
?>
Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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 by CroNiX
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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,

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.