While code not properly being processed

alexoldshane

Hey guys. So, I've got this code below. Now, for some reason, the while which is marked keeps failing. If the operator I use is ==, it will cause the website to timeout. However, if I use '=' it just ignores that while entirely. Now, I've got it set so that it should in fact parse. I just... clearly I'm not seeing something here. I'm pretty sure it's a simple mistake, but I've looked over it for a good couple hours now and just can't get it to work. Any questions, feel free. Thanks!

$slotzero = "SELECT * FROM t_monsters WHERE trainer = '$obtainusers3[Username]' AND slot = '0'";
$slotzero2 = mysql_query($slotzero) or die("Slot zero check fail - count -- Ln 24.");
$slotzero3 = mysql_num_rows($slotzero2);
print "<tr><td>$slotzero3 monsters had a slot of zero.</td></td>"; //record keeping

if(!($slotzero3 = 0)){ //says if any monsters have a slot number of zero
$getzeros1 = "Select * from t_monsters where trainer = '$obtainusers3[Username]' and slot = '0' order by ID ASC";
$getzeros2 = mysql_query($getzeros1) or die("Get zeros fail - Ln 29.");
print "Zeros changed:";
$getzeros = mysql_fetch_array($getzeros2);
while($getzeros = mysql_fetch_array($getzeros2)){ //goes thru the list of monsters slot 0, much like roster displays the list
$zerocounter = 0;
print "Pass on line 33";
//Issue is with immediate below while code. with == it loops continuously, with = it bypasses
while($zerocounter == 0){
    print $getzeros[Slot];
$newslot = $getzeros[Slot] + 1;
    print $newslot;
$updatezeros = "Update t_monsters set slot = '$newslot' where trainer = '$obtainusers3[Username]' and id='$getzeros[ID]'";
mysql_query($updatezeros) or die("Update zeros slots fail. - Ln 36");
$newzerocheck = "Select * from t_monsters where trainer = '$obtainusers3[Username]' and slot = '$newslot'";
$newzerocheck2 = mysql_query($newzerocheck) or die("New zero check die. - Ln 38");
$newzerocheck3 = mysql_num_rows($newzerocheck2);

print "Monster:$getzeros[Monster]  ID:$getzeros[ID]<br>";
if($newzerocheck3 > 1){//checks to see if new slot for monster is already held. if so, keeps value at zero so that 
                        //previous while is performed and a new slot is obtained
$zerocounter = 0;
}
elseif($newzerocheck3 = 1){ // if the monster is only one with this slot for this trainer, sets value to 1 so that can move on to next
                            // monster in the sequence of those with a slot of 0
$zerocounter = 1;
}
else{
die("Zerocheck is coming back < 1... something is jacked up.");
} //end of else from zerocheck
}// end of while from zerocounter 
} // end of while for getzeros
print "</td></tr>";
} // end of if for slotzeros - at this point should no longer have monsters with a slot of zero for the trainer.

bradgrafelman

Yikes... first thing you should do is learn how to properly indent/format your code. Otherwise:

impr  op erl yspac edcodeis  h ardt   o re   a d,y   o uk n  ow?

After you do that (and then re-post the well formatted version, of course), can you explain the general idea behind what you're trying to do with all of that code? The reason I ask is because as soon as I see SQL queries executed inside loops processing other SQL queries, huge red flags go up in my mind as there is likely a much better (i.e. more optimized and cleaner) way to do things.

NogDog

Ran it through my beautifier macro, because I was bored:

$slotzero = "SELECT * FROM t_monsters WHERE trainer = '$obtainusers3[Username]' AND slot = '0'";
$slotzero2 = mysql_query($slotzero) or die("Slot zero check fail - count -- Ln 24.");
$slotzero3 = mysql_num_rows($slotzero2);
print "<tr><td>$slotzero3 monsters had a slot of zero.</td></td>"; //record keeping
if (!($slotzero3 = 0)) 
{ //says if any monsters have a slot number of zero
	$getzeros1 = "Select * from t_monsters where trainer = '$obtainusers3[Username]' and slot = '0' order by ID ASC";
	$getzeros2 = mysql_query($getzeros1) or die("Get zeros fail - Ln 29.");
	print "Zeros changed:";
	$getzeros = mysql_fetch_array($getzeros2);
	while ($getzeros = mysql_fetch_array($getzeros2)) 
	{ //goes thru the list of monsters slot 0, much like roster displays the list
		$zerocounter = 0;
		print "Pass on line 33";
		//Issue is with immediate below while code. with == it loops continuously, with = it bypasses
		while ($zerocounter == 0) 
		{
			print $getzeros[Slot];
			$newslot = $getzeros[Slot] + 1;
			print $newslot;
			$updatezeros = "Update t_monsters set slot = '$newslot' where trainer = '$obtainusers3[Username]' and id='$getzeros[ID]'";
			mysql_query($updatezeros) or die("Update zeros slots fail. - Ln 36");
			$newzerocheck = "Select * from t_monsters where trainer = '$obtainusers3[Username]' and slot = '$newslot'";
			$newzerocheck2 = mysql_query($newzerocheck) or die("New zero check die. - Ln 38");
			$newzerocheck3 = mysql_num_rows($newzerocheck2);
			print "Monster:$getzeros[Monster]  ID:$getzeros[ID]<br>";
			if ($newzerocheck3 > 1) 
			{ //checks to see if new slot for monster is already held. if so, keeps value at zero so that
				//previous while is performed and a new slot is obtained
				$zerocounter = 0;
			}
			elseif ($newzerocheck3 = 1) 
			{ // if the monster is only one with this slot for this trainer, sets value to 1 so that can move on to next
				// monster in the sequence of those with a slot of 0
				$zerocounter = 1;
			}
			else
			{
				die("Zerocheck is coming back < 1... something is jacked up.");
			} //end of else from zerocheck

	} // end of while from zerocounter

} // end of while for getzeros
print "</td></tr>";
} // end of if for slotzeros - at this point should no longer have monsters with a slot of zero for the trainer.

alexoldshane

Sorry about that. I meant to go back and edit it after I saw how it came out. But not allowed to edit my posts. Anyways,

Basically, what the script is supposed to do is this:

Currently, two of my sql tables are connected via some php code in such a way, that when one of the values change, a corresponding on in the other table changes.
Well, while troubleshooting some errors in our code (Not the one above), some of the values got mixed up and the tables are now... a mess. This code here, is basically a fix.
The above code is just a part of the overall code that is supposed to do the following:

Call all users. Within each user, call all 'monsters'. Compare all the monsters slot (like a party/roster). If any of the slots are equal, meaning, they are in the same spot - there is an issue that needs to be fixed. Code should then modify that value by adding 1 to it. Recompare against the table to see if that monster now is in the same slot. This repeats until it is in its own location.

This below is the portion to check for any monsters with a slot of 0. We don't use zero in our slot system, as it is just easier to follow if 1 = starter, 2 = second, etc.
So, this should call all trainers, check their monster information. If any of the monsters have a slot number of 0, to run the code below to fix that and set into their own slot that != 0. Upon fixing all the monsters that have a slot of 0 for that trainer, it should move onto the next trainer and repeat until the table is good to go.

While, we could very well do this manually, we still have a good deal of testing to do on the other side of things, and this will be an easier "restore" process in the long run.

alexoldshane

I found the issue with this bit of coding. Reason it was being sent through an infinite loop was due to some of the mysql not being checked properly.

bradgrafelman

Sounds like you've patched up the code enough such that it will do what you need it to? Regardless, here are some comments about your first reply:

alexoldshane;11013439 wrote:
Currently, two of my sql tables are connected via some php code in such a way, that when one of the values change, a corresponding on in the other table changes.

Don't do this. IMHO, this is a very, very bad idea. Why not let MySQL take care of doing this for you? After all, that's exactly what triggers are for!

alexoldshane;11013439 wrote:
Well, while troubleshooting some errors in our code (Not the one above), some of the values got mixed up and the tables are now... a mess. This code here, is basically a fix.

Not really - I'd call it more of a workaround. The real fix would be to simply restore the DB to the latest known-good backup (i.e. the one you took right before you started troubleshooting things). 😉

alexoldshane;11013439 wrote:
Call all users. Within each user, call all 'monsters'. Compare all the monsters slot (like a party/roster). If any of the slots are equal, meaning, they are in the same spot - there is an issue that needs to be fixed.

Why wouldn't you simply SELECT only the rows that contain duplicate values, rather than looping through all rows?

alexoldshane;11013439 wrote:
Code should then modify that value by adding 1 to it. Recompare against the table to see if that monster now is in the same slot. This repeats until it is in its own location.

I don't really understand exactly what's going on here, but it sounds like you should be able to programmatically find the next unused value and immediately set one of the duplicates to that value, rather than brute-forcing it and just creating yet another duplicate until that stops happening.

alexoldshane;11013439 wrote:
Upon fixing all the monsters that have a slot of 0 for that trainer, it should move onto the next trainer and repeat until the table is good to go.

Again, sounds like you should be able to programmatically figure out what the next "good" value is. Once you do that, you could then just run a single UPDATE query without looping over result sets and brute-forcing multiple UPDATEs.

alexoldshane

Actually, yeah. Code isn't fix. I ended up just removing the entire "zero fix" portion and made it so that it would update them all to 1 so that it could be taken care of in the next portion of the code.

However, I'm still having a similar issue.

Reason they are linked is due to I've never actually heard of triggers. Learned mysql on the fly while helping buddy fix his site, and so I've been using what I can get by on.

The database never really had a "good" backup. The real issue is that the roster file is currently out of whack. It is setting values to things that it shouldn't for some reason, and I've yet had time to take a look at it. But I need to fix this before I can fix that - otherwise it'll be a manual fix and I'd rather not have to sift and compare against 3000 lines of mysql having to go back and forth between 2 tables to check values.

I suppose I could do the duplicate calling, but I'm honestly not sure how I'd do it. I can logically think it, but not being able to perform mysql with operators such as > and such kinda kill my process.

Programmatically finding next unused value, would be great. Honestly, I'd prefer it that way. However, I've no idea how other than the method I'm working now. The slots need to be in numerical order without a gap: 1,2,3,4,5... works whereas 1,3,4,5,7 would almost break my system.

This is currently the most updated copy of my "rosterfix" file that I have. Maybe seeing the entire thing would give you more insight?

<?php

$useridcounter = 1;
$usercheck = "Select * from trainers where userID = '$useridcounter'";
$usercheck2 = mysql_query($usercheck) or die("User check fail - ln 5");
$usercheck3 = mysql_num_rows($usercheck2) or die("Initial usercheck die - Ln5.");
print "<table>";

while($usercheck3 > 0){ //maintains that a user exists at X id

$obtainusers = "SELECT * FROM `trainers` where userID = '$useridcounter'";
$obtainusers2 = mysql_query($obtainusers) or die("Could not obtain user -Ln 11.");
$obtainusers3 = mysql_fetch_array($obtainusers2);

print"<table> <tr><td>---$obtainusers3[Username]---</td></tr>"; //record keeping

$slotzero = "SELECT * FROM t_monsters WHERE trainer = '$obtainusers3[Username]' AND slot = '0'";
$slotzero2 = mysql_query($slotzero) or die("Slot zero check fail - count -- Ln 24.");
$slotzero3 = mysql_num_rows($slotzero2);
print "<tr><td>$slotzero3 monsters had a slot of zero.</td></td>"; //record keeping

if(!($slotzero3 = 0)){ //says if any monsters have a slot number of zero
    mysql_query("Update t_monsters set slot = '1' where slot ='0'");
} // end of if for slotzeros - at this point should no longer have monsters with a slot of zero for the trainer.

//selects all monsters of the trainer
$getmonsterinfo = "Select * from t_monsters where trainer = '$obtainusers3[Username]' order by slot ASC";
$getmonsterinfo2 = mysql_query($getmonsterinfo) or die("Get monster info die - Ln 20.");

while($monsterinfo = mysql_fetch_array($getmonsterinfo2)){ //check every monster trainer has in order of slot
    $slotcounter = 0;
    while($slotcounter == 0){// checking one monster
        $slotchecker1 = "Select * from t_monsters where trainer = '$obtainusers3[Username]' and slot = '$monsterinfo[Slot]'";
        $slotchecker2 = mysql_query($slotchecker1) or die("Slotchecker failed getting count - Ln 62.");
        $slotcheck = mysql_num_rows($slotchecker2);

        if($slotcheck == 0){ //no monsters exist at this location, therefore, can ignore
            $slotcounter = 1;
        }
        elseif($slotcheck == 1){ // if no monster has this slot, its good - allows process to move on
            $slotcounter = 1;
            print "<tr><td>$monsterinfo[Monster] ID:$monsterinfo[ID] should be good.</td></tr>";
        }
        elseif($slotcheck > 1){ // if more than one monster has this slot perform:
            $newnonzeroslot0 = $monsterinfo[Slot] ; //Initially had '$newnonzeroslot = $monsterinfo[Slot] +1' but wasn't updating properly
            $newnonzeroslot = $newnonzeroslot0 + 1; // increases slot by one
            print "<tr><td>$monsterinfo[Monster] ID:$monsterinfo[ID] was moved from slot $monsterinfo[Slot] 
                    to slot $newnonzeroslot</td></tr>";  //record keeping
            $updatenonzero = "Update t_monsters set Slot = '$newnonzeroslot' where ID ='$monsterinfo[ID]' 
                                AND Trainer = '$monsterinfo[Trainer]'";
            mysql_query($updatenonzero) or die("Could not update nonzero-slot increase - Ln 69."); //updates mysql for later comparison

        }    
    }//end of second while
}

//This below is mostly just to ensure that the variables that are linked between the two tables are accurate
$newstarter = "Select * from t_monsters where trainer = '$obtainusers3[Username]' and slot = '1'";
$newstarter2 = mysql_query($newstarter) or die("calling slot one monster of trainer failed - Ln 84.");
$newstarter3 = mysql_fetch_array($newstarter2);
$newsecond = "Select * from t_monsters where trainer = '$obtainusers3[Username]' and slot = '2'";
$newsecond2 = mysql_query($newsecond) or die("calling slot two monster of trainer failed. - Ln 85");
$newsecond3 = mysql_fetch_array($newsecond2);
$newthird = "Select * from t_monsters where trainer = '$obtainusers3[Username]' and slot = '3'";
$newthird2 = mysql_query($newthird) or die("calling slot three monster of trainer failed - Ln 86.");
$newthird3 = mysql_fetch_array($newthird2);
$Updatestarters = "Update trainers set Starter = '$newstarter3[ID]' where Username = '$obtainusers3[Username]'";
mysql_query($Updatestarters) or die("Could not update new starter in trainer DB. Ln-91");
$Updatesecond = "Update trainers set Second = '$newsecond3[ID]' where Username = '$obtainusers3[Username]'";
mysql_query($Updatesecond) or die("Could not update new second in trainer DB - Ln 93");
$Updatethird = "Update trainers set Third = '$newthird3[ID]' where Username = '$obtainusers3[Username]'";
mysql_query($Updatethird) or die("Could not update new third in trainer DB - Ln 95");
print "<tr><td>    </td></tr><tr><td>New Starters:</td></tr>
<tr><td>Starter:</td><td>$newstarter3[Monster] ID:$newstarter3[ID]</td></tr>
<tr><td>Second:</td><td>$newsecond3[Monster] ID:$newsecond3[ID]</td></tr>
<tr><td>Third:</td><td>$newthird3[Monster] ID:$newthird3[ID]</td></tr></table>";


$useridcounter = $useridcounter +1;
$obtainusers = "SELECT * FROM `trainers` where userID = '$useridcounter'";
$obtainusers2 = mysql_query($obtainusers) or die("Could not obtain user -Ln 101.");
if($obtainusers2 != ''){
$usercheck3 = mysql_num_rows($obtainusers2) or die("Initial usercheck die - Ln 102.");
}
} // end of while function to check against every trainer - basically end of file.
print "</table>";
?>

bradgrafelman

EDIT: Stupid double post...

bradgrafelman

Lunch break is over, so it's back to work for me. Unless someone else does, I'll try to take a look at this sometime later and see if I can work out a response, but in the meantime...

alexoldshane;11013455 wrote:
The slots need to be in numerical order without a gap: 1,2,3,4,5... works whereas 1,3,4,5,7 would almost break my system.

Why? How does your "system" work (or, more aptly, not work it would seem 😉)?

If it was working on slot 2, and it wanted to get the item in the next slot, how does it do that? It sounds like it just adds one to the current value and then checks for a DB row equal to that new value. Instead, it would be much more flexible to simply let the DBMS take care of finding the next item by telling it you want to SELECT the rows WHERE the slot number is greater than (the current value) and to ORDER BY the slot numbers in ASCending order, LIMITing the result set to 1.

EDIT: Might be cleaner to describe what I mean via SQL queries. Assuming we want the item in the slot after slot #2...

Current method (I'm guessing) which is inflexible:

SELECT col1, col2 FROM myTable WHERE slot = 3

More flexible method:

SELECT col1, col2 FROM myTable WHERE slot > 2 ORDER BY slot ASC LIMIT 1

alexoldshane

What I meant by break my system wasn't in reference to this bit of code. Sorry. I meant the actual website. Slot 1-3 are mandatory to be filled. In the sense that, if 1 and 2 are filled, but 3 is not, there is no issue. However, that would be dependent upon the fact that the user only had 2 slots to fill - it would be impossible to have slots 4 or more filled. Just the setup. 1-3 are the main slots. Determine visuals and sorts about the site.

johanafm

SELECT some, stuff
FROM tbl
WHERE user_id = @some_id
ORDER BY slot ASC LIMIT 3;

might deal with what you describe.

But if the first 3 slots are really special and not like other slots, it's possible they should either make up their own table, or that you should add another column to the current slot table, i.e. slot_type.