Table query, select data from table1, but only if user settings in table2 is set to a specific value

I got a problem with this query. Not sure what this type of problem is called, so have problems searching for a solution 🙁

I got two tables: configurations, users

Lets say the tables looks like this:

configurations:
id
userid
...more

users:
id
smart enum('0','1')
...more

This is the query I got now, but it fetch a lot of rows with the same data:

SELECT
    configurations.*
FROM
    configurations, users
WHERE
    configurations.userid!='$userid' AND configurations.deleted='0' AND users.smart='1' AND
    (
        configurations.id LIKE '%$filter%' OR
        configurations.type LIKE '%$filter%' OR
        configurations.type_desc LIKE '%$filter%' OR
        configurations.other_id LIKE '%$filter%' OR
        configurations.project_no LIKE '%$filter%' OR
        configurations.part_no LIKE '%$filter%' OR
        configurations.company_name LIKE '%$filter%' OR
        configurations.company_contact LIKE '%$filter%'
    )
ORDER BY
    configurations.createdate DESC
LIMIT
    $search_config_count
";

How can I make it, so I only get ‘configurations’ if users.smart=’1′ ?

My configurations table has a “userid” field, that match the ID of a user in the users table. That specific user need to have smart=’1′, for the configurations to be listed.

Answer

If I understand your question correctly, you can just do a JOIN with both tables.

$sql = "
SELECT
    *
FROM
    configurations,
    users
WHERE
    configurations.userid!='$userid' AND configurations.deleted='0' AND users.smart = 1 AND
    (
        configurations.id LIKE '%$filter%' OR
        configurations.type LIKE '%$filter%'
    )
ORDER BY
    createdate DESC
LIMIT
    $search_config_count
";

Advertisement

Answer