PHP, MySQL and checking the condition of a field's data

timcclayton

Hello all.

I am a beginner when it comes to PHP and MySQL but am learning fast. I have a database with 2 tables, one table has a user's information in it ("PILOTS" table) and the other has their filed flight reports ("PIREPS" table). I need to check that when they file a flight report, whether their total hours of flying (i.e. all their total flight reports) has hit a certain mark. This will then change the data in a field in the other table ("PILOTS") to show their progress. The PHP script for filing a flight plan is:

<?
include("dbinfo.inc.php");
mysql_connect("127.0.0.1",$username,$password);
@mysql_select_db($database) or die( "Unable to select database"); 

mysql_query("INSERT INTO PIREPS (pilot, flightno, aircraft, start, end, duration) VALUES('$pilot','$flightno','$aircraft','$start','$end','$duration' ) ") or die(mysql_error());  

mysql_query("UPDATE PILOTS SET pireps=pireps+1 WHERE pilotid = '$pilot'");

mysql_query("UPDATE PILOTS SET hours=hours+'$duration' WHERE pilotid = '$pilot'");

$totalhours = mysql_query("SELECT hours FROM PILOTS WHERE pilotid = '$pilot'") or die(mysql_error());

if ($totalhours > '10') {
    mysql_query("UPDATE PILOTS SET ranking = \"Commercial Pilot\" WHERE pilotid='$pilot'");
} elseif ($totalhours >= '5') {
    mysql_query("UPDATE PILOTS SET ranking = \"Private Pilot\" WHERE pilotid='$pilot'");
} elseif ($totalhours < '5') {
    mysql_query("UPDATE PILOTS SET ranking = \"Student Pilot\" WHERE pilotid='$pilot'");
}


mysql_query("UPDATE PILOTS SET lastflightdate=current_date() WHERE pilotid = '$pilot'");

mysql_close();

header("location: /pireps.php");
?>

So, for example if, after filing this latest flight plan, their total hours hits 5, they progress from being a "Student Pilot" to being a "Private Pilot" and so on. I just can't get the IF statements to change their ranking.

Does anyone know what I am doing wrong?

Thanks in advance for your help,
Tim.

Houdini

Where are all the variables for insertion or updating the databse coming from? Certainly they are not within the dbinfo.inc.php so how can you query with nonexistant variables. Namly $pilot

timcclayton

The variables come from a form in the previous PHP script that calls this one.

Houdini

What happen if you echo $pilot? Do you set a result, it look like you are depending on register_globals being turned on (they should not be after PHP 4.2.0) and that the sever allow 'short tags' also not a good coding practice.
Try echoing out he $pilot variable and see if it contains what you think it ought to like

include("dbinfo.inc.php");
echo "\$pliot = $pilot":
echo "The Pilot variable is {$_POST['pilot']}";
mysql_connect("127.0.0.1",$username,$password);// You could use localhost vs the ip, that is up to you