Jump to content


Photo

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


  • Please log in to reply
4 replies to this topic

#1 timcclayton

timcclayton
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 08 July 2006 - 05:28 PM

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.

#2 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 08 July 2006 - 05:43 PM

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

#3 timcclayton

timcclayton
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 08 July 2006 - 05:53 PM

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.

#4 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 08 July 2006 - 06:08 PM

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

<?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;
?>


#5 timcclayton

timcclayton
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 08 July 2006 - 06:59 PM

Thank you so much toplay - 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 - very much appreciated,
Tim.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users