1

Im trying to create a checklist system eg. a list of items to collect. the user will be able to add the list to their profile and then as they collect the items then can check the box for an item in the list click submit and the checked item will now be marked as collected. I have it coded and working fine but it makes an insane amount of queries to the database to work.

i have 5 tables. a users table (for the registered users username, id..etc), a lists table (containing the list name, description, id ), a list items table (containing the individual items. id, title and listID (to reference the list it belongs to)). userslist table (for the lists the user has added to their profile. userID and listID) and collecteditems table (this has the list items that a person has checked the box for as collected. listItemsID, UserID)

the problem is when i view the mylists.php page it will query the userslist table and return all the ID's for the lists the user has added. then once i have the ID for the list it then queries the list table to find out what the name of the list is (this could mean having to make 10 queries to the list table if i have 10 different lists added). if i added a listname column to the userslist table i would only need to make 1 query for the page and that is to the userslists table and i could construct the page with that 1 query.

Dan Hastings
  • 3,241
  • 7
  • 34
  • 71
  • If users are allowed to rename lists, you could store that in the `userlist` table. Otherwise, premature optimizationis the root of much evil, even though it may be fun. – tc. Feb 23 '13 at 16:54
  • Not really relevant to the question but the `collecteditems` table could alternatively have `(ListID, ItemsID, UserID)` as columns. That way you could enforce Referential Integrity. The way you have the table now, you may well end with a user having collected an item that belongs to a list that is not in that user's lists. – ypercubeᵀᴹ Feb 23 '13 at 16:58

2 Answers2

1

It sounds like you should keep your data normalized (that is, avoid the redundant data) and instead gather all the data you want in a single query, by using a JOIN.

Matt Ball
  • 354,903
  • 100
  • 647
  • 710
1

First, I wouldn't worry about queries on primary keys. All your tables have a primary key referenced by other tables. These will use joins.

Second, you don't have to get the list names separately. Use a query such as:

select l.listName
from UserLists ul join
     Lists l
     on ul.listId = l.listId
where userId = $userid

This will return all the names in a single query.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    This is solid aside from the part where it's begging for a SQL injection attack. – Matt Ball Feb 23 '13 at 16:50
  • @MattBall . . . It is good advice to use parameterized queries/prepared statements for such an application. This is a good explanation of that topic . . . http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php. – Gordon Linoff Feb 23 '13 at 16:56
  • thanks a lot. also thanks for the link above aswell i hasnt been using prepared statements i will go and do that now – Dan Hastings Feb 23 '13 at 17:05