MySql: select rows between two specific values:

Question

I have this table:

and I'd like to select the rows between 'inizio_votazione1' e fine_votazione1'.

I tried this code:

$sql  = "SELECT codice FROM scansioni WHERE codice BETWEEN 'inizio_votazione1' 
AND 'fine_votazione1'";

while ($row = $result->fetch_row()) {
// print_r($row);

$res = $row[0];
echo $res;
echo "<br />";
}

but I see no result.

Which is the correct syntax to retrieve the desired result? Thanks

Answer 1

I don't think you really want to use BETWEEN. That will basically look for alphabetically ordered values that are between those two. I would think you could do something like this:

SELECT codice
FROM scansioni
WHERE id > (SELECT MIN(id) 
            FROM scansioni
            WHERE codice IN ('inizio_votazione1', 'fine_votazione1'))
  AND id < (SELECT MAX(id) 
            FROM scansioni
            WHERE codice IN ('inizio_votazione1', 'fine_votazione1'))

This may not be the most elegant solution, but from what I tried, it worked.

A better option might be to add a separate table that stores the start and end id for each group. Then you could just get the start and end ids for the group you want and then select all the values from scansioni that have ids in the correct range.

Answer 2

Try this:

SELECT codice FROM scansioni WHERE codice between 'inizio_votazione1' abd 'fine_votazione1'

Answer 3

Try changing:

$sql  = SELECT codice FROM scansioni WHERE codice BETWEEN 'inizio_votazione1' 
AND 'fine_votazione1';

To:

$sql  = "SELECT codice FROM scansioni WHERE codice BETWEEN 'fine_votazione1' 
AND 'inizio_votazione1'";

BETWEEN uses the sort order to determine the result.

Also, strings need to be quoted in php.

Answer 4

Try this:-

SELECT codice FROM `scansioni` WHERE id between (select id from scansioni where codice='inizio_votazione1') and (select id from scansioni where codice='fine_votazione1')