[Solved]Mysql Query inside template file

[skylineVirtual]

Hello

I'm trying to add whether a pilot is flying online on Vatsim or IVAO to the acarsdata table. To figure out if he is logged into a network I'm reading out the whazzup files with this code inside the acarsmap.tpl

To download the txt files:

$cws = new CodonWebService();
    $ivaowhazz = $cws->get('http://de1.www.ivao.aero/whazzup.txt');

    $cws = new CodonWebService();
    $vatwhazz = $cws->get('http://www.pcflyer.net/DataFeed/vatsim-data.txt');

Then to check if this file contains the right callsign, I'm using this code (in the acarsmap.tpl as well and working as it shood):

if (strpos($ivaowhazz,'<%=flight.flightnum%>') !== false) {

echo '<div style="align: center"><img style="height: 18px;" src="http://www.skyline-va.de/phpvms/core/lib/skins/crystal/images/ivao.jpg"></div>';
}

else if (strpos($vatwhazz,'<%=flight.flightnum%>') !== false) {
echo '<div style="align: center"><img style="height: 18px;" src="http://www.skyline-va.de/phpvms/lib/skins/crystal/images/vatsim.png"></div>';

}
else {
echo "Offline";};

Now I'd like to insert this data into the phpvms_acarsdata table. I've added an extra column which is titled network but I couldn't figure out a code how to change the value of that column according to which network the pilot's currently flying on. I've tried several things like:

$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // Database name
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$mysqlnet = "UPDATE phpvms_acarsdata SET network = '2' WHERE flightnum='<%=flight.flightnum%>'";
$a1 = mysql_query($mysqlnet);

or I tried to use stuff that phpvms already uses like this:

$mysqlnet = "UPDATE phpvms_acarsdata SET network = '1' WHERE flightnum='<%=flight.flightnum%>'";
DB::query($mysqlnet);

But nothing worked yet. I didn't really understand the phpvms framework logic yet as well, which is probably the reason for my error. Is there any documentation for it?

Thanks in advance!

-Lauritz

[freshJet]

EDIT: Nope, that's nonsense.

I think it's the use of <%=flight.flightnum%>, I think it should be in PHP format. Could be wrong though.

[Vangelis]

Hello

I would suggest you to do a little search in the forum how a module is created.

You will avoid a lot of hassle and you wont need to open a connection again

Also per rules of ivao and vatsim you must select a random server and not 1specific that could ban your site from downloading whazzup

As i am not in front of a pc at the moment i can look if i can make you something .

[skylineVirtual]

Ok, thanks for your answer. I'm going to have a look around the Forum to see if I can get the problem solved.

I've already looked around the phpvms files a bit. Is it possible to add a query to the acarsmap part of the ACARS module which first get's all the entries in the acarsdata table, then checks whether the pilot is flying online or not and enters this back into the table?

Then the second question would be how I can work with the variables I defined in the modules file in the tpl file. Because there is no need to download the TXT files twice if I can use those from the modules file.

As for the IVAO and VATSIM data thanks for the information. I'm going to change that as well.

Thanks in advance!

-Lauritz

[Vangelis]

The logic of the framework is you make a module and this module renders a tpl file

Also that means that in tpl files there is no need to execute any sql queries , that is done throught the module or class that you have done.

I do not know if what i am saying makes sense but if you need anything tell me

[skylineVirtual]

I'm moving forward I started to understand the logic and made some first tries. This code seems to do the job except for the queries. They aren't working yet.

  $results = ACARSData::GetACARSData();

  foreach($results as $result)
  {
  if (strpos($ivaowhazz,$result->flightnum) !== false)
  {
  $sql = 'UPDATE '.TABLE_PREFIX."acarsdata SET network='1' WHERE flightnum='".$result->flightnum."'";
  DB::query($query);
 }

  else if (strpos($vatwhazz,$result->flightnum) !== false)
  {
  $sql = 'UPDATE '.TABLE_PREFIX."acarsdata SET network='2'";
  DB::query($query);
  };
  };

Could someone maybe help me out on this?

Thanks in advance

[skylineVirtual]

Or can someone tell me which framework phpvms uses? This way I could look for some documentation on the Internet concerning mysql queries as I was unable to find something here in the forums.

[mseiwald]

Try that code and see if it works

  $results = ACARSData::GetACARSData();

  foreach($results as $result)
  {
  if (strpos($ivaowhazz,$result->flightnum) !== false)
  {
  $sql = "UPDATE ".TABLE_PREFIX."acarsdata SET network='1' WHERE flightnum='$result->flightnum'";
  DB::query($query);
 }

  elseif (strpos($vatwhazz,$result->flightnum) !== false)
  {
  $sql = "UPDATE ".TABLE_PREFIX."acarsdata SET network='2' WHERE flightnum='$result->flightnum'";
  DB::query($query);
  }
  }

[Vangelis]

Hi

As it seems an interest project i have created this function for you

save it as chkpilotonline.class.php

<?php
class chkpilotonline extends CodonModule {

	 public function search_pilot_online($callsign) {

		 //get new file from Vatsim if the existing one is more than 5 minutes old
 if(!file_exists('vatsimdata.txt') || time()-filemtime('vatsimdata.txt') > 300) {
 //choose a random download location per Vatsim policy
	 $random = (rand(1, 2));
	 if ($random == 1) {$url = 'http://fsproshop.com/servinfo/vatsim-data.txt';}
	 if ($random == 2) {$url = 'http://info.vroute.net/vatsim-data.txt';}
	 //get the file
	 $file = new CodonWebService();
	 $contents = @$file->get($url);
	 //save new file to server
	 $newfile="vatsimdata.txt";
	 $file = fopen ($newfile, "w");
	 fwrite($file, $contents);
	 fclose ($file);
 }
		 //get new file from Ivao if the existing one is more than 5 minutes old
 if(!file_exists('ivaodata.txt') || time()-filemtime('ivaodata.txt') > 300) {
 //choose a random download location per Ivao policy
	 $random = (rand(1, 2));
	 if ($random == 1) {$url = 'http://de1.www.ivao.aero/whazzup.txt';}
	 if ($random == 2) {$url = 'http://eu20.ivan.ivao.aero/whazzup.txt';}
	 //get the file
	 $file = new CodonWebService();
	 $contents = @$file->get($url);
	 //save new file to server
	 $newfile="ivaodata.txt";
	 $file = fopen ($newfile, "w");
	 fwrite($file, $contents);
	 fclose ($file);
 }
 $contents_Vatsim = file_get_contents('vatsimdata.txt');
 $contents_Ivao = file_get_contents('ivaodata.txt');
	 if (strpos($contents_Vatsim,$callsign) !== false) {

$sql = 'UPDATE '.TABLE_PREFIX.'acarsdata
 SET network =1
 WHERE pilotid='.$callsign ;
 DB::query($sql);
	 echo $callsign ;
echo '<div style="align: center"><img style="height: 18px;" src="http://vatoce.net/newsite/files/3713/3795/8721/vatsim_logo.png"></div>';
}
else if (strpos( $contents_Ivao,$callsign) !== false) {
 $sql = 'UPDATE '.TABLE_PREFIX.'acarsdata
 SET network =2
 WHERE pilotid='.$callsign ;
 DB::query($sql);
 echo $callsign ;
echo '<div style="align: center"><img style="height: 18px;" src="https://ivao.aero/images/logosmall.jpg"></div>';
}
else {
 $sql = 'UPDATE '.TABLE_PREFIX.'acarsdata
 SET network =0
 WHERE pilotid='.$callsign ;
 DB::query($sql);
echo "Offline";};

}
}

you can call it from anywhere with chkpilotonline ::chkpilotonline($callsign)

the problem now is that acars map uses java and in java you cannot run php functions and i am still looking how to do it

p.s this code uses some part of simpilots VatsimReader

[skylineVirtual]

Wow thanks.

Wouldn't it be possible to run this function in the ACARS.php module and then change the function that get's the acarsdata so that it will get the network variable from the table.

After you run it is the variable then available in the template like the other ones (in the format <=flight.network=> or how the format is, don't have it here right now)?

You could then check if that variable is 1 and display IVAO or 2 for Vatsim, 0 for offline.

[mseiwald]

Btw i would use their VATSIM / IVAO ID instead of the callsign.... especially for non fictional VA's or if your VA allows to Book the same flight multiple times.

Vangelis...yeah indeed you can add the network variable to the code in the ACARS.php file.

[skylineVirtual]

Using the Vatsim and IVAO ID sounds good. The thing is the Id's of our pilots are stored in a different database.

But using the flight number shouldn't be a problem because we are a fictional VA and have customized our phpvms so that a flight can only be booked once.

[Vangelis]

I have searched all the filles and i could'nt find where to add a new variable like <%=flight.flightnum%>

[Vangelis]

Wow thanks.

Wouldn't it be possible to run this function in the ACARS.php module and then change the function that get's the acarsdata so that it will get the network variable from the table.

After you run it is the variable then available in the template like the other ones (in the format <=flight.network=> or how the format is, don't have it here right now)?

You could then check if that variable is 1 and display IVAO or 2 for Vatsim, 0 for offline.

I wanted to ask you before why do you want to add 1,2,0 in the acars table ? because if you didnt know it it get's empty after some time so there is no point to store the online status by my opinion

[skylineVirtual]

I want to transfer this value into the pirep so that we can create online statistics or give rewards according to the online flights/hours.

I've already changed the code for that and I hope it works but I couldn't test it because the other stuff wasn't working so far. I'm going to try your code later as soon as I get home.

[Vangelis]

If it is for that it is easyer you will just put the class in the class folder and call the function when the pirep is filled

if you have any questions here whe are

[mseiwald]

Vangelis (sorry for the wrong name ) what exactly do you want to do with the acars map? Displaying the flights on the map? Thats not necessary since they already are in the acarsdata table. But if you want to show a online image in the table below the acarsmap next to the flight you can add all stuff you want if you go to acars.php and look for:

$c = (array) $flight; // Convert the object to an array

at line 72 or so...

below that you can add the code like for example:

if($flight->network== '1')
{
$c['onlineimage'] = "http://www.OFFLINEIMAGE.com/";
}
elseif($flight->network== '2)
{
$c['onlineimage'] = "http://www.VATSIMIMAGE.com/";
}
elseif($flight->network== '3')
{
$c['onlineimage'] = "http://www.IVAOIMAGE.com/";
}

and then on acarsmap.tpl add it to the table:

<td><img src="<%=flight.onlineimage%>"/></td>

[skylineVirtual]

My aim is to somehow get it to work that the online status is displayed in the map and then the value of acarsdata is changed so that when the pirep get's filed, the online status will be entered as well.

If the query part you wrote works, then the whole thing would probably work

[skylineVirtual]

Thanks to everyone Got the map part to work as it should.

Now I have to hope that the transfer of these values into the pirep will work.

The only thing I did so far was to edit this query (from the PIREPData.class.php; function is fileReport; around line 748):

$sql = "INSERT INTO ".TABLE_PREFIX."pireps(
 `pilotid`,
 `code`,
 `flightnum`,
 `depicao`,
 `arricao`,
 `route`,
 `route_details`,
 `distance`,
 `aircraft`,
 `flighttime`,
 `flighttime_stamp`,
 `landingrate`,
 `submitdate`,
 `accepted`,
 `log`,
 `load`,
 `fuelused`,
 `expenselist`,
 `source`,
 `exported`,
 `rawdata`,
 `network` //added
)
 VALUES ( {$pirepdata['pilotid']},
 '{$pirepdata['code']}',
 '{$pirepdata['flightnum']}',
 '{$pirepdata['depicao']}',
 '{$pirepdata['arricao']}',
 '{$pirepdata['route']}',
 '{$pirepdata['route_details']}',
 '{$pirepdata['distance']}',
 '{$pirepdata['aircraft']}',
 '{$pirepdata['flighttime']}',
 '{$flighttime_stamp}',
 '{$pirepdata['landingrate']}',
 NOW(),
 ".PIREP_PENDING.",
 '{$pirepdata['log']}',
 '{$pirepdata['load']}',
 '{$pirepdata['fuelused']}',
 '0',
 '{$pirepdata['source']}',
 {$pirepdata['exported']},
 '{$pirepdata['rawdata']}',
 '{$pirepdata['network']}' //added
 )";

Is this enough? Or are there other things to do for this to work?

EDIT: it didn't work

[mseiwald]

First you need to add a new function to the PirepData.class.php like that:

/ * Check If Flight was Online */

public static function GetOnlineFlightInfo($pirepdata)
{ $pilotinfo = PilotData::getPilotData($pirepdata['pilotid']);

$sql = "SELECT network FROM ".TABLE_PREFIX."acarsdata
WHERE pilotid='".$pilotinfo->pilotid."' ";
 $ret = DB::get_row($sql);
return $ret;
}

Then to the filePirep function add the following above the sql query.

$onlineflt = self::GetOnlineFlightInfo($pirepdata);
$network = $onlineflt->network;

and then the actual query like that:

$sql = "INSERT INTO ".TABLE_PREFIX."pireps(
`pilotid`,
`code`,
`flightnum`,
`depicao`,
`arricao`,
`route`,
`route_details`,
`distance`,
`aircraft`,
`flighttime`,
`flighttime_stamp`,
`landingrate`,
`submitdate`,
`accepted`,
`log`,
`load`,
`fuelused`,
`expenselist`,
`source`,
`exported`,
`rawdata`,
`network` //added
)
VALUES ( {$pirepdata['pilotid']},
'{$pirepdata['code']}',
'{$pirepdata['flightnum']}',
'{$pirepdata['depicao']}',
'{$pirepdata['arricao']}',
'{$pirepdata['route']}',
'{$pirepdata['route_details']}',
'{$pirepdata['distance']}',
'{$pirepdata['aircraft']}',
'{$pirepdata['flighttime']}',
'{$flighttime_stamp}',
'{$pirepdata['landingrate']}',
NOW(),
".PIREP_PENDING.",
'{$pirepdata['log']}',
'{$pirepdata['load']}',
'{$pirepdata['fuelused']}',
'0',
'{$pirepdata['source']}',
'{$pirepdata['exported']}',
'{$pirepdata['rawdata']}',
'$network' //added
)";

[skylineVirtual]

I changed that but now it looks like the pireps that we're sent after this change weren't reported. So they are not in our database...

[mseiwald]

Thats interesting... try that one: Your additional column in both tables (acarsdata and pireps) is named "network"?

$sql = "INSERT INTO ".TABLE_PREFIX."pireps(
`pilotid`,
`code`,
`flightnum`,
`depicao`,
`arricao`,
`route`,
`route_details`,
`distance`,
`aircraft`,
`flighttime`,
`flighttime_stamp`,
`landingrate`,
`submitdate`,
`accepted`,
`log`,
`load`,
`fuelused`,
`expenselist`,
`source`,
`exported`,
`rawdata`,
`network`
)
VALUES ( {$pirepdata['pilotid']},
'{$pirepdata['code']}',
'{$pirepdata['flightnum']}',
'{$pirepdata['depicao']}',
'{$pirepdata['arricao']}',
'{$pirepdata['route']}',
'{$pirepdata['route_details']}',
'{$pirepdata['distance']}',
'{$pirepdata['aircraft']}',
'{$pirepdata['flighttime']}',
'{$flighttime_stamp}',
'{$pirepdata['landingrate']}',
NOW(),
".PIREP_PENDING.",
'{$pirepdata['log']}',
'{$pirepdata['load']}',
'{$pirepdata['fuelused']}',
'0',
'{$pirepdata['source']}',
{$pirepdata['exported']},
'{$pirepdata['rawdata']}',
'{$network}'
)";

[skylineVirtual]

Thanks, I'm going to try that one.

Is there any way to test those functions instead of risking even more lost pireps?

[skylineVirtual]

Is there no way to test the functions in advance?

[Vangelis]

just conect to vatsim and whait 5 minutes after that send a manual pirep, i dont think there is another way

[skylineVirtual]

Thanks but I think I found my error.

The

//added

comment shouldn't be there

EDIT: Everything is working now

[Vangelis]

Congratulations wana share the code for further referance ?

[skylineVirtual]

No problem. It's probably not the best solution as I mixed some stuff that should be in the classes into my modules because I couldn't get it work that way.

But I will post the code as soon as I'm at the PC.

[skylineVirtual]

Okay this is the Code to display the online status for flights of your VA (how I got it working):

The first thing you have to do is to add some columns into your phpvms tables.

Into the table phpvms_acarsdata you have to add a column named "network". Type is int(11), I don't have any standards set.

You need the same column in your table phpvms_pireps.

(note: your table prefix may vary)

The second step is in the file PIREPData.class.php

In the function fileReport (around line 560) put this just before the query:

/* Online Flight check */
$sql = "SELECT network FROM ".TABLE_PREFIX."acarsdata
WHERE pilotid='{$pirepdata['pilotid']}' ";
	 $ret = DB::get_row($sql);
$network = $ret->network;

Then edit the query so it looks like this:

/* File query */
$sql = "INSERT INTO ".TABLE_PREFIX."pireps(
 `pilotid`,
 `code`,
 `flightnum`,
 `depicao`,
 `arricao`,
 `route`,
 `route_details`,
 `distance`,
 `aircraft`,
 `flighttime`,
 `flighttime_stamp`,
 `landingrate`,
 `submitdate`,
 `accepted`,
 `log`,
 `load`,
 `fuelused`,
 `expenselist`,
 `source`,
 `exported`,
 `rawdata`,
 `network`
 )
 VALUES ( {$pirepdata['pilotid']},
 '{$pirepdata['code']}',
 '{$pirepdata['flightnum']}',
 '{$pirepdata['depicao']}',
 '{$pirepdata['arricao']}',
 '{$pirepdata['route']}',
 '{$pirepdata['route_details']}',
 '{$pirepdata['distance']}',
 '{$pirepdata['aircraft']}',
 '{$pirepdata['flighttime']}',
 '{$flighttime_stamp}',
 '{$pirepdata['landingrate']}',
 NOW(),
 ".PIREP_PENDING.",
 '{$pirepdata['log']}',
 '{$pirepdata['load']}',
 '{$pirepdata['fuelused']}',
 '0',
 '{$pirepdata['source']}',
 {$pirepdata['exported']},
 '{$pirepdata['rawdata']}',
'{$network}'	
 )";

Next step is to edit the file ACARS.php. You will have to add some stuff to the function viewmap so it looks like this:

public function viewmap()
{
$cflights = ACARSData::GetACARSData();
if ($cflights != ""){
Foreach ($cflights as $flight){
					 //get new file from Vatsim if the existing one is more than 5 minutes old
	 if(!file_exists('vatsimdata.txt') || time()-filemtime('vatsimdata.txt') > 300) {
	 //choose a random download location per Vatsim policy
			 $random = (rand(1, 2));
			 if ($random == 1) {$url = 'http://fsproshop.com/servinfo/vatsim-data.txt';}
			 if ($random == 2) {$url = 'http://info.vroute.net/vatsim-data.txt';}
			 //get the file
			 $file = new CodonWebService();
			 $contents = @$file->get($url);
			 //save new file to server
			 $newfile="vatsimdata.txt";
			 $file = fopen ($newfile, "w");
			 fwrite($file, $contents);
			 fclose ($file);
	 }
					 //get new file from Ivao if the existing one is more than 5 minutes old
	 if(!file_exists('ivaodata.txt') || time()-filemtime('ivaodata.txt') > 300) {
	 //choose a random download location per Ivao policy
			 $random = (rand(1, 2));
			 if ($random == 1) {$url = 'http://de1.www.ivao.aero/whazzup.txt';}
			 if ($random == 2) {$url = 'http://eu20.ivan.ivao.aero/whazzup.txt';}
			 //get the file
			 $file = new CodonWebService();
			 $contents = @$file->get($url);
			 //save new file to server
			 $newfile="ivaodata.txt";
			 $file = fopen ($newfile, "w");
			 fwrite($file, $contents);
			 fclose ($file);
	 }
	 $contents_Vatsim = file_get_contents('vatsimdata.txt');
	 $contents_Ivao = file_get_contents('ivaodata.txt');
			 if (strpos($contents_Vatsim,$flight->flightnum) !== false) {
$sql = 'UPDATE '.TABLE_PREFIX.'acarsdata
	 SET network =2
	 WHERE pilotid='.$flight->pilotid ;
	 DB::query($sql);

}
else if (strpos( $contents_Ivao,$flight->flightnum) !== false) {
	 $sql = 'UPDATE '.TABLE_PREFIX.'acarsdata
	 SET network =1
	 WHERE pilotid='.$flight->pilotid ;
	 DB::query($sql);

}
else {
	 $sql = 'UPDATE '.TABLE_PREFIX.'acarsdata
	 SET network =0
	 WHERE pilotid='.$flight->pilotid ;
	 DB::query($sql);
};
}
};
$this->set('acarsdata', ACARSData::GetACARSData());
$this->render('acarsmap.tpl');

}

The last step is to add this code to the function "data" in the ACARS.php module. I put it before the "Normalize the data" part.

if($flight->network== '0')
{
$c['onlineimage'] = "Offline";
}
elseif($flight->network== '2')
{
$c['onlineimage'] = "<div style='align: center'><img style='height: 18px;' src='http://www.skyline-va.de/phpvms/lib/skins/crystal/images/vatsim.png'></div>";
}
elseif($flight->network== '1')
{
$c['onlineimage'] = "<div style='align: center'><img style='height: 18px;' src='http://www.skyline-va.de/phpvms/core/lib/skins/crystal/images/ivao.png'></div>";
}

Note: You should change the image link to a path on your server.

Be careful that you don't put the things into if structures where the code doesn't belong.

Thanks to mseiwald and Vangelis for their help!

Lauritz

(It's probably not the best solution as I probably mixed up some class/module stuff but this is how I got it working)