Jump to content


Photo

MySQL infinite loop?


  • Please log in to reply
5 replies to this topic

#1 trillion

trillion
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 20 September 2006 - 06:36 PM

some values in one mysql are acting quite erratically and I am wondering if I have accidentally coded and infinite loop. Here is my code:

$move_q1 = "UPDATE classes SET class = 'pos2' WHERE class = 'pos1'";
$move_r1 = mysql_query($move_q1) or die('Query failed: ' . mysql_error());
$move_q2 = "UPDATE classes SET class = 'pos1' WHERE class = 'pos2'";
$move_r2 = mysql_query($move_q2) or die('Query failed: ' . mysql_error());

will this cause mysql to continually switch these values or will it do it once and stop?

What I want to happen is
when an element using 'pos1' is changed by submit to use 'pos2'
then the element using 'pos2' switches automatically to 'pos1'

my code wasn't working so I went to the command line and did a select * command for my table.

I got two columns with 'pos1'

out of curiosity I did again select *

the two columns that were 'pos1' are now 'pos2'

more select * commands showed that these values were unstable - they kept on switching

What is going on? How can I get the results I want?

here is my table structure

I will gladly change it if need be

+--------+-------------+------+-----+---------+-------+
| Field    | Type        | Null  | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| class  | varchar(20) | YES  |      | NULL  |          |
| src      | varchar(20) | YES  |      | NULL  |        |
| height  | varchar(20) | YES  |      | NULL  |        |
| width  | varchar(20) | YES  |      | NULL  |        |
| op      | varchar(2)  | YES  |      | NULL    |        |
+--------+-------------+------+-----+---------+-------+

well thaat came out badly, but whatever there it is

#2 Daniel0

Daniel0
  • Staff Alumni
  • Advanced Member
  • 11,956 posts

Posted 20 September 2006 - 06:40 PM

I don't understand what you mean. MySQL can't do loops.

#3 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 20 September 2006 - 09:25 PM

look http://www.phpfreaks...c,103521.0.html

#4 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 21 September 2006 - 01:55 AM

I'll offer another suggestion (sasa's idea will work, though there seems to be a syntax error).

You can set class = 'temp' where class = 'pos1'
Then class = 'pos1' where class = 'pos2'
Then class = 'pos2' where class = 'temp'

Three queries, when you could do it in one, but it will also work :)  If you can get sasa's technique to work then use that, it is safer and faster than this one.

#5 trillion

trillion
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 21 September 2006 - 02:58 AM

I couldn't get the example from sasa to work as I am dealing ideally with only one variable in each row that is effected

the temp idea works great when i run it at the command line however there is something in my php that is making mysql show very strange results

if i comment out the mysql query that updates the tables everything comes off clean and nothing is changed.  Just as it should be.

however when I uncomment the update table queries and give the signal for the script to be run all this happens:

1) the browser hangs up.
2) when I go back to the command line and do a select * from classes; the class column, with pos1 and pos2 are unstable. I run the select all command say five times in a row, without doing anything else and the values are different on just about every select * execution.

is this because of some lag effect. am I some how asking mysql to change these values a number of times each?

I beleive this is the case. after I typed this far I went back and ran select * twice and the valuse were stable.

So how am I asking for these values to be change that many times?

here is my code again.

$lt = $_POST['tl'];

if ($lt == 'rt') {
$move_q1 = "UPDATE classes SET class = 'temp' WHERE class = 'pos1'";
$move_r1 = mysql_query($move_q1) or die('Query failed: ' . mysql_error());
$move_q2 = "UPDATE classes SET class = 'pos1' WHERE class = 'pos2'";
$move_r2 = mysql_query($move_q2) or die('Query failed: ' . mysql_error());
$move_q3 = "UPDATE classes SET class = 'pos2' WHERE class = 'temp'";
$move_r3 = mysql_query($move_q3) or die('Query failed: ' . mysql_error());
}


#6 trillion

trillion
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 21 September 2006 - 04:43 AM

I finally got the desired results with this:

$lt = $_POST['tl'];

if ($lt == 'rt') {

$move_q1 = "UPDATE please SET status = '1' WHERE position = 'pos2'";
mysql_query($move_q1) or die('Query failed: ' . mysql_error());
$move_q2 = "UPDATE please SET position = 'pos2' WHERE position = 'pos1'";
mysql_query($move_q2) or die('Query failed: ' . mysql_error());
$move_q3 = "UPDATE please SET position = 'pos1' WHERE status = '1'";
mysql_query($move_q3) or die('Query failed: ' . mysql_error());
$move_q4 = "UPDATE please SET status = '0' WHERE position = 'pos1'";
mysql_query($move_q4) or die('Query failed: ' . mysql_error());
}

this code seems pretty long to acheive the result of switching two values.

If anyone could now help me shorten it I would be very grateful

Thank you




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users