Select records between the first and last time the speed was positive 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:


Get the ID of the first row whose speed is positive (in our table it's id number 4).
Get the ID of the last row whose speed is positive (in our table it's id number 12).
Get lines between these two IDs (4 and 12).


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

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


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

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


And then use those ids to get what I want:

$q3=$conn-&gt;prepare("SELECT * FROM tableX WHERE id between '$first_id' and '$last_id'");
$q3-&gt;execute();
while($r3=$q3-&gt;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!
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 &gt; 0 AND date = '2015-01-01' ) tmp
 WHERE id &gt;= tmp.startId AND id &lt;= 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-&gt;prepare(
   "SELECT tableX.* FROM tableX, 
     ( SELECT min(id) as startId, max(id) as endId FROM tableX 
        WHERE speed &gt; 0 AND date = '$date' ) tmp 
   WHERE id &gt;= tmp.startId AND id &lt;= tmp.endId ");
$speed_query-&gt;execute();
while($row = $speed_query-&gt;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.