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": "" } ] } } Link to comment https://forums.phpfreaks.com/topic/217338-php-mysql-help/ 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 Link to comment https://forums.phpfreaks.com/topic/217338-php-mysql-help/#findComment-1128573 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.