I've got a pretty complex SQL statement and want to add a WHERE clause that only selects results WHERE the hidden
column IS NOT '1', however it needs to relate to a specific user.
i.e If in table 1 hidden
is 1 and userid
is 1 I don't want to get this results. However as there is no record for user 2 in that table I want them to see it.
This is what I have managed to get working so far:
$where .= " AND uh.hidden IS NULL ";
However if I login as User 2 then I see the same results as user 1.
How do I make it so results are shown based on the user too?
SQL query:
$pdo = new PDO('mysql:host=localhost;dbname=myDB', 'root', 'root');
$select = 'SELECT tl.id,
tl.name,
tl.locale,
ROUND(AVG(pr.rating),0) AS rating ';
$from = ' FROM theList AS tl ';
$join = ' LEFT JOIN post_rating AS pr ON tl.id = pr.postid ';
$join2 = ' LEFT JOIN user_hidden_list AS uh ON uh.est_id = tl.id ';
$opts = isset($_POST['filterOpts']) ? $_POST['filterOpts'] : [''];
$where = ' WHERE 1 = 1 ';
if (in_array("pub", $opts)) {
$where .= " AND pub = 1";
}
if (in_array("bar", $opts)) {
$where .= " AND bar = 1";
}
$where = ' WHERE uh.hidden IS NULL ';
$group = ' GROUP BY tl.id, tl.name ';
$sql = $select . $from . $join . $join2 . $where . $group;
$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);