0

Problem: In my mysql database, I have two tables lists and shares. The lists table has all of the to do lists that users have created:

'lists' Table

The shares table stores all lists that have been shared from one user to another:

'shares' table

What I want to do is get all lists for a user, both the ones they've created as well as the shared ones they have access to. Currently, I have a select statement that works perfectly when there is an entry in the shares table (doesn't matter if it relates to the specific user I'm retrieving results for).

As soon as I remove the result from the shares table and try to use that same multi-table SELECT statement, I get no results and I can't figure out why.

Here's the SELECT statement that I'm using:

SELECT DISTINCT `lists`.* FROM `lists`,`shares` WHERE `lists`.user_id = '$userid' OR (`shares`.sharedwith_id = '$userid' AND `lists`.id = `shares`.list_id) ORDER BY `lists`.datecreated DESC

Any help with the query would be awesome. I've looked at JOINs but haven't seen anything that would be more efficient that what I have.

Vaughn Dabney
  • 94
  • 8

1 Answers1

1

You really should be using JOIN instead of WHERE clause:

SELECT DISTINCT `lists`.* 
FROM `lists`
LEFT JOIN `shares`
ON `lists`.`id`=`shares`.list_id AND `lists`.`user_id`=`shares`.`sharedwith_id`
WHERE `lists`.`id` = ?

Finally, avoid SQL injection attacks: never embed arguments in your SQL. Pass your arguments as parameters.

Stavr00
  • 3,219
  • 1
  • 16
  • 28
  • Thanks, I'll try this. Any resources you can point me to about injection that you would recommend? Although, this is coming from a functions file that is not readily available via web (powering an app), I'd like to learn more about the topic! – Vaughn Dabney Jun 02 '16 at 15:59
  • Had to modify the query a bit to the below statement but it works! Thanks for the help. – Vaughn Dabney Jun 02 '16 at 16:19