MySQL glitch

[Damond]

I have been working on a Questing mod for my game and I am expecting to constantly be adding new quests. So instead of having to go in edit the page all the time I have added a column to my database take called "active". This way users only see what ever quests are set to active. For the admin side it lists every quest in the game with two extra columns on showing if the quest is active and the other showing a link to edit the quest.

Where I am having my problem is the "active" column. Starting about line 61 here. No mater what the first quest is set too all of the others are shown the same. Meaning if the first quest is set to "yes" all the quests listed after are set to yes even if they are not in the db. Logging in as a normal use I still only see the quests that are set to active in the database.

 

if ($_GET['qstart']) {
       $_GET['qstart'] = abs((int) $_GET['qstart']);
       //Verify.
       $cd         = $db->query("SELECT * FROM questing WHERE qID={$_GET['qstart']}");
       $coud       = $db->fetch_row($cd);
       $item       = $db->query("SELECT * FROM inventory WHERE inv_itemid={$coud['qItem']}");
       $f          = $db->fetch_row($item);
       if ($db->num_rows($cd) == 0) {
           showErrMsg('You are trying to start a nonexistent quest!');
       } 
       if ($db->num_rows($item) < 1) {
           showErrMsg('You don\'t have the '.$coud["qItem"].'. You must get one before starting this quest.');
       }
           $cd   = $db->query("SELECT * FROM questing WHERE qID={$_GET['qstart']}");
           $coud = $db->fetch_row($cd);
           $db->query("UPDATE users SET quest=".$coud['qID'].",quest_time=".$coud['qStart'].", quest_step=1 WHERE userid=$userid");
                       print "".$coud['qStartText'].", it will take ".$coud['qStart']." minutes to finish this step.";
   } else {
       //list courses
       print "Here is a list of quests that are available to you.";
       if ($ir['user_level'] == 2) {
        $q = $db->query("SELECT * FROM questing ORDER BY qID ASC");
       } else {
       	$q = $db->query("SELECT * FROM questing WHERE qActive=1 ORDER BY qID ASC");
       }
       print "<br>
<table id='demo-table3' width=100% cellspacing=1 class='table'>
	<tr> 
		<th>Quest</th>
		<th>Description</th>
		<th>Item Needed</th>
		<th>Start</th>";

			if ($ir['user_level']==2) {
				print" 
				<th>Active</th>
				<th>Edit</th>";
			}
		print "</tr>";
       while ($r = $db->fetch_row($q)) {
           $cdo = $db->query("SELECT * FROM quest_done WHERE userid=$userid AND questid={$r['qID']}");
           if ($db->num_rows($cdo) == 5) {
               // 5 allowed per day
               $do = "<i>Done</i>";
           } else {
               $do = "<a href='questing.php?qstart={$r['qID']}'>Take</a>";
           }
           $cd   = $db->query("SELECT * FROM questing");
           $coud = $db->fetch_row($cd);
           $n    = $db->query("SELECT itmname FROM items WHERE itmid={$coud['qItem']}");
           $need = $db->fetch_row($n);
           print "
<tr align=center>
	<td width=20%>".$r['qName']."</td>
	<td>".$r['qDescription']."</td>
	<td width=15%>".$need['itmname']."</td>
	<td width=10%>$do</td>";
	if ($ir['user_level']==2) {
		print"
		<td width=7%>";
			if ($coud['qActive'] == 1) {
				print"<font color=green>Yes</font>";
			} else {
				print"<font color=red>No</font>";
			}
		print"</td>


		<td width=7%><a href='staff_quest.php?action=editquest'>Edit</a></td>";
	}
print"</tr>";
       }
       print "</table>";
   }
}

[G7470]

It looks like you are referencing the incorrect variable in order to determine whether it is "active" or not.

You should be referencing $r['qActive'] instead of $coud['qActive'] on line 61.

Also, I'm pretty sure you can do some clean-up here too. Here's the new block of code with some clean-up:

 

if ($_GET['qstart']) {
       $_GET['qstart'] = abs((int) $_GET['qstart']);
       //Verify.
       $cd         = $db->query("SELECT * FROM questing WHERE qID={$_GET['qstart']}");
       $coud       = $db->fetch_row($cd);
       $item       = $db->query("SELECT * FROM inventory WHERE inv_itemid={$coud['qItem']}");
       // $f          = $db->fetch_row($item);
       if ($db->num_rows($cd) == 0) {
           showErrMsg('You are trying to start a nonexistent quest!');
       }
       if ($db->num_rows($item) < 1) {
           showErrMsg('You don\'t have the '.$coud["qItem"].'. You must get one before starting this quest.');
       }
           $cd   = $db->query("SELECT * FROM questing WHERE qID={$_GET['qstart']}");
           $coud = $db->fetch_row($cd);
           $db->query("UPDATE users SET quest=".$coud['qID'].",quest_time=".$coud['qStart'].", quest_step=1 WHERE userid=$userid");
                       print "".$coud['qStartText'].", it will take ".$coud['qStart']." minutes to finish this step.";
   } else {
       //list courses
       print "Here is a list of quests that are available to you.";
       if ($ir['user_level'] == 2) {
           $q = $db->query("SELECT * FROM questing ORDER BY qID ASC");
       } else {
           $q = $db->query("SELECT * FROM questing WHERE qActive=1 ORDER BY qID ASC");
       }
       print "
   <table id='demo-table3' width=100% cellspacing=1 class='table'>
       <tr>
           <th>Quest</th>
           <th>Description</th>
           <th>Item Needed</th>
           <th>Start</th>";

               if ($ir['user_level']==2) {
                   print"
                   <th>Active</th>
                   <th>Edit</th>";
               }
           print "</tr>";
       while ($r = $db->fetch_row($q)) {
           $cdo = $db->query("SELECT * FROM quest_done WHERE userid=$userid AND questid={$r['qID']}");
           if ($db->num_rows($cdo) == 5) {
               // 5 allowed per day
               $do = "<i>Done</i>";
           } else {
               $do = "<a href='questing.php?qstart={$r['qID']}'>Take</a>";
           }
           // $cd   = $db->query("SELECT * FROM questing");
           // $coud = $db->fetch_row($cd);
           $n    = $db->query("SELECT itmname FROM items WHERE itmid={$r['qItem']}");
           $need = $db->fetch_row($n);
           print "
   <tr align=center>
       <td width=20%>".$r['qName']."</td>
       <td>".$r['qDescription']."</td>
       <td width=15%>".$need['itmname']."</td>
       <td width=10%>$do</td>";
       if ($ir['user_level']==2) {
           print"
           <td width=7%>";
               if ($r['qActive'] == 1) {
                   print"<font color=green>Yes</font>";
               } else {
                   print"<font color=red>No</font>";
               }
           print"</td>


           <td width=7%><a href='staff_quest.php?action=editquest'>Edit</a></td>";
       }
   print"</tr>";
       }
       print "</table>";
   }
}

 

Notes:

1. Commented out fetching the row of $item on line 7. I'm not too sure why it's there really since it's not being referenced anywhere.
   
2. Commented out lines 48 and 49. This is part of the problem area because this query will only grab the first row.
   
3. Line 51, changed $coud['qItem'] to $r['qItem'] since it is existing already.
   
4. Line 61, changed $coud['qActive'] to $r['qActive'], which was the problem area because again, $coud can only retrieve one row, which will be the first row.
   

 

So in conclusion, your while loop starting on line 41 loops through all of the quests, so you do not need to re-query that same table again to gather more information about the quest when you already have it through the $r data row.

Hope this helps. :)

~G7470

[Damond]

[MENTION=70485]G7470[/MENTION] Thanks for the quick response. That worked perfectly. I'm still trying to get a handle on this PHP stuff but with people like you around I'm learning more and more.