Select records between the first and last time the speed was positive

Question

I have the following table:

id   speed   date
1    0       01/01/2015     
2    0       01/01/2015 
3    0       01/01/2015 
4    0       01/01/2015 
5    0       01/01/2015 
6    0       01/01/2015 
7    25      01/01/2015 
8    78      01/01/2015 
9    13      01/01/2015 
10   45      01/01/2015 
11   0       01/01/2015 
12   80      01/01/2015 
13   86      01/01/2015 
14   10      01/01/2015 
15   0       01/01/2015 
16   0       01/01/2015 
17   0       01/01/2015 
18   0       01/01/2015

This is just a small part of the table, it has many dates. Each new date starts and ends with an unknown amount of zeros. And I have to skip these zeros and get only what's between them. So I came up with the following solution:

1. Get the ID of the first row whose speed is positive (in our table it's id number 4).

2. Get the ID of the last row whose speed is positive (in our table it's id number 12).

3. Get lines between these two IDs (4 and 12).

I get the id of the first row whose speed is positive:

$q1=$conn->prepare("SELECT * FROM tableX WHERE speed > 0 order by date asc LIMIT 1");
$q1->execute();
$r1=$q1->fetch();
$first_id = $r1['id'];

And then get the id of the last row whose speed is positive:

$q2=$conn->prepare("SELECT * FROM tableX WHERE speed > 0 order by date desc LIMIT 1");
$q2->execute();
$r2=$q2->fetch();
$last_id = $r2['id'];

And then use those ids to get what I want:

$q3=$conn->prepare("SELECT * FROM tableX WHERE id between '$first_id' and '$last_id'");
$q3->execute();
while($r3=$q3->fetch(){}

Expected result:

id   speed   date
7    25      01/01/2015 
8    78      01/01/2015 
9    13      01/01/2015 
10   45      01/01/2015 
11   0       01/01/2015 
12   80      01/01/2015 
13   86      01/01/2015 
14   10      01/01/2015

My code is actually working but I think this solution of mine is lame, so I am looking for an improvement, kind of all these three queries in one!

Answer 1

First of all a style comment: Please use longer descriptive names. Stuff like $q3, $q2, is just ugly, and confusing.

You could simplify your query either by using subqueries in the WHERE clause, or by creating a temporary table in the FROM clause. You could use the BETWEEN operator, but some think BETWEEN is evil as it is somewhat unclear from the wording whether the range is inclusive or exclusive. In addition if used on date fields, you could have various interesting rounding errors. So it better to stick with ordinary operators.

I've commented upon the fact that you'll have a potential issue related to order, and I haven't compensated for that in the following code, only assuming that rows are indeed ordered by id. This could/should possibly be enforced. Here is the SQL code to get your rows in one go:

SELECT tableX.* FROM tableX,
  ( SELECT min(id) as startId, max(id) as endId FROM tableX
     WHERE speed > 0 AND date = '2015-01-01' ) tmp
 WHERE id >= tmp.startId AND id <= tmp.endId

Note that in the temporary table select I chosen a given date, instead of ordering by date (which most likely would throw of the id part quite easily. Another option could be to specify a double order like in ORDER BY date, id.

Untested version translated into php-code:

$speed_query = $db_connection->prepare(
   "SELECT tableX.* FROM tableX, 
     ( SELECT min(id) as startId, max(id) as endId FROM tableX 
        WHERE speed > 0 AND date = '$date' ) tmp 
   WHERE id >= tmp.startId AND id <= tmp.endId ");
$speed_query->execute();
while($row = $speed_query->fetch()) {
   ... do something useful with $row ...
}

The advantage of this method is that you now get all of your data from one single query, and one roundtrip from your code base to the database engine. Using subqueries like this shouldn't be to expensive, and at least when comparing to making several roundtrips it is a way better solution.

_{PS! You could consider using heredocs or other variations to get the multiline string in your query. But do try to break it over multiple lines to enhance the readability of the SQL query. See PHP string doc for various option on how to use strings in PHP.}

Answer 2

So, your first two functions are correct but unnecessary for thing you want to do.

You successfully get ID of first and last row where 'speed' is larger than 0. And that's okay, but not needed.

But problem is here:
$q3=$conn->prepare("SELECT * FROM tableX WHERE id between '$first_id' and '$last_id'");

This function will get all rows between first and last id but it doesn't care what is value of 'speed'.

Now, let's examine this fixed code:

$q2=$conn->prepare("SELECT * FROM tableX WHERE speed > 0");

This one will get all rows where 'speed' is larger than 0.
It will look through every row and check column 'speed' value.

If you want ordering, you just change it to
$q2=$conn->prepare("SELECT * FROM tableX WHERE speed > 0 ORDER BY //-SOME ROW-// ASC"); (or DESC)
And you can safely delete those two functions that get first and last row id.