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:

[code]<?
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");
?>  [/code]

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.

[toplay]

You haven't done a fetch after your hours query!

[timcclayton]

Can you give me an idea of the syntax of the "fetch" line in the context of the above code, as all I can find is "fetch array", and I only want to get one field of data (or do I have to fetch the whole row and then specify which data field I want?) ?

Thanks again,
Tim.

[toplay]

There's no need for so many updates. Study this:

[code]
<?php

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());

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

$row = mysql_fetch_assoc($result);

if (!$row) {
    // No data matched search criteria, give error
    echo 'Could not find pilotid: ', $pilot;
    exit;
} else {
    $totalhours = $row['hours'] + $duration; // Duration set somewhere
}

if ($totalhours > 10) {
    $rank = 'Commercial Pilot';
} elseif ($totalhours >= 5) {
    $rank = 'Private Pilot';
} else {  // Default to something
    $rank = 'Student Pilot';
}

// Update row just once
$result = mysql_query("UPDATE PILOTS SET pireps=pireps+1, ranking='$rank', hours=hours+'$duration', lastflightdate=current_date() WHERE pilotid = '$pilot'");

if (!$result) {
    echo 'Could not update. Error: ', mysql_error();
    exit;   
}

mysql_close();

header('Location: /pireps.php');
exit;
?>
[/code]

[timcclayton]

Thank you so much [color=red]toplay [/color]- that has cleared a number of issues up for me. I didn't know for example that you could update multiple fields in one go. I also assumed the SELECT would get my field data - didn't realise that you needed the FETCH command to actually produce the data. It really streamlines the script as well.

Thanks again - [b]very [/b]much appreciated,
Tim.