how to insert/delete based on 2D arrays

mmoranuk

I am trying to insert a list of records into a table based upon values being true or false in a 2D array. I can figure out how to loop through all the ones with a value of 1 and do an insert but I can't see how to a delete if there is a value of 0.

The app is basically a matrix of people and groups that they belong to. The table is a join table to link people_id and group_id. So I am trying to do:
person_id group_id
1 12
1 15
2 15
2 16
3 16

My HTML code auto generates the matrix using a loop for the following row for each person:

		<input type=\"checkbox\" name=\"person_group_arr[$person_id][$group_id]\" value=\"1\">

My PHP code that receives this post is:

  while(list($index_x, $dummy_x) = each($person_group_arr)) {
    while(list($index_y, $dummy_y) = each($person_group_arr[$index_x])) {
      $value = $person_group_arr[$index_x][$index_y];
	  $query = "
	  	INSERT INTO group_person (
			group_id
		,	person_id) 
		VALUES (
			$index_y
		, 	$index_x
		)";
		$result = mysql_query($query) or die ("Sorry, we are unable to assign people to groups at this point.<br><br>Please try again later.");

	echo "The person is $index_x<br>\n";
	echo "The group is $index_y<br>\n";
	echo " person_group_arr[$index_x][$index_y] = $value<br>\n";
}
  }

This works great. However, I also want to delete from the group_person table if a checkbox is unchecked. Am I missing something obvious? I can't see how to do a delete statement based on a value in the array.....

Thanks in advance, Mark.....

eoghain

Well you are going to run into a couple of problems here. The biggest one being that unchecked checkboxes aren't sent by a browswer when a form is submitted. Now if you user is given all possible choices and can check/uncheck the ones that they want, then you can just delete everything for the given user, and then insert the checked values. You should wrap all of this into a transaction block to make sure you don't leave your database in an inconsistent state if something goes wrong.

mmoranuk

Thanks for the reply - the delete everything first option did cross my mind.

I haven't done too much on transactions using php/mysql so I guess I should go and read up on it. Any particularly good references/sites you can point me to?

eoghain

The MySQL site (www.mysql.com) has all of the information that you need. As far as integrating transactions with PHP, I just do it all through SQL queries to the database, it's simple. You just run your BEGIN TRANSACTION statement then do your work, and if you had no errors when you are done with your work you run your COMMIT statement if you had an error you run your ROLLBACK statement.

mmoranuk

Thanks for your input. Just one other realted question:

how do I do a rollback on error? I can't find any examples of it. All the error examples do:

$result = mysql_query($query) or die("There has been a database error".mysql_error());

What I need to do is a test for an error, then peform a rollback, then die with a message.

pseudo code:

begin work

delete all from table

loop
insert stuff
if result OK then continue
else rollback and exit and message....
end loop

commit

I just need the syntax for the testing of the result for an error. Thanks.

mmoranuk

OK - I think I figured it out....

$result = mysql_query($query) ;
if (mysql_error()) {
  mysql_query("ROLLBACK");
  echo "There has been a database error ".mysql_error();
  exit() ;
}

If there is a better way, please post... Thanks.

eoghain

If that works for your code then that is just fine. The only thing is you are exiting from your code as soon as an error happens and that might not be a graceful way of telling your users that an error happened.

mmoranuk

That is just a quick example - the real thing will have suitably fluffy error messages :-) Thanks for all your help.