1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

1 to Many Query

Discussion in 'Databases' started by scottlpool2003, Jan 22, 2014.

  1. #1
    I'm currently upgrading our websites to run on PHP 5.4 so while I'm at it, I'm optimizing as much as possible.

    Currently we have news stories that have images. The stories table is 1 to many with the images table. The way it's pulling at the moment is running 2 queries, so I'm trying to do it in 1 query.

    Stories:
    id
    subject
    body
    SEMrush
    Images:
    id
    story_id
    name

    Any help appreciated
     
    scottlpool2003, Jan 22, 2014 IP
    SEMrush
  2. khodem

    khodem Member

    Messages:
    201
    Likes Received:
    5
    Best Answers:
    3
    Trophy Points:
    45
    #2
    what kind of out your looking for give an example then only I can assist you
     
    khodem, Jan 24, 2014 IP
  3. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,709
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #3
    stories.id, stories.subject, stories.body, images.name

    Story has many images. Is it a GROUP BY stories.id or something similar I need?
     
    scottlpool2003, Jan 24, 2014 IP
  4. khodem

    khodem Member

    Messages:
    201
    Likes Received:
    5
    Best Answers:
    3
    Trophy Points:
    45
    #4
    if I get it im not sure as it's not that much clear to me you can use join i.e
    select s.id,s.stories,s.body,i.name
    from stories s
    join image i on id=id

    this is idea that you can use to create your main query... your solution is join
     
    khodem, Jan 24, 2014 IP
  5. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,709
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #5
    This pulls out the all of the stories per image though, that's the problem I'm having... I could write PHP to only use 1 of them but say there's 10 images to 1 story, I don't want to pull out 10 stories with 10 images... I want 1 story with the corresponding images from the image table. Running 2 queries would be less resource intensive than pulling out the same data 10 times...
     
    scottlpool2003, Jan 27, 2014 IP
  6. philnet

    philnet Well-Known Member

    Messages:
    180
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    103
    #6
    I think it'd be simpler (if you're writing the code), to stay running the way you are (ie fetch the story and then have a dedicated function to fetch the relevant images).
    I'm coming from a more SQL Server based background and I know from experience that the database engine will compile an execution plan for queries such as this so we'd always fetch the related items from a separate function. You've not said what your back end database is but if it's mysql, you could always wrap the whole query in a stored procedure (if you haven't already!)
     
    philnet, Jan 27, 2014 IP
  7. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,709
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #7
    Thanks, I've never really played with stored procedures before so I'm not sure how this would benefit what I'm trying to achieve. Could you elaborate a little on how this would help? Cheers
     
    scottlpool2003, Jan 28, 2014 IP
  8. philnet

    philnet Well-Known Member

    Messages:
    180
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    103
    #8
    Sure, simply put, a stored procedure is like a function in programming terms, it has parameters and returns data.
    In your case, you could create a proc along the lines of usp_fetchImages(intNewsItemID) which internally does something along the lines of "select * from newsimages where newsitem_id=parameter" and then returns a recordset (in sql server terms) of data relating to your images.
    We always use stored procedures for a couple of reasons:
    • They allow you to control database security as you can just grant access to the stored procedure, removing direct application access from the underlying tables
    • In SQL Server at least, the execution plan for the stored procedure is stored by the server making subsequent executions more efficient than just repeatedly calling "select * from...."
     
    philnet, Jan 28, 2014 IP
  9. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,709
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #9
    Thanks, so am I correct in thinking that this creates a cached version of the stored procedure and I can use PHP to grab that 'already grabbed' data set?
     
    scottlpool2003, Jan 28, 2014 IP
  10. philnet

    philnet Well-Known Member

    Messages:
    180
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    103
    #10
    No, It doesn't cache the data, it caches how to best fetch the data (at the database engine level). This will mean that after the first execution, the database engine will (or should have) figured out the best way to retrieve the data so subsequent fetches will be faster, something that is much harder to do with ad hoc sql queries.
     
    philnet, Jan 28, 2014 IP
  11. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,709
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #11
    I'm trying to create a stored procedure but all I run into are MySQL errors I've never had before:

    This one is select all from stories, but I originally did it with the stories/images query although it didn't work so I decided to test it with select *. Any ideas?
    
    delimiter ;;
    
    drop procedure if exists test2;;
    
    create procedure test2()
    
    begin
    
    SELECT * FROM stories
    
    end
    
    ;;
    
    PHP:
     
    scottlpool2003, Jan 28, 2014 IP
  12. khodem

    khodem Member

    Messages:
    201
    Likes Received:
    5
    Best Answers:
    3
    Trophy Points:
    45
    #12
    there is many error in your procedure here is simple code I found which will help you out... it's very simple and yet deliver what it's need to be done:

    CREATE PROCEDURE productpricing(
       OUT pl DECIMAL(8,2),
       OUT ph DECIMAL(8,2),
       OUT pa DECIMAL(8,2)
    )
    BEGIN
       SELECT Min(prod_price)
       INTO pl
       FROM products;
       SELECT Max(prod_price)
       INTO ph
       FROM products;
       SELECT Avg(prod_price)
       INTO pa
       FROM products;
    END;
    Code (markup):
    and this is how you call this procedure:

    CALL productpricing(@pricelow,
                        @pricehigh,
                        @priceaverage);
    Code (markup):
    incase you want this to be explained in details this is the link http://www.brainbell.com/tutorials/MySQL/Using_Stored_Procedures.htm

    cheers
     
    khodem, Jan 28, 2014 IP
  13. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,709
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #13
    Does this work with MySQL or SQL? I can't seem to be able to create the stored procedure even as basic as:

    
    CREATE PROCEDURE getStories()
    BEGIN
       SELECT id, subject, body
       FROM stories;
    END;
    
    Code (markup):
     
    scottlpool2003, Jan 29, 2014 IP
  14. philnet

    philnet Well-Known Member

    Messages:
    180
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    103
    #14
    Take a look at http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx (I don't seem able to add proper links yet) which seems like a pretty good tutorial on getting started with MySQL stored procedures.
     
    philnet, Jan 29, 2014 IP
  15. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,709
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #15
    I've had a look but I don't think I can get the desired result. Here is a solid view of what I want:

    Images
    ___________________________
    | id | story_id | name |
    --------------------------------------
    | 180 | 1726 | image249.jpg |
    --------------------------------------
    | 181 | 1727 | image250.jpg |
    --------------------------------------
    | 182 | 1727 | image250(2).jpg |
    ---------------------------------------
    | 183 | 1727 | image250(3).jpg |
    ---------------------------------------
    | 184 | 1743 | image292.jpg |
    --------------------------------------

    Stories
    ______________________________________________________
    | id | subject | body | dateposted |
    -----------------------------------------------------------------------------
    | 1726 | Hello World | Hey, just wanted to... | 2012-08-13 09:26:28 |
    -----------------------------------------------------------------------------
    | 1727 | You've Be... | Hello, you have bee... | 2012-08-13 09:26:28 |
    -----------------------------------------------------------------------------
    | 1743 | I don't k... | Wow, Don't know if ... | 2012-08-13 09:26:28 |
    -----------------------------------------------------------------------------
    | 1744 | Dear frie... | Hey friend, did you... | 2012-08-13 09:26:28 |
    -----------------------------------------------------------------------------
    | 1745 | Lipsum lo.. | Ipsum lorem ipsum ... | 2012-08-13 09:26:28 |
    -----------------------------------------------------------------------------

    The only way I can pull the data out is like this:
    ________________________________________________________________________
    | id | subject | body | dateposted | name |
    -----------------------------------------------------------------------------------------------------
    | 1727 | You've Be... | Hello, you have bee... | 2012-08-13 09:26:28 | image250.jpg |
    ------------------------------------------------------------------------------------------------------
    | 1727 | You've Be... | Hello, you have bee... | 2012-08-13 09:26:28 | image250(2).jpg |
    ------------------------------------------------------------------------------------------------------
    | 1727 | You've Be... | Hello, you have bee... | 2012-08-13 09:26:28 | image250(3).jpg |
    ------------------------------------------------------------------------------------------------------

    Unless I run 2 separate queries (which I'm currently doing) but I guess I'm just looking for best practice. The above result is pulling the same data out 3 times, this is a waste...

    Somebody help!
     
    scottlpool2003, Jan 29, 2014 IP
  16. philnet

    philnet Well-Known Member

    Messages:
    180
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    103
    #16
    I would still run through two separate queries but make sure the queries are wrapped in a stored procedure. I wouldn't try getting all the data in one hit.
     
    philnet, Jan 30, 2014 IP
  17. Nitin.Mail

    Nitin.Mail Well-Known Member

    Messages:
    456
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    135
    #17
    I believe you can use partition by to get subset groups.
     
    Nitin.Mail, Feb 9, 2014 IP