Page 1 of 1

Insert row then delete it...

Posted: Wed Jun 23, 2004 8:52 pm
by hawleyjr
I have two insert queries. If the second query fails, I want to delete the first query.

TABLE STRUCTURE:

TABLE1

Code: Select all

ID | COMPANY_NAME | DTTM
TABLE2

Code: Select all

ID | UNAME
QUERY:

Code: Select all

$qry ="INSERT INTO TABLE1(COMPANY_NAME,DTTM) VALUES('comp name',now())";

$result = mysql_query($qry);
$new_id = mysql_insert_id();

//FORCE THE SECOND QRY TO NOT WORK
//TESTING ONLY
unset($new_id);

$qry2 = "INSERT INTO TABLE2 (ID,UNAME) VALUES($new_id,'')";

$result = mysql_query($qry2); 

if (!$result){//QUERY DOESN'T WORK
	//DELETE FIRST INSERT
	$qry = "DELETE FROM TABLE1 WHERE ID=$new_id";
	$result = mysql_query($qry);
	echo ‘results:’.$result;
	
}
When I try to delete the row it does not delete. I have tried:

Code: Select all

sleep(10);
In between queries, but it didn't help.

Any thoughts?

Posted: Wed Jun 23, 2004 8:58 pm
by feyd
why not just calculate/query for the next id table1 would get instead?
Here's the function I use in my DB class..

Code: Select all

<?php

	function next_id($tblname)
	{
		if(!$this->connected)
			return !Failed($this->err->entry(SQL_NOCONN)); // ignore this, it's a custom error logging system
		$query = $this->query("SHOW TABLE STATUS LIKE '$tblname'");
		$row = $this->fetch_assoc($query);
//	print_r($row);
		if(!empty($row['Auto_increment']))
			return (int)$row['Auto_increment'];
		else
			return false;
	}

?>

Posted: Wed Jun 23, 2004 9:00 pm
by hawleyjr
I'm not sure what you mean by "calculate/query" my actual app has about 5 inserts into different tables. If one insert doesn't work, I want to remove any of the previous inserts.

Posted: Wed Jun 23, 2004 9:04 pm
by feyd
have you tried closing and re-opening the connection to do the delete?

Posted: Wed Jun 23, 2004 9:08 pm
by hawleyjr
I just tried that. Doesn't work.

Posted: Wed Jun 23, 2004 9:11 pm
by markl999
Sounds like you need a transaction (presuming you have the required version of mysql)

Posted: Wed Jun 23, 2004 9:14 pm
by hawleyjr
I'm an IDIOT!!!!!!!


The username I was using only had insert/update privileges.


Thanks for the help Feyd

BTW: the original code works fine!

Posted: Wed Jun 23, 2004 9:32 pm
by feyd
I have no idea what I helped on, but I'll take it. :D

Posted: Wed Jun 23, 2004 11:33 pm
by Weirdan
here is the snippet I used once to emulate transactions:

Code: Select all

/**
 * In PHP4 function defined in a scope of  another function
 * has no access to  'parent'  function's scope.  So proper
 * closures (like in Object Pascal) are  impossible.
 * Operator overloading  is absent also. So we can't create
 * functors like in C++.
 * Callback passed to preg_replace_callback does not accept
 * any user-defined params.
 * Thus  we  _have_  to   define   this  placeholder  array
 * globally.
 * It is used to collect the last_insert_ids of the queries
 * with 'saveid' flag set.
 *
 * UPDATE: actually, we can use functor-like approach using
 * 'call_user_func_array',  'create_function'  and   custom
 * 'closure' class, but it would be a mess in this case.
 */
$__ids=array();

/**
 * simulates transaction on non-transactional table types
 * do not work for multi-row inserts
 * attempts to rewind the transaction, but does not check if
 * rewind was successful
 *
 * @param $queries         array  array of queries to execute
 * @param $reverse_queries array  array which consists of
 *                                reverse queries
 * @return                 bool   true if transaction succeded,
 *                                false if  forced to reverse
 */
function simulate_transaction($queries, $reverse_queries){
    global $mysql,$__ids;
    $size = count($queries);
    $reverse = false;
    for($i=0; $i<$size; $i++) {
      $queries[$i][0] = preg_replace_callback( //parse for %idXX macros
          "/%id(\d{1,2})/",
          create_function(
              '$matches',
              'global $__ids;
               return $__ids[$matches[1]];'
              ),
          $queries[$i][0]
          );
      if( !$mysql->query($queries[$i][0])
         || (!$mysql->num_rows && $queries[$i][1] == 'affect') 
      ) {
          $reverse = true;
          break;
      }
      if( stristr($reverse_queries[$i], '%id') ) { //parse reverse query for %id macro
          $reverse_queries[$i] = eregi_replace('%id',
                                                               strval( $mysql->insert_id() ),
                                                               $reverse_queries[$i]
                                           );
      }
      if(@$queries[$i][2] == 'saveid') {
          $__ids[$i] = $mysql->insert_id();
      }
    }
    if($reverse)
      for(--$i; $i>=0; $i--)
        $mysql->query($reverse_queries[$i]);
    return !$reverse;
}