Filter a MySQL result from a PHP array

This is hopefully a quick php question...

I have stored a number of id's in a field on a user table like '1,2,3'. I now want to query another table against those numbers. I think my brain has gone to mush this morning because can't seem to get it right...

Could 'IN' be used in the query?

Update -

Realised I didn't explain my self well enough...

I have two tables, one for users (id, username, password, products) The 'products' field has comma separated id's in which related to another table which holds product information.

I am basically开发者_运维百科 trying to filter out what a user can see, via a query to the database with that users privileges.

You should fix your database schema; then, querying will be obvious and efficient.

Every column in your database should contain atomic values. If you've stored multiple values in a single column, it means you should have created a table with a one-to-many relation.

CREATE TABLE user_whatever (user_id int, whatever_id int)

...with one row per item related to the user. To query another table against those numbers, you simply JOIN this new table in the query.

You may be better off using a join, hard to tell from your question what sort of data you're working with.

Join's would be far more efficient with large amounts of data.

without knowing your database structure it's impossible to help further

SELECT column_names
FROM table_one
INNER JOIN table_two
ON table_one.column_name=table_two.column_name

is the basic syntax.

You can use IN in SQL without any problem, but you will need to convert the PHP array to a compatible list for example using the implode(separator,array) function...

[Update] Seeing the question update: The technique you chose for saving the "products" is not very good and WILL cause you problems in the future. the standard solution for storing these values is creating an additional table (userid,productid) and the primary key is both fields in it you create multiple rows for each user/product permission.

To actually use the specific solution you created you will need to run an SQL getting the values from the table, and then using a SECOND SQL use the WHERE IN $ids to actually get the data (as far as I know you can not use the IN on a string result like this (no such thing as EVAL in SQL)

Like everybody said, you should use atomic values. But since everybody already explained that, I'm only going to clarify in detail what you should do.

Use a table for users, which contains the id, username and password, but without product

id - username - password  
1  - mike     - XXXX  
2  - joe      - XXXX

Then you have your products table

id - name                 - price
1  - Aluminum bat         - 19.99
2  - Scattergun           - 39.99
3  - BONK (with isotopes) - 14.99

Now, you introdouce a third table, which represents what users have bought.

user - product
1    - 2
1    - 3
2    - 1

Now, if you want to select the products that mike has bought, you just select all rows from the third table where the user id is equal to 1. In conjunction with foreign keys and indexes, the queries should also have a better performance. For clarification, this is how my example would look like in your current implementation.

id - username - password - products
1  - mike     - XXXX     - 2,3
2  - joe      - XXXX     - 1

$sql = "SELECT * FROM table
WHERE id IN (";

$i=1;
$count = count($array);
foreach($array as $a){
if($i==$count){
$sql .= $a['id'];
}else{
$sql .= $a['id'].','
}
$i++;
}

$sql .= ")";

Please try this..

Get the field with the ids ('1,2,3') from your first table into some variable -> $ids

$sql = sprintf(SELECT * FROM second_table WHERE id IN (%s),$ids);

Hope this helps.

ps.: I don't recommend storing id this way.