0

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.

Kenneth Poulsen
  • 929
  • 10
  • 25
  • Possbile SQL injection [read more](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496?r=SearchResults&s=1|647.8381#60496) – Jimmix Nov 12 '19 at 20:53
  • Please show the structures of both tables: users and configurations – Manny Ramirez Nov 12 '19 at 21:24
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Nov 12 '19 at 23:00
  • @MannyRamirez Tables added – Kenneth Poulsen Dec 02 '19 at 08:25

1 Answers1

1

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
";
davidev
  • 7,694
  • 5
  • 21
  • 56
  • I can't get this to work. It fetch the same table row/record over and over. I think the issue is related to this : "users.smart = 1". users.smart has to be associated with configurations.userid - not sure how to do that. It need to do something like this: Fetch all configurations.* where configurations.userid = user.id AND WHERE user.smart=1, for the user creating each configuration. – Kenneth Poulsen Dec 02 '19 at 08:19
  • 1
    I fixed it using this: https://stackoverflow.com/questions/13155462/fetch-posts-from-non-blocking-users – Kenneth Poulsen Dec 02 '19 at 09:37