CODEWITHSUNDEEP
mysqlsql
Ponzio Pilato
Ponzio Pilato

Reputation: 325

MySql: select rows between two specific values:

I have this table:

enter image description here

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

Upvotes: 0

Views: 4126

Answers (4)

Malcolm G
Malcolm G

Reputation: 614

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.

Upvotes: 1

Manav Sharma
Manav Sharma

Reputation: 187

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')

Upvotes: 0

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

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.

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Try this:

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

Upvotes: 0

Related Questions