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
TABLE2
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:
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.

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;
}