如何查询第一个和最后一个日期

Question

I'm querying a wildlife sightings database to display the first sighting of Dingy_Skipper based on querystring 'yr' AS Dingy_Skipper_FDate. I would like to also display the last/latest sighting from the same querystring 'yr' AS Dingy_Skipper_LDate

I presume I need to use UNION but I have tried several times and can't seem to get it to work. I have never used UNION before so any help appreciated. Massive thank you in advance!

I have tried the following query but this produces an error on line $Dingy_Skipper1 = $sp1->query($Dingy_Skipper);.

<?php
// connect
$sp1 = dbConnect('read', 'pdo');
// prepare query
$theyear = $_GET['yr'];
$Dingy_Skipper = "
SELECT rDate AS Dingy_Skipper_FDate, Dingy_Skipper
FROM wbcrecords
WHERE YEAR(rDate) = '$theyear' AND Dingy_Skipper >='1' ORDER BY rDate ASC Limit 1
UNION
SELECT rDate AS Dingy_Skipper_LDate, Dingy_Skipper
FROM wbcrecords
WHERE YEAR(rDate) = '$theyear' AND Dingy_Skipper >='1' ORDER BY rDate DESC Limit 1";
// submit query capture result
$Dingy_Skipper1 = $sp1->query($Dingy_Skipper);
// free database
$Dingy_Skipper1->closeCursor();
?>

I have updated the query (see below) which now works correctly and I presume will protect against SQL injection? However, what would be the most efficient way of selecting the first and last date from a second column in the same table called Grizzled_Skipper so I have the first and last dates as $Grizzled_Skipper_FDate and $Grizzled_Skipper_LDate for the Grizzled_Skipper column as well as $Dingy_Skipper_FDate and $Dingy_Skipper_LDate for the Dingy_Skipper column?

<?php
if (isset($_GET['yr'])) {
require_once('inc/connection.php');
$conn = dbConnect('read', 'pdo');
$sql = 'SELECT MIN(rDate) AS Dingy_Skipper_FDate, MAX(rDate) AS Dingy_Skipper_LDate, Dingy_Skipper
FROM wbcrecords
WHERE YEAR(rDate) = :yr AND Dingy_Skipper >="1"';
$searchterm = $_GET['yr'];
$Species = $conn->prepare($sql);
$Species->bindParam(':yr', $searchterm, PDO::PARAM_STR);
$Species->bindColumn(1, $Dingy_Skipper_FDate);
$Species->bindColumn(2, $Dingy_Skipper_LDate);
$Species->bindColumn(3, $Dingy_Skipper);
$Species->execute();
$numRows = $Species->rowCount();
}
?>

Answer 1

You could likely get this using MIN() and MAX(), like this:

  SELECT MIN(rDate) AS Dingy_Skipper_FDate,
         MAX(rDate) AS Dingy_Skipper_LDate, 
         Dingy_Skipper
    FROM wbcrecords
   WHERE YEAR(rDate) = '$theyear' 
     AND Dingy_Skipper >='1'
GROUP BY Dingy_Skipper

Be sure to escape the variables that you are using in this query. Otherwise, you are wide open to SQL injection attacks.

EDIT: Rather than escaping, you would be better off using prepared statements and using the $theyear variable as a parameter.