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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

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

Post 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?
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

try making those if's, to else if's
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

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

Post 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.
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post 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?
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post 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...
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post 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.
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post 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.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

$q1 = execute_query($sql1);
if($q1){
$q2 = execute_query($sql2);
}
if($q2){
}
and so on..
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post 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.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post 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.
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post 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.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

What would this be needed for?
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post 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.
User avatar
dethron
Forum Contributor
Posts: 370
Joined: Sat Apr 27, 2002 11:39 am
Location: Istanbul

Post 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.
Post Reply