JeremyCanada26 Posted October 31, 2010 Share Posted October 31, 2010 This is my first time really working with more than 1 or two tables in a mysql database and I'd like to know what is the best way to go about querying my data so that i'm doing it in at least a somewhat efficient manner that the way I am currently doing it. First I will list my Tables and pastebin the Table creation code so you can get an idea of what I'm working with. Graff_Users, Drawings, Drawings_Stamps, Stamps, Packages Ok, those are the tables, the Graff_Users is the main table. The way I've got things setup is that I first do a query on the Graff_Users table for any uid that I want to lookup data for. Then, based on whether the artist column is equal to 1 will determine if there are Drawings to fetch, at least that is how I've been doing it and I dont feel like redesigning something that works so well for me right now. so everything is fine when artist=0 or null or anything but 1. But my trouble starts if artist=1, then I have to query a whole lot of data here, let me explain it. Ok, if artist=1, currently, I query the Drawings table to find any drawings where the house_owner_id is equal to the uid of the User. This leads me to a list of Drawings rows. Next, I check which of those rows has stamps_used=1 for each Drawing and gather up a list of id's of the Drawings that have that set to 1. Once I have the list of id's of the Drawings, I query the Drawings_Stamps table for rows where the drawings_id is equal to any of those id's. Then once I have that, I use the stamp_id's from those and do another query on the Stamps table to get all of the stamps matching those id's. Then each Stamp also contains an author_id so I gather those id's up and do another query back on the Graff_Users table to get the first_name,last_name and picture. Once I have all of that data in various array throughout my script, i create a new array and start putting the pieces back togather from the various arrays until I am left with an array that looks like this one here(JSON used for visualization here) { "request_type": 1, "target_uid": "1234567", "data": { "id": "277", "uid": "1234567", "first_name": "Jeremy", "last_name": "Canada", "picture": "http://www.pictures.com/mypic.png", "signed_up": "2010-10-29 03:47:09", "selected_house": "10", "mp3": "1", "emblem_id": "2", "artistData": [ { "id": "210", "artist_id": "1234567", "house_owner_id": "1234567", "anonymous": null, "stamps_used": "1", "timestamp": "2010-09-23 00:52:42", "note": "wanna dance?", "first_name": "Jeremy", "last_name": "Canada", "picture": "http://blahblah.com/picture.png", "stampsArray": [ { "id": "250", "name": "", "author_id": "654321", "created": "2010-09-23 23:40:22", "keywords_id": null, "packages_id": "27", "public": "1", "package_category_id": "0", "filename": "drawingitems\/663231980_1285285221_445643.png", "number_of_users": "0", "description": "just another stamp my friend", "rating": "0", "thumbs_up": "0", "thumbs_down": "0", "first_name": "Cat Man", "last_name": "Blues", "picture": "http:\/\/pic.com\/_q.jpg", "packageData": { "id": "27", "name": "Circles Of cool", "description": null, "total_stamps": "6", "number_of_users": "0", "rating": "0", "thumbs_up": "0", "thumbs_down": "0" } }, { "id": "249", "name": "", "author_id": "1680999989", "created": "2010-09-23 23:38:28", "keywords_id": null, "packages_id": "26", "public": "1", "package_category_id": "0", "filename": "drawingitems\/663231980_1285285108_441450.png", "number_of_users": "0", "description": null, "rating": "0", "thumbs_up": "0", "thumbs_down": "0", "first_name": "Raymond", "last_name": "Coldstar", "picture": null, "packageData": { "id": "26", "name": "The nice pics pack", "description": "description of the package would go here", "total_stamps": "123", "number_of_users": "234", "rating": "3", "thumbs_up": "2", "thumbs_down": "7" } } ] }, { "id": "212", "artist_id": "4", "house_owner_id": "1234567", "anonymous": "0", "stamps_used": "1", "timestamp": "2010-07-09 03:45:59", "note": "shake it baby", "stampsArray": [ { "id": "248", "name": "flying stick man 1", "author_id": "663231980", "created": "2010-09-23 23:37:08", "keywords_id": "6", "packages_id": "26", "public": "1", "package_category_id": "0", "filename": "drawingitems\/663231980_1285285027_288422.png", "number_of_users": "1200", "description": "stamp description would go here", "rating": "6734", "thumbs_up": "18", "thumbs_down": "22", "first_name": "Gary", "last_name": "Hoss", "picture": "http:\/\/profiles.com\/63231980_1168_q.jpg", "packageData": { "id": "26", "name": "The nice pics pack", "description": "description of the package would go here", "total_stamps": "6546345", "number_of_users": "234", "rating": "3", "thumbs_up": "2", "thumbs_down": "7" } }, { "id": "249", "name": "", "author_id": "1680999989", "created": "2010-09-23 23:38:28", "keywords_id": null, "packages_id": "26", "public": "1", "package_category_id": "0", "filename": "drawingitems\/663231980_1285285108_441450.png", "number_of_users": "0", "description": null, "rating": "0", "thumbs_up": "0", "thumbs_down": "0", "first_name": "Raymond", "last_name": "Blues Brutha", "picture": null, "packageData": { "id": "26", "name": "The nice pics pack", "description": "description of the package would go here", "total_stamps": "6543534", "number_of_users": "234", "rating": "3", "thumbs_up": "2", "thumbs_down": "7" } } ] }, { "id": "221", "artist_id": "663231980", "house_owner_id": "1234567", "anonymous": null, "stamps_used": null, "timestamp": "2010-09-13 07:35:25", "note": "hi jeremy", "first_name": "Coolio", "last_name": "Stars", "picture": "http:\/\/nowhere.com\/1980_1168_q.jpg" }, { "id": "227", "artist_id": "4321", "house_owner_id": "1234567", "anonymous": null, "stamps_used": null, "timestamp": "2010-09-24 01:24:19", "note": "" } ] } } Quote Link to comment Share on other sites More sharing options...
JeremyCanada26 Posted October 31, 2010 Author Share Posted October 31, 2010 Woops, I just realized I didn't even pastebin the Table creation mysql link. Here it is, http://pastebin.com/LudLfvKz Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.