Advice required please (mysql/php)

[amagicdrag0n]

MYSQL 5.5
I collect information from aircraft using an adsb receiver.
This information populates a mysql table called 'realtime' using PHP. The information stays in this table until no signal has been received from the aircraft for 20 minutes, the details of that aircraft is then moved to a 'history' table

All aircraft are given a 'squawk' code by Air Traffic Control and each airfield has a number of these codes that is unique to them.

I need to isolate those aircraft that are being controlled by the ATC at particular airfields .

The 'squawk' code can change a number of times while the aircraft is my range and it is only the last code that is seen that goes into the 'history' table. So, I need to be able to collect and save the aircraft information while it is sending (squawking) the code that I am interested in before it changes.
Would some kind soul please advise the best way to do this?
Thanks
Dave

 

[Unidentified]

Well.. I advice you watch this video...

You must be registered for see links

.. and you will need to set up your ADS B Reciever in a location as close as possible to an airport/airspace. You will need a Linux based computer/server to decrypt the data and then with external programs, you can view the data live.. However that is a lot of money to invest and i am pretty sure you do not have the time/money or relevance to retrieve this data.. Also I am sure decrypting data from an airport/airspace is illegal, so.. you could be prosecuted for theft of data..

I personally think you should not carry on with this idea lol.. Well done on the attempts tho

 

[amagicdrag0n]

Hi and thanks for your reply.
I already collect all the data that I require from aircraft flying over the UK (some 200 mile radius of my location)
There is a thriving worldwide community of Radar Spotters out there at the moment ( please do Google - PlanePlotter, FlightRadar24 etc)
My website has been up and running for more than 6 months now at donnyradar dot co dot uk (please take a look) and now I just want to refine what I can see.
By the way the data that comes down from the aircraft is in the public domain, so it is not illegal to collect it, only to act on that data in a way that will endanger safety.
As a matter of interest, the cost to set up a site is minimal, my original setup cost me less than £20

 

[Unidentified]

Aha, Very nice website.. I am wondering on the uses of this website tho? Who would use it? What will they gain from it? And I meant the initial set up for it the be kept at the locations to keep collecting data on a real-time bases without your equipment getting stolen :')
 
I just checked out

You must be registered for see links

and now I know the relevance to your website

 

[amagicdrag0n]

The set up is actually an antenna on the roof of my house and that feeds a receiver on my desk. I use a windows PC to collect the data and that is then sent to a Ubuntu web server (in the same room)
Some people use my site to give them a 'heads up' as to what type of aircraft is in their area. Those who collect registrations will use it to confirm their sightings and yet others will use it to follow a loved ones flight to wherever

 

[Unidentified]

How much does that all cost you tho? including the PC etc.. and if you can output the information from your receiver to your computer on a webage normally, you can easily write a query for all the data

 

[amagicdrag0n]

It costs very little to be honest. the PC is a standard desktop (refurbished cost £150) the webserver is an old pc that can't handle modern Windoze put runs Ubuntu very well. As I said the original adsb equipment cost less than £20.
I know that I just need to write a query of some sort, but nothing I have tried works the way I want it to. I have tried Before and After Insert and Before and After Update triggers, but they don't seem to work correctly. They put the data into a different table, but stop any new data coming into the 'realtime' table

 

[Unidentified]

Can you output the data using PHP or JS without the Query tho?

 

[amagicdrag0n]

I'm not sure what you mean... remember I am not a programmer really just an old guy doing his best
I collect the data from my receiver and that is sent to a PHP file that then populates the 'realtime' table of mysql database. I can then obviously query that table (and others) to populate my website.
What I need to do is somehow isolate the 'squawk' codes that I need and copy those details to another table without affecting the rest of the information coming in. This must be done dynamically so that all the aircraft are identified, even if I am away from the machine

 

[Unidentified]

Do you wish to share you screen and show me and I will take a quick look into it?

 

[amagicdrag0n]

OK, what do you need from me??? I have Team Viewer installed

 

[Unidentified]

teamviewer will do, PM me