select help

[janim]

Hi all

i'm having problem make select statement, i'm sure it just simple but i just cant find the solution

let's say i have this table  : downs: id, name, users .

users is user id who can view this file and it's in php session

sql = select * from downs:

res =

id    name      users

---------------------

1    file          1,2,4

2    anot        5,2,4

3    rgrg        1,7,2,6

so i want to do select statement: select * from downs where {$user_id} in users

which means that logged in user is in that row

 

thanks for any help

[premiso]

SELECT * FROM downs WHERE users LIKE '%userid,%'

 

Should get you what you want, however, for this to work the ending user id's will need a comma added to them so you do not get false results from something like, searching for 4 and there is a userid of 40.

 

So IE:

1  file  1,2,4  would need to be changed to  1  file  1,2,4,

 

 

[printf]

Use a regex with a whole word boundary so you don't have to worry about the separator *,*. We use the whole word boundary so user_id 24, 14, etc won't match user_id equals 4

 

 

$user_id = 4; // would return rows 1, 2

// you can match multiple user_id(s) at the same time.

// $user_id = '5|7'; // would return rows 2,3

$query = "SELECT * FROM yourTable WHERE users REGEXP '[[:<:]]" . $user_id . "[[:>:]]'";

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set

 

Untested but should work -

select * from downs where FIND_IN_SET('$user_id',users)

 

Edit: And if you are trying to find if a specific user has permission to download a specific file, you would include the name = comparison in your query -

select * from downs where FIND_IN_SET('$user_id',users) AND name = '$the_file_name_that_was_requested'

[janim]

thanks all for help

i have made simple way to deal with that

                               while($rowd = mysql_fetch_array($sqld)){
                                	//$row_users = $rowd['users'];
                                	$users_row = explode(",",$row_users);
                                	if($rowd['users'] !== ''){
                                	  if(!in_array($user,$users_row)){ continue; }
                                	}
echo "whatever";
}

 

 

[PFMaBiSmAd]

You do realize that looping through all the rows in a result set and performing a comparison against each row to find a specific value, using some slow parsed/tokenized/interpreted php code, is going to be at least 10 times slower than letting the database engine find the correct row(s) for you?