2. PHPRunner General questions
  3. DELETE record in table A while UPDATING field in table B
This topic is locked

DELETE record in table A while UPDATING field in table B

 		2/15/2008 10:45:26 AM
PHPRunner General questions
L
 Lisa2006 author

I have 2 tables in my project.
[font=Verdana]Table A: posting_backup

Fields: name, address, telephone, order_number, deleted_status[/font]

[font=Verdana]

Table B: posting

Fields: name, address, telephone, order_number
[font=Verdana]

I need to carry out the following:

While i'm in the posting_list page, i check off the appropriate record to be deleted and click on "Delete Selected". At this point i want the corresponding record contained in the posting_backup table to have its field "deleted_status" amended to 1.
[font="Verdana"]I have entered the following code at posting | List Page | After Record Deleted Event

[font="Verdana"][/font] ;

global $conn;

$result = mysql_query("SELECT * FROM posting_backup where order_number='".$values["order_number"]."'");

[font="Verdana"][color=#000080]while($row = mysql_fetch_array($result))

{

$strUpdate = "update posting_backup set deleted_status = '1' where order_number = '".$_SESSION["order_number"]."'";

db_exec($strUpdate,$conn);

}

return true;[/color][/font]

[font=Verdana]

[/font] or any other combination, i simply get errors or no display.
Your help would be much appreciated.
Lisa
L
 		Lisa2006 author 2/15/2008

I've replaced part of the previous script as below:- with the physical contents of order_number field value from the posting table.
i.e. posting table - order_number field = E202

Additionally, posting_backup - order_number field = E202
global $conn;

$result = mysql_query("SELECT * FROM posting_backup where order_number='E220'");

[font="Verdana"][color=#000080]while($row = mysql_fetch_array($result))

{

$strUpdate = "update posting_backup set deleted_status = '1' where order_number = '".$row["order_number"]."'";

db_exec($strUpdate,$conn);

}

return true;[/color][/font]

[font=Verdana]

When executed it works 100%.
In my original coding, there appears to be a problem with pulling the value E202 from order_number, posting table.
Your help with resolving this would be much appreciated.
Lisa
[/font]
J
 		Jane 2/18/2008

Lisa,
ID of deleted records are stored in the $deleted_values array:

global $conn;

$result = mysql_query("SELECT * FROM posting_backup where order_number='".$deleted_values["order_number"]."'");

while($row = mysql_fetch_array($result))

{

$strUpdate = "update posting_backup set deleted_status = '1' where order_number = '".$deleted_values["order_number"]."'";

db_exec($strUpdate,$conn);

}

return true;
L
 		Lisa2006 author 2/18/2008

Thanks Jane,
I'll give this a try.
Lisa
L
 		Lisa2006 author 2/18/2008

Hi Jane,
I've got another quick question:
Based on the table schema below:

Table: posting_backup

Fields: name, address, telephone, order_number, deleted_status
I only need to display records based on the deleted_status = 0. If this field contains '1' then it should not be displayed on posting_backup_list.php
I believe this is done via Edit SQL Query, but i'm not sure how to code it.
Thanks in advance Jane.
Lisa
J
 		Jane 2/18/2008

Hi,
just add where clause to your SQL query on the Edit SQL query tab:

where deleted_status=0
L
 		Lisa2006 author 2/18/2008

Hi,

just add where clause to your SQL query on the Edit SQL query tab:


Thank you ..... Jane,
Lisa