Jump to content

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


timcclayton

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.