how to SELECT a range

Anon

..having difficulty understanding how to select all records from MySQL within a range of two criteria.

In other words, my form has two pull-down lists of items.

List one - 1-13 items

List two - the same 1-13 items

Let's say we choose item 2 in list one, and item 5 in list two.

How do I query the database to echo all records in the range between 2 and 5 inclusive, i.e. 2,3,4,5 ?

thanks

Anon

SELECT *
FROM table
WHERE field BETWEEN '$item1' AND '$item2'

Anon

I think you can use the "limit" phrase to finish it. For example,
select * from table_name limit first_cri last_cri;

Anon

Actually, LIMIT serves a different purpose: to restrict the number of records returned (and, optionally, the starting point in terms of relative rows within the query results). He was asking about restricting the rows returned to having particular values in a particular column.

Anon

thanks for your assistance, but it isn't working yet.

You said: "SELECT * FROM table WHERE field BETWEEN '$item1' AND '$item2'"

I tried this:

$query = "SELECT * FROM mytable WHERE myfield BETWEEN '$item1' AND '$item2'";
$result = mysql_query($query);

It does occasionally pull out some records, but the results seem almost to be random, favoring the '$item2' end of the query.

Also, in my million PHP/MySQL books and online references, there does not appear to be any info in the use of BETWEEN in a SELECT statement. Can you point me toward anything that will clarify this for me, please.

Many thanks.

Anon

Make sure that you are using between on a field that is structured correctly (an integer field for best results). Remember that if you are comparing strings it's a given that 'one' is less than 'two'.

In the MySQL manual (version 3.23.5-beta 13) on page 146 there is a description of how between is used.

expr BETWEEN min AND max
if expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1 ortherwise it returns 0. The is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. The first argument(expr) determines how the comparison is performed. IF expr is a case-insensitive string expression, and case-insensitive string comparision is done. If expr is a case-sensitive string expression a case-sensitive string comparison is done. If expr is an interger expression, an integer comparison is done. Otherwise, a floation-point (real) comparison is done.

mysql> select 1 BETWEEN 2 AND 3;
-> 0
mysql> select 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> select 2 BETWEEN 2 AND '3';
-> 1
mysql> select 2 BETWEEN 2 AND 'x-3';
-> 0

Anon

First. BETWEEN keywords syntax can be found in the MySQL User Manual. Search for \"BETWEEN\" and you\'ll find it.

In your case, I think you must add an additional \"ORDER BY Clause to avoid random data retrieval (in the sequence where your record is stored.

Hope that helps.