Page 1 of 1

2 (my)SQL-query at a time.....

Posted: Fri Feb 20, 2004 6:45 pm
by dethron
Hi;
I'm using PHP, MySQL (just like u.)

The problem is in one of my functions.

I'm sending 2 different query to the database.

I want to them executed both, or be rolled back.

In my function the code is like that....

Code: Select all

<?php
$q1 = "INSERT INTO tbname1 VALUES ($bla1, $bla2, $bla3)";
// where $bla1 is key
Execute_Query($q1) ? $flag1 = true : $flag1 = false;

$q2 = "UPDATE tbname2 SET VALUES fld1 = fld1+1"
// fld1 is the field of the table tbname2
Execute_Query($q1) ? $flag2 = true : $flag2 = false;

if($flag1 && $flag2)
    return true;
if(!$flag1 && $flag2){
    $q4 = "UPDATE tbname2 SET VALUES fld1 = fld1-1"
    Execute_Query($q4);
}
if($flag1 && !$flag2){
    $q3 = "DELETE FROM tbname1 WHERE fld1 = $bla1";
    Execute_Query($q3);			
}
return false;
?>
What i'm trying to say is that : If any of these queries($q1,$q2) can not be executed, then another should not be executed, too. And if it is executed, it should be rolled back.

But the problem rises here. There is a possibility to fail queries for $q3 and $q4.

P.S : I have changed the code a little bit, the above code can be written in better format. But even this case, the problem is same.

BIG DEAL : WHAT IF THE ROLL BACK STATEMENT FAILS?

Posted: Fri Feb 20, 2004 6:51 pm
by Illusionist
try making those if's, to else if's

Re: 2 (my)SQL-query at a time.....

Posted: Fri Feb 20, 2004 6:58 pm
by dethron
dethron wrote:Hi;
P.S : I have changed the code a little bit, the above code can be written in better format. But even this case, the problem is same.
Changing the if-else structure is not works, because we are talking about the inside part of the if-statement.

We are trying to solve the situation if any of $q3 or $q4 fail.

Let say, we are considering just $q3.

We should also pay attention to failure of $q3. May be what i need is a loop.

Just like that,

Code: Select all

<?php
$q3 = "......."
while(!Execute_Query($q3)){
 $do_something; 
}
return true;
?>
But this is not good as i want, because there is a possibility for an infinite-loop.

Posted: Fri Feb 20, 2004 7:03 pm
by dethron
Of course we can chage the while-loop to prevent an infinite-loop just like that :

Code: Select all

<?php
$flag=true;
$counter = 0;

while($flag){
    if(Execute_Query($q3))
        return true;
    else 
       $counter++

    if($counter>=30){
       $flag = false;
       SendMailToAdmin("Fail in $q3")
    }
}
?>
But we do not solve the problem, just post it to the admin. (It is not desired.)
Sure, there should be other ways. What are your ideas?

Posted: Fri Feb 20, 2004 7:30 pm
by Illusionist
it might help to know what Execute_Query is...

And oh ya, not everyone here uses MySQL.. just so you know and don't add comments like "(Just like you.)" and look stupid...

Posted: Fri Feb 20, 2004 7:35 pm
by dethron
Illusionist wrote: and look stupid...

Code: Select all

<?php
if($Illusionist_Wants_To_Help)
   echo "thnx";
else
   echo "go $You_Dont_Want_To_Hear yourself";
?>
Everytime I post something, you disagree.
I dont need your help.
Thnx.

Posted: Fri Feb 20, 2004 7:45 pm
by Illusionist
ok, being as this is the first time i've read one of your post.. that really makes sense. And second your code about me doesn't make sense at all...

go $You_Dont_Want_To_Hear yourself

That doesn't make any sense...

Anyways if you'd like help it'd be nice to see all the code.. if you use a custom function in your script that could possibly be causing an error, then it'd probably be a good idea to include it with the code you post. For future refrences, keep those in mind.

Posted: Fri Feb 20, 2004 8:10 pm
by d3ad1ysp0rk
$q1 = execute_query($sql1);
if($q1){
$q2 = execute_query($sql2);
}
if($q2){
}
and so on..

Posted: Fri Feb 20, 2004 8:18 pm
by dethron

Code: Select all

<?php
		$resultSQL = "INSERT INTO preparations VALUES (
		".$sIn["UserID"].",
		".$sIn["EventID"].",
		)";
		if(ODBQ($resultSQL)){
			$resultSQL = "UPDATE sub_part SET VALUES COUNT = COUNT + 1 WHERE EventID = ".$sIn["EventID"];
			if(ODBQ($resultSQL))
				return = true;
			// roll back
			$flag = true;
			$counter = 0;
			$resultSQL = "UPDATE sub_part SET VALUES COUNT = COUNT - 1 WHERE EventID = ".$sIn["EventID"];
			while($flag){
				if(ODBQ($resultSQL))
					$flag = false;
				else 
					$counter++;
				if($counter>=5){
					$flag = true;
					Warn_Admin();
					return false;
				}
			}
		}
		return false;
?>

Where ODBQ is a generic function to execute the query. I write this code since i do not want to bother with the type of the database. I ignore the db-type, then just pass the SQL-query to my function, the function hadles the rest. It is working fine, i have used it many times.

The above I stated the final code. I just want to remove Warn_Admin(); part. The code is working, but it doesn't smell good :)

By the way, LiLpunkSkateR, i can't get your idea, please explain it.
If you don't get any part of the code, please ask.

Thnx.

Posted: Fri Feb 20, 2004 8:22 pm
by d3ad1ysp0rk
make execute_query (your function im guessing) return either true or false, depending on whether the query ran successfully or not.

if it's true, it will move onto executing the next query, and so on.

Posted: Fri Feb 20, 2004 8:25 pm
by dethron
Yes your guess is true,
the execute_query function return false in case of any failure, and
the result of the query otherwise.

But the point is that :

If first query are completed, then the script runs the second query.
Let's assume that second query fails.
Then we must roll back the first query.
Lets create a new query to roll back the first.
(Problem here)
What if our roll back query fails.

Thnx.

Posted: Fri Feb 20, 2004 8:41 pm
by d3ad1ysp0rk
What would this be needed for?

Posted: Fri Feb 20, 2004 8:46 pm
by dethron
Since two query related with each other. Any of them is meaningless without another. They are equal parts of whole system.

I wonder whether there is any method to send two query to db at a time or not.

If still cant imagine what i want to do, let me know; then I'll explain with an example.

Thnx.

Posted: Sat Feb 28, 2004 3:41 pm
by dethron
Finally, I found what I need.
Thanx to all.

I want to share the link.

http://hotwired.lycos.com/webmonkey/02/11/index3a.html

A good article about transactions on mysql.