Jump to content

Archived

This topic is now archived and is closed to further replies.

timcclayton

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

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.