SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Stored Procedure

    Hello all,

    I have read some tutorials on MYSQL stored procedures and find it very difficult to get my head around it. My first query is:

    Code MySQL:
    SELECT aChild.id, aChild.type, GROUP_CONCAT(aClient.client_id) AS `client_ids`, count(aClient.client_id) AS `count`
    			FROM Asset_Child AS aChild 
    			JOIN Asset_Client AS aClient ON 
    				(
    					aChild.`type` = aClient.attachment_type 
    					AND aClient.attachment_id = aChild.id 
    					AND aClient.enabled = 1 
    					AND aChild.`status` = 1
    				) 
    			WHERE aClient.client_id IN (1,2)
    			GROUP BY aChild.id, aChild.type
    			HAVING count = 2
    			ORDER BY aChild.created DESC
    			LIMIT 0, 15

    This gives me an array similar to this:
    [id: 5, type: 'image'], [id: 6, type: 'text'], [id: 7, type: 'link'], [id: 8, type: 'image'], [id: 9, type: 'text'], [id: 11, type: 'link']

    I then loop through this and load the individual rows like this:

    Code MySQL:
    SELECT * FROM Asset_Image WHERE id = 5;
    SELECT * FROM Asset_Text WHERE id = 6;
    SELECT * FROM Asset_Link WHERE id = 7;

    I would like to cut down the number of queries being issued and clean up the code, how would I use a stored procedure for this?

    BTW I'm very aware it sounds like Im begging for an answer, thats not the case, I just need a push in the right direction

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by audienceWatching View Post
    I would like to cut down the number of queries being issued and clean up the code, how would I use a stored procedure for this?
    you wouldn't

    you can eliminate those extra queries by simply joining those tables to your original query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    three left joins? You cant do if statements in a query? Besides the attachment_type is marked as 'image', 'link' and 'text', the tables are called Asset_Text, Asset_Link and Asset_Image

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes, left outer joins to implement your "if" capability, because the asset is going to match only one of those tables, right?

    the tables it doesn't match will have nulls returned in those columns, so your php code would display only the non-null stuff
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, Ive made this query:

    SELECT
    aChild.`id`, aChild.`type`, GROUP_CONCAT(aClient.`client_id`) AS `client_ids`, count(aClient.`client_id`) AS `count`,
    assetImage.text, assetImage.label, assetImage.source,
    assetText.text, assetText.label,
    assetLink.text, assetLink.label
    FROM Asset_Child AS aChild
    JOIN Asset_Client AS aClient ON
    (
    aChild.`type` = aClient.`attachment_type`
    AND aClient.`attachment_id` = aChild.`id`
    AND aClient.`enabled` = 1
    AND aChild.`status` = '.$status.'
    )
    LEFT JOIN Asset_Text AS assetText ON assetText.id = aChild.id
    LEFT JOIN Asset_Link AS assetLink ON assetLink.id = aChild.id
    LEFT JOIN Asset_Image AS assetImage ON assetImage.id = aChild.id
    WHERE aClient.`client_id` IN ('.implode(',', $clientIds).')
    GROUP BY aChild.`id`, aChild.`type`
    HAVING count = ' . count($clientIds).'
    ORDER BY aChild.`created` DESC
    LIMIT '.$from.', '.$to
    Im not sure this is optimised correctly?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by audienceWatching View Post
    Im not sure this is optimised correctly?
    start with something more important -- does it work correctly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It does

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, next question, does it run fast?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    3.0ms for 11 results

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    seems optimized to me... how about you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah I would say so, thank you!


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •