Newest Pilots Help

[Jimmy_S]

Can I list my Newest Pilots by date instead of their number? My current pilot list jumps around quite a bit and instead of listing the newest pilots it is listing the last two pilot ID's.

[Nuclear]

How are you currently getting the recent pilots? The default template retrieves the records by descending pilotid. Since pilotid is an auto-increment field, it should return the latest pilots. You certainly can list them by date, but I'd have to see how you're retrieving the records first.

[Jimmy_S]

How are you currently getting the recent pilots? The default template retrieves the records by descending pilotid. Since pilotid is an auto-increment field, it should return the latest pilots. You certainly can list them by date, but I'd have to see how you're retrieving the records first.

You are right I am using the the default template and the records are by descending pilotid. Reason that is not working for me is we had a server crash and rebuilt using phpVMS. Some of the pilots that returned wanted their old ID and I gave it to them. Now when we sign up new pilots they are being assigned ID's that are between current pilots. Example I have 005 and several in the three hundreds. So the new pilots are assigned PID in between and only the last few PID's show up, they are current pilots not new ones. Thanks!

[Nuclear]

Well, one way to do it would be this:

Make a copy of the frontpage_main.tpl from /core/templates/

Edit the new copy and replace this:

<?php MainController::Run('Pilots', 'RecentFrontPage', 5); ?>

With this:

<?php
$query='SELECT * FROM phpvms_pilots ORDER BY joindate DESC LIMIT 5';
$pilots=DB::get_results($query);
foreach($pilots as $pilot)
{
	echo '<a href='.SITE_URL.'/index.php/profile/view/'.$pilot->pilotid.'>';
	echo PilotData::getPilotCode($pilot->code,$pilot->pilotid).' '.$pilot->firstname.' '.$pilot->lastname.'</a><br><br>';
}?>

Now place the edited file into your skins directory (/lib/skins/crystal/ if you're using the default crystal skin).

That will show the 5 most recent pilots sorted by date starting with the most recent.

[Jimmy_S]

Thanks so much Michael!

[UAL3626]

I'm running into the same problem. What do I need to do in order to not show the duplicates. I have removed some of the original code and still get errors. This is the code I have now with yours that works but shows the duplicates.

<?php

foreach($pilots as $pilot)

{

if($pilot->confirmed != 1)

continue;

?>

<p><a href="<?php echo url('/profile/view/'.$pilot->pilotid);?>"><?php echo

PilotData::GetPilotCode($pilot->code, $pilot->pilotid). ' ' .$pilot->firstname . ' ' .

$pilot->lastname?></a></p>

<?php

}

?>

<?php

$query='SELECT * FROM phpvms_pilots ORDER BY joindate DESC LIMIT 5';

$pilots=DB::get_results($query);

foreach($pilots as $pilot)

{

echo '<a href='.SITE_URL.'/index.php/profile/view/'.$pilot->pilotid.'>';

echo PilotData::getPilotCode($pilot->code,$pilot->pilotid).' '.$pilot-

>firstname.' '.$pilot->lastname.'</a><br><br>';

}?>

[Nuclear]

Your welcome, Jimmy. Glad you got it working.

[Nuclear]

I'm running into the same problem. What do I need to do in order to not show the duplicates. I have removed some of the original code and still get errors. This is the code I have now with yours that works but shows the duplicates.

<?php

foreach($pilots as $pilot)

{

if($pilot->confirmed != 1)

continue;

?>

<p><a href="<?php echo url('/profile/view/'.$pilot->pilotid);?>"><?php echo

PilotData::GetPilotCode($pilot->code, $pilot->pilotid). ' ' .$pilot->firstname . ' ' .

$pilot->lastname?></a></p>

<?php

}

?>

<?php

$query='SELECT * FROM phpvms_pilots ORDER BY joindate DESC LIMIT 5';

$pilots=DB::get_results($query);

foreach($pilots as $pilot)

{

echo '<a href='.SITE_URL.'/index.php/profile/view/'.$pilot->pilotid.'>';

echo PilotData::getPilotCode($pilot->code,$pilot->pilotid).' '.$pilot-

>firstname.' '.$pilot->lastname.'</a><br><br>';

}?>

You should remove this part:

<?php

foreach($pilots as $pilot)
{
if($pilot->confirmed != 1)
continue;
?>

<p><a href="<?php echo url('/profile/view/'.$pilot->pilotid);?>"><?php echo

PilotData::GetPilotCode($pilot->code, $pilot->pilotid). ' ' .$pilot->firstname . ' ' .

$pilot->lastname?></a></p>
<?php
}
?>

If you only want to show pilots who have been confirmed, change this:

$query='SELECT * FROM phpvms_pilots ORDER BY joindate DESC LIMIT 5';

To this:

$query='SELECT * FROM phpvms_pilots WHERE confirmed=1 ORDER BY joindate DESC LIMIT 5';

I should have also noted - if you don't want to show the newest 5 pilots, you can change 'LIMIT 5' to however many pilots you want to display.

[Nabeel]

If you only want to show pilots who have been confirmed, change this:

$query='SELECT * FROM phpvms_pilots ORDER BY joindate DESC LIMIT 5';

To this:

$query='SELECT * FROM phpvms_pilots WHERE confirmed=1 ORDER BY joindate DESC LIMIT 5';

I should have also noted - if you don't want to show the newest 5 pilots, you can change 'LIMIT 5' to however many pilots you want to display.

Don't use raw SQL, use the API for these queries:

$pilots = PilotData::getLatestPilots (10); // gets the last 10 pilots

See my sig for API listings.

[Nuclear]

Nabeel, is of course right, but I do have a question:

Don't use raw SQL, use the API for these queries:

I understand the API exists to deal with most everything. but what's the problem with using raw SQL? Beside the fact that it's raw SQL? I do get it, but I find it much easier to use raw SQL (assuming that there isn't an API call for it...)

[Nabeel]

Nabeel, is of course right, but I do have a question:

I understand the API exists to deal with most everything. but what's the problem with using raw SQL? Beside the fact that it's raw SQL? I do get it, but I find it much easier to use raw SQL (assuming that there isn't an API call for it...)

If there isn't an API call, then you can use SQL, but do let me know so I can add it in. But for calls where an API method can be used, it's best to use it, since it's the safest way for when there are changes in the database or how things work, they'll be invisible to you. For instance, there have been some changes to financials between the last two versions and this next one, so using raw SQL might just stop working. In 2.1 there were huge changes in how the basic queries were done and organized, and a few columns were removed (like flighttime going to flighttime_stamp), you wouldn't notice.

The other reason is the API implements some level of caching, so that database heavy queries (especially ones having to do with statistics, which can potentially go through thousands of records), are cached for a sensible amount of time, so they don't unnecessarily tax your server. So unless you implement those cache read/writes yourself (which isn't difficult to do), then you're missing out on those optimizations.