Query Problem

[Rommeo]

For the website I  m working on

 

one table is like :

table_user

usernaname | shoppingbox

rommeo  | 1 3 5 19 36 89...94 105 1002

julliet | 3 5 6 8 9 10

rome | 8 9 10

 

I want to list the users whose shoppingbox does not contain "1", I m stuck about the query since it does not contain just one value.

$boxitem = 1;

$query = "SELECT * FROM table_user WHERE shoppingbox != $boxitem";

 

This query would not work I guess,

 

( I m aware of the problem about the design of the table, But I must use this way )

[Rommeo]

any ideas ? which keyword I need to use ?

[suresh_kamrushi]

Hi Rommeo,

you can try this

$boxitem = 1;
$query = "SELECT * FROM table_user WHERE  shoppingbox REGEXP '^$boxitem | $boxitem$| $boxitem ' OR shoppingbox =$boxitem";

 

 

 

Suresh

[Rommeo]

Hi Rommeo,

you can try this

$boxitem = 1;
$query = "SELECT * FROM table_user WHERE  shoppingbox REGEXP '^$boxitem | $boxitem$| $boxitem ' OR shoppingbox =$boxitem";

Hey,

I think it s the correct code and works well, but it gives me the result of datas where shoppingbox contains $boxitem, I want the results of datas where shoppingbox does NOT contain boxitem

 

[mikosiko]

try:

 

   $query = "SELECT * FROM table_user WHERE  shoppingbox NOT REGEXP '\[\[:<:]]$boxitem\[\[:>:]]'";

 

 

 

[fenway]

If the spaces are simply delimiters, then you can hack it -- replace() spaces with commas, then use FIND_IN_SET().

 

But you really should fix your table.