<?php
// show all errors while debugging
error_reporting(E_ALL);
ini_set('display_errors', '1');
//set default timezone for date conversion
date_default_timezone_set('UTC');
$connection = mysql_connect ("localhost", "user", "pass");
if (!$connection)
{
die('Not connecting to db server : ' . mysql_error());
}
// Sets the active MySQL database.
$db_selected = mysql_select_db("satellite_data_example", $connection);
if (!$db_selected) {
die('Can\'t use db : ' . mysql_error());
}
// get a unique list of all PTT
$get_ptts = "
SELECT distinct ptt from birds
ORDER BY ptt";
$result=mysql_query($get_ptts) or die ("Could not retrieve unique PTT numbers: ".mysql_error());
?>
<?php
// Itterates through the MySQL results, for each PTT.
while ($row = @mysql_fetch_assoc($result)) {
// Var for unique PTT number of current bird
$bird = $row['ptt'];
print "Current ptt = " . $bird . "
<br />
";
//get ptt, location_date, location_class, latitude, longitude for best row from each transmission for current bird during timewindow
$get_best_locs = "
SELECT MAX(location_class) AS id, location_class, ptt, latitude, longitude, location_date, FROM_UNIXTIME(location_date) FROM birds
WHERE (ptt=$bird)
and (location_class IN (1,2,3))
and (location_date >= 1398072323)
and (location_date <= 1398115003)
GROUP BY YEAR(FROM_UNIXTIME(location_date)), MONTH(FROM_UNIXTIME(location_date)), DAY(FROM_UNIXTIME(location_date))";
$result2=mysql_query($get_best_locs) or die ("Could not retrieve all best locations data: ".mysql_error());
if (mysql_num_rows($result2)) { // if one or more non-empty rows are returned..
while ($row2 = @mysql_fetch_assoc($result2)) {
$locDateTimestamp = strtotime($row2["FROM_UNIXTIME(location_date)"]);
print "ptt - " . $row2['ptt'] . "id - " . $row2['id'] . " class - ". $row2['location_class'] . " timestamp - " . $row2['location_date'] . " (" . date('d.m.Y H:i:s e', $locDateTimestamp) . ") latlng - (" . $row2['latitude'] . "," . $row2['longitude'] . ")
<br /><br />";
}
}
}
?>
php-example.html<?php // demo/temp_williamskellorn.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';
// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28427438.html
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
trigger_error("CONNECT FAIL: $mysqli->connect_errno $mysqli->connect_error", E_USER_ERROR);
}
$mysqli->query('SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";');
$mysqli->query('SET time_zone = "+00:00";');
// NOTE THE CORRECTIONS TO THE DATA TYPES IN THE MODIFIED COLUMNS
$mysqli->query("CREATE TEMPORARY TABLE `birds` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ptt` varchar(50) NOT NULL DEFAULT '',
`location_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`location_class` varchar(50) NOT NULL DEFAULT '',
`latitude` DECIMAL(11,6) NOT NULL DEFAULT '0.0',
`longitude` DECIMAL(11,6) NOT NULL DEFAULT '0.0',
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1")
;
$arr = array(
array('128303', '1398072323', '0', '38.38434', '-0.38647'),
array('128303', '1398074434', '0', '38.41594', '-0.70849'),
array('128303', '1398077966', 'B', '38.44554', '-0.8972'),
array('128303', '1398087460', '0', '38.89024', '-0.56271'),
array('128303', '1398092003', '1', '38.86478', '-0.84718'),
array('128303', '1398093572', 'B', '38.91282', '-0.87303'),
array('128303', '1398096591', '0', '39.12389', '-0.65677'),
array('128303', '1398098399', '1', '39.19132', '-0.58176'),
array('128303', '1398100680', '1', '39.24069', '-0.62806'),
array('128303', '1398102564', 'B', '39.25547', '-0.63597'),
array('128303', '1398106766', '3', '39.23175', '-0.64606'),
array('128304', '1398109369', '0', '39.26613', '-0.65195'),
array('128304', '1398112352', 'A', '39.26271', '-0.66721'),
array('128304', '1398113216', '1', '39.25291', '-0.66799'),
array('128304', '1398115003', '2', '39.25449', '-0.64654'),
array('128304', '1398115003', '0', '39.25449', '-0.64654')
)
;
// LOAD THE DATA
foreach ($arr as $sub)
{
// CHANGE THE UNIX TIMESTAMP INTO A HUMAN-READABLE DATETIME VALUE
$sub[1] = date('c', $sub[1]);
$mysqli->query("INSERT INTO `birds` (`ptt`, `location_date`, `location_class`, `latitude`, `longitude`) VALUES ('$sub[0]', '$sub[1]', '$sub[2]', '$sub[3]', '$sub[4]') ");
}
// ACTIVATE THIS BLOCK TO LOOK AT THE LOADED DATA
$res = $mysqli->query('SELECT * FROM birds');
while($row = $res->fetch_object())
{
// print_r($row);
}
$sql = <<<EOD
SELECT id, MAX(location_class) AS location_class, ptt, latitude, longitude, location_date FROM birds
WHERE location_class IN (3,2,1)
GROUP BY ptt ORDER BY location_class DESC, location_date DESC, ptt ASC;
EOD;
$res = $mysqli->query($sql);
while($row = $res->fetch_object())
{
var_dump($row);
}
table that I dare not reformatNobody would suggest that you reformat. Just use the ALTER TABLE command to add the appropriate column definitions, then you can use those column definitions in your work and everyone else can "catch up" as time permits.
there would be two outputs for PTT 128303OK, that eliminates the GROUP clause. Back to the drawing board...
<?php // demo/temp_williamskellorn.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';
// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28427438.html
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "satellite_data_example";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
trigger_error("CONNECT FAIL: $mysqli->connect_errno $mysqli->connect_error", E_USER_ERROR);
}
$mysqli->query('SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";');
$mysqli->query('SET time_zone = "+00:00";');
// HAVE A LOOK AT THE LOADED DATA
$res = $mysqli->query('SELECT * FROM birds');
while($row = $res->fetch_object())
{
echo PHP_EOL
. "$row->ptt "
. "$row->location_date "
. "$row->location_class "
. "$row->latitude,$row->longitude "
. "ID: $row->id"
;
}
echo PHP_EOL;
print '<br /><br />';
// FIND THE PARTS OF EACH ROW WITH THE MAX(location_class) FOR THE GROUP
$sql = <<<EOD
SELECT FROM_UNIXTIME(location_date,'%Y-%m-%d') AS loc_date, MAX(location_class) AS max_location_class, ptt
FROM birds
WHERE location_class IN (1,2,3)
GROUP BY ptt, location_date
ORDER BY ptt, location_date
EOD;
if (!$res = $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. PHP_EOL
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
// COPY THE INFORMATION WE NEED TO IDENTIFY THE RIGHT ROWS
while ($row = $res->fetch_object())
{
$dcps[] = $row;
}
print_r($dcps);
print '<br /><br />';
// GET ID, LAT, LNG FOR EACH ROW
$dataset = array();
foreach ($dcps as $row)
{
$sql = <<<EOD
SELECT *
FROM birds
WHERE FROM_UNIXTIME(location_date,'%Y-%m-%d') = '$row->loc_date'
AND location_class = '$row->max_location_class'
AND ptt = '$row->ptt'
ORDER BY ptt, location_date
LIMIT 1
EOD;
if (!$res = $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. PHP_EOL
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
$dataset[] = $res->fetch_object();
}
// SHOW THE WORK PRODUCT
print_r($dataset);
?>