1

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();
}
?>
Steven Cheshire
  • 61
  • 1
  • 7
  • **Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). – Dharman Apr 24 '19 at 20:37
  • What are you doing with cursors? Are you doing anything with cursors at all or is it a red herring? – Dharman Apr 24 '19 at 20:42
  • What is the output of your query? What is not working for you? I would recommend to remove the aliases from your columns. – Dharman Apr 24 '19 at 20:45
  • Thanks Dharman for all your feedback. Appreciate your help. I'm still very new to MYSQL and php so learning all the time! I have updated my original question and query but wonder if you can help re: querying a second column and the most effective way of doing this? Oh, and cursors was a red herring!! Sorry Steve – Steven Cheshire Apr 25 '19 at 11:08
  • Your code looks much better, but please user proper indentations too. What I see you are still missing is `$Species->fetch(PDO::FETCH_BOUND)`, and `execute()` should be before `bindColumn`. – Dharman Apr 25 '19 at 11:19
  • Thanks Dharman. How would I query a second column called (Grizzled_Skipper) in the same table to get first and last dates for Grizzled_Skipper as well as Dingy_Skipper in the same query resulting in $Species->bindColumn(1, $Dingy_Skipper_FDate); $Species->bindColumn(2, $Dingy_Skipper_LDate); $Species->bindColumn(3, $Dingy_Skipper); $Species->bindColumn(4, $Grizzled_Skipper_FDate); $Species->bindColumn(5, $Grizzled_Skipper_LDate); $Species->bindColumn(6, $Grizzled_Skipper); Steve – Steven Cheshire Apr 25 '19 at 12:05
  • It looks like you should [normalize your DB](https://en.wikipedia.org/wiki/Database_normalization). If you plan to have every animal in another column then your SQL will be too complex. – Dharman Apr 25 '19 at 12:07
  • Unfortunately its a database I have inherited although I only need to do this on no more than 5 columns. Not quite sure how to do that. – Steven Cheshire Apr 25 '19 at 12:13

1 Answers1

0

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.

derek.wolfe
  • 1,086
  • 6
  • 11
  • 4
    Do not escape variables, **USE PREPARED STATEMENTS** instead. – Dharman Apr 24 '19 at 20:48
  • That is the best case scenario, yes... But we don't even know that the OP's servers are set up to handle prepared statements. Escaping will at least ensure the query is safe and they can look into using prepared statements at a later date. EDIT: scratch that... just noticed the pdo tag.. – derek.wolfe Apr 24 '19 at 20:52
  • Just remove the mention about escaping entirely. Don't give anyone such ideas. Prepared statements are supported for many years in PHP through MySQLi and PDO extensions. – Dharman Apr 24 '19 at 21:02
  • Thanks drakin8564. Your code and additional comments certainly got me thinking. Hopefully I'm more on track now (see updated original question). If you could help with the best way of querying a second column to find first and last dates of second column, that would be amazing. Many thanks. Steve – Steven Cheshire Apr 25 '19 at 11:11
  • Sounds like you will have to move this to a subquery and each of your other min and max dates will have to be separate subqueries as well. Each will only have one row, so you can join them all together on true. This will put all of your dates on one row. The other option would be to union all of the subqueries, but doing that would lose your column aliases. – derek.wolfe Apr 25 '19 at 13:24