record is deleted whether or not condition is met

[webguync]

I have a SQL query that deletes a record in a log table based upon the login criteria. This part works good, but I have the query set up in a conditional if statement and the delete is occurring if the condition is met and if it not met. I have the conditional set up to display other info based upon whether or not it is met, so the conditional other than triggering the delete seems to work ok.

Here is the code involved.

<?php
   if(($pcnt[$i]*100) > 79)
   {
      echo "congrats, you passed!";
   }
   else
   {
     $query_delete = "DELETE FROM test_log_April2010
USING test_log_April2010 INNER JOIN test_roster_April2010
WHERE test_log_April2010.user_id = test_roster_April2010.user_id
AND test_roster_April2010.user_id = '{$_SESSION['user_id']}'"; 
//echo $query_delete; //for debugging test
$result_delete = mysql_query($query_delete) or trigger_error('Query failed: ' .mysql_error());
$num = mysql_affected_rows($con);
//echo "Affected rows: $num.\r\n";
if ($result_delete)
{
echo "Delete Successful";
}// end if
else
{
echo "No record of taking exam";
} //end else
   }
?>

 

 

if I need to post the entire code on the page let me know. It's quite a bit of code though.

[de.monkeyz]

Is that the exact code?

 

* snip *

 

Sorry I didn't read it correctly.

 

I suggested debugging the conditions. There's no way the else will run if the condition is working

[Psycho]

Validate that the values used in your IF condition are what you expect them to be. I have yet to ever see an IF condition make the wrong decision - it is always a failure of the values or the condition specified:

 

Try adding this before the IF condition for debugging purposes

echo '$pcnt[$i] = ' . $pcnt[$i] . '<br />';
echo '(($pcnt[$i]*100) > 79) = ' . ((($pcnt[$i]*100) > 79) ? 'True' : 'False');

 

Is that the exact code?

 

if(($pcnt[$i]*100) > 79)

 

There's a missing ) in that line

You want to take that back?

[de.monkeyz]

Yeah I took it back, long day ^^

 

I suggest he debugs it like yourself. Also I'd suggest he checks further down in his code to see if he has another query running

[Pikachu2000]

Do your calculation outside of the conditional check and store it in a variable, echo the variable to verify it contains the value you'd expect, then use it in the conditional.

 

$var = $pcnt[$i] * 100;

echo $var;

if($var > 79) { etc., etc...

[webguync]

debugging when a score is greater than 79 produces

$pcnt[$i] = 1
(($pcnt[$i]*100) > 79) = True

 

so, it seems the conditional is working but the else query_delete is still executing? No queries are running further down the page.

 

also, I don't see where this line is missing a ), maybe I misunderstood that part.

 

if(($pcnt[$i]*100) > 79)

[Psycho]

If that DELETE query is running (inside the ELSE condition) then you should also be seeing one of the two echo statments

if ($result_delete)
{
    echo "Delete Successful";
}// end if
else
{
    echo "No record of taking exam";
} //end else

 

Which one is being displayed?

[webguync]

well in this block

<?php
//echo '$pcnt[$i] = ' . $pcnt[$i] . '<br />';
//echo '(($pcnt[$i]*100) > 79) = ' . ((($pcnt[$i]*100) > 79) ? 'True' : 'False');
   if(($pcnt[$i]*100) > 79)
   {
      echo "I AM NOT SUPPOSED TO BE DELETING!";
   }
   else
   {
     $query_delete = "DELETE FROM test_log_April2010
USING test_log_April2010 INNER JOIN test_roster_April2010
WHERE test_log_April2010.user_id = test_roster_April2010.user_id
AND test_roster_April2010.user_id = '{$_SESSION['user_id']}'"; 
//echo $query_delete; //for debugging test
$result_delete = mysql_query($query_delete) or trigger_error('Query failed: ' .mysql_error());
$num = mysql_affected_rows($con);
//echo "Affected rows: $num.\r\n";
if ($result_delete)
{
echo "exam ready for retake";
}// end if
else
{
echo "No record of taking exam";
} //end else
   }
?>

 

just tried again and the score is greater than 79 which displays 'I AM NOT SUPPOSED TO BE DELETING", with the code above but I can look at the database log and see that it is fact deleting so my code is lying or I am doing something wrong ;-)

[Psycho]

Again, if the ELSE condition is running you would be seeing one of those two echo conditions. If not, then that ELSE condition is not running and the DELETE is taking place somewhere else. I've been in this type of situation before and the answer always turns out to be something logical that I overlooked.

 

If you are not seeing one of those two echo statements, post the entire page (attach to the post if it is very long).

[webguync]

No the last two echo statements are not being displayed. I am sure it is probably something I am overlooking, anyway here is all of the code on the page. To briefly describe what is happening is this is the secured area of a login form stored in a user table which authenticates the info and displays the results of an exam application. The exam application is a flash based app which creates a log file in MySQL. This log file is already created when they take the exam and is totally independent of this login, however I want the delete to occur if the <79 condition is met, which is happening, but as we have discussed is also happening when the score is > 79.

 

 

<?php
//ini_set("display_errors","1");
//ERROR_REPORTING(E_ALL);
session_start();

$con = mysql_connect("localhost","nestle","nutrition") or die('Could not connect: ' . mysql_error());

mysql_select_db("nestle_exam") or die(mysql_error());




// Same checking stuff all over again.
if(isset($_POST['submit'])) {
   if(empty($_POST['username']) || empty($_POST['pwid']) ) {
     echo "<h2 class='fail'>Please fill in both your username and password to access your exam results.<br /><br >You will be redirected back to the login screen in five seconds.</h2>";
  echo "<meta http-equiv='refresh' content='5; url=StudentLogin.php'>";
                exit;
   }
   // Create the variables again.
   
   $username = mysql_real_escape_string($_POST['username']);
   $pwid = $_POST['pwid'];

   // Encrypt the password again with the md5 hash. 
   // This way the password is now the same as the password inside the database.
   //$pwid = md5($pwid);

   // Store the SQL query inside a variable. 
   // ONLY the username you have filled in is retrieved from the database.
   $query = "SELECT username,pwid,name,user_id
           FROM   test_roster_April2010
           WHERE
           pwid = '$pwid'
           AND
           username='$username'";

   $result = mysql_query($query) or die(mysql_error());
   if(mysql_num_rows($result) == 0) { 
      // Gives an error if the username/pw given does not exist.
      // or if something else is wrong.
     echo "<h2 class='fail'>You have entered a username or password that does not match our database records. please try again.<br><br>You will be redirected back to the login screen in five seconds.</h2> " . mysql_error();
echo "<meta http-equiv='refresh' content='5; url=StudentLogin.php'>";
exit();

   } else {
     
      $row = mysql_fetch_object($result);
      

      // By storing data inside the $_SESSION superglobal,
      // you stay logged in until you close your browser.
$_SESSION['name'] = $row->name;
       $_SESSION['user_id']  = $row->user_id;
       $_SESSION['username'] = $username;
       $_SESSION['sid'] = session_id(); 
      // Make it more secure by storing the user's IP address.
       $_SESSION['ip'] = $_SERVER['REMOTE_ADDR'];
      // Now give the success message.
      $user_id = $_SESSION['user_id']; 
      // print_r($_SESSION);




$dat = time() + 3600;
$query = "UPDATE test_roster_April2010
          SET login_timestamp = DATE_ADD(NOW(), INTERVAL 3 HOUR)
          WHERE username = '$username'
           AND pwid = '$pwid' ";
//echo $query; //for debugging test 
$result = mysql_query($query) or die(mysql_error()); 

//Check if query ran successfully     
   }
}

// Start a session. If not logged in will be redirected back to login screen.

if(!isset($_SESSION['username'])){
header("Location:StudentLogin.php");
exit;
}
echo "<table id='header'><tr><td><img src='Caris-Life-Sciences-Logo_small.png' /></td><td align='middle'><div id='welcome'><h3>Welcome! You are now logged in " . $_SESSION['name'] . "</h3></td></tr>";

echo "<tr><td><a class='logout' href='LogoutStudent.php'>Logout</a></td></tr></table>";

?>
<?php
require_once('../protected/databaseClass.php');

$db = new Database('localhost','nestle','nutrition','nestle_exam',0);




$sql = "SELECT test_roster_April2010.name, test_results_April2010.total_questions, test_results_April2010.responses,test_results_April2010.incorrect_resp, test_results_April2010.num_correct,test_results_April2010.WorkonAreas,test_results_April2010.date
	FROM test_roster_April2010 LEFT JOIN test_results_April2010 USING (user_id)
	WHERE test_results_April2010.user_id = test_results_April2010.user_id  AND test_roster_April2010.username='$username' 
	ORDER BY name, date";


$report = $db->query($sql);

if ($report->get_rows()) {
//loop to create arrays for each column
while ($row = $report->fetch_assoc()) {
	if($row['num_correct']) {
		$name[] = $row['name'];
                        $responses[] = $row['responses'];
		$numCorr[] = $row['num_correct'];
		$pcnt[] = ($row['num_correct'])/($row['total_questions']);
		$incorr[] = $row['incorrect_resp'];

		$date[] = $row['date'];
		$workon[] = $row['WorkonAreas'];
		}
	}


}

?>


<body class="results">

<div>

<h1>Caris Validation Exam #2 - April 2010</h1>



<table id="resultlist">
   
	<tr>
		<th scope="col">Employee Name</th>
                        
		<th scope="col">Number Correct</th>
		<th scope="col">Score</th>
		<th scope="col">Question Number Answered Incorrectly</th>
		<th scope="col">Date Completed</th>
                       
                       <th scope="col">Pass/Fail</th>
		<th scope="col">Material to review in specific Primer(Chapter,Section) or Articulate Program</th>
	</tr>

	<?php if (!isset($name)) { ?>
	<tr><td colspan="7">There are no scores to display</td></tr>
	<?php
	} else {
	for ($i=0; $i<count($name); $i++) { ?>
	<tr class="<?php echo $i%2 ? 'hilite' : 'nohilite'; ?>">
		<td ><?php echo $name[$i];?></td>
		<td><?php echo $numCorr[$i];?></td>
            

		<td><?php echo (ROUND(($pcnt[$i]*100),0).'%'); ?></td>
		<td><?php echo $incorr[$i];?></td>
		<td><?php echo (date('F j, Y  g:i A',($date[$i])));?></td>
          <td><?php
   if(($pcnt[$i]*100) > 79)
   {
      echo "<div class='passed'>" .Passed."</div>";
   }
   else
   {
      echo "<div class='failed'>" .Failed. "</div>";
   }
?></td>
<td><?php
//echo '$pcnt[$i] = ' . $pcnt[$i] . '<br />';
//echo '(($pcnt[$i]*100) > 79) = ' . ((($pcnt[$i]*100) > 79) ? 'True' : 'False');
   if(($pcnt[$i]*100) > 79)
   {
      echo "I am not supposed to delete";
   }
   else
   {
     $query_delete = "DELETE FROM test_log_April2010
USING test_log_April2010 INNER JOIN test_roster_April2010
WHERE test_log_April2010.user_id = test_roster_April2010.user_id
AND test_roster_April2010.user_id = '{$_SESSION['user_id']}'"; 
//echo $query_delete; //for debugging test
$result_delete = mysql_query($query_delete) or trigger_error('Query failed: ' .mysql_error());
$num = mysql_affected_rows($con);
//echo "Affected rows: $num.\r\n";
if ($result_delete)
{
echo "exam ready for retake";
}// end if
else
{
echo "No record of taking exam";
} //end else
   }
?></td>


<td class="wo"><?php echo $workon[$i];?></td>
<td></td>
</tr>
	<?php }
	} ?>

</table>

</div>


<?php if (!isset($name)) { ?>
<tr><td><p><strong>We don't have a record of you taking this exam</strong></p></td></tr>
<?php
} else {
?>



<?php } ?>

[Psycho]

Looking through that code there is a lot of in and out of PHP code that makes the whole page difficult to follow. Plus, it seems that page is doing an authentication. You should have a separate authenticatiuon script that is called on the pages that require it. Lastly, you are doing your DB queries two different ways, using the procedural

$result = mysql_query($query) or die(mysql_error());

 

And using the object oriented approach

$report = $db->query($sql);

That makes no sense to me.

 

Basically, the code is somewhat disorganized which is probably a contributing factor.

 

Anyway, here is another debugging approach. Create a function to "test" for the exisitance of the record that would be deleted. Then call that function at different intervals during the script. Then you can identify where in the process the delete is taking place. I just threw this together, so it may need some work.

 

Add this to the top of your page:

function testRecords($lineNo)
{
    $output = "{$lineNo}: ";
    if(!isset($_SESSION['user_id']))
    {
        $output .= '$_SESSION[\'user_id\'] does not exist';
    }
    else
    {
        $query = "SELECT user_id
                  FROM test_log_April2010
                  USING test_log_April2010 INNER JOIN test_roster_April2010
                  WHERE test_log_April2010.user_id = test_roster_April2010.user_id
                    AND test_roster_April2010.user_id = '{$_SESSION['user_id']}'";
        $result = mysql_query($query);
        $output .= '$_SESSION[\'user_id\'] = ' . $_SESSION['user_id'];
        $output .= ' : Records = ' . mysql_num_rows($reslt);
    }
    return "$output<br />\n";
}

 

Then at different point in the script add the following line:

$debugOutput .= testRecords(__LINE__);

 

Finally at the end of the script, echo the results of the debugging:

echo $debugOutput;

[webguync]

the result of the echo $debugoutput is...

131: $_SESSION['user_id'] = 1 : Records = 

 

I guess something is supposed to be output for Records...

[de.monkeyz]

This might be a really REALLY stupid question. But are you sure the row is EVER there? It may look deleted, but it could just be that it's never inserted?

[webguync]

no, it's there, and then not there. I have been checking before and after I login to execute the script.

[Psycho]

I had a typo on this line (surprised it didn't create an error).

$output .= ' : Records = ' . mysql_num_rows($reslt);

 

Try fixing the variable name on that line

[webguync]

yea, I did fix that line before I tried it. I caught that :-)

 

What is supposed to be in Records =?