Search Query

[srikanth2321]

Hello all,

I have a problem in building the query for search.

I have a table which contains a column X stores numerous values in each respective cell of a row.

For e.g.

rows           Column X
1              120;132;143;154;165
2              122;134;543;566;

What I want to do is to make a query which search with a given range and return the rows which falls into the search

For e.g.

If I want to get the rows which contains values between 120 and 136 then it should return rows 1 and 2.

The datatype for Column X is varchar since each cell has to store numerous integers.

Please help me to get over this.

thanks

[gotboots]

try this:

$this = "120";
$that = "136";

mysql_query("SELECT * FROM table WHERE column BETWEEN '$this' AND '$that'");

then a while statement for results.

Use NOT BETWEEN for well, you get it, not between. Hope this helps you. Please mark solved if solved, cheers.

[smantscheff]

First of all, you have a bad table design. Column X should not exists except in a view.
If you still stick to the design, the question is if the values in column X refer to another table. For the sake of the argument let's assume they refer to column C in table T and for each single value in column X there is a referred value in table T.
Then you could query:

select * from myTable, T
where locate(concat( ',', T.C, ';'), X)
or X rlike concat( '^', T.C, ';')
or X rlike concat( ';', T.C, '$');

[aquanetta]

The Search query syntax is that the

mysql_query("select * from table_name where column_name BETWEEN value_start AND value_end")

EXAMPLE:

mysql_query("SELECT * FROM student WHERE mark BETWEEN 30 AND 50");