1 to Many Query

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

Images:
id
story_id
name

Any help appreciated

 

what kind of out your looking for give an example then only I can assist you

 

stories.id, stories.subject, stories.body, images.name

Story has many images. Is it a GROUP BY stories.id or something similar I need?

 

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

 

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...

 

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!)

 

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

 

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...."

 

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?

 

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.

 

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:

 

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

 

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):

 

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.

 

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!

 

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.

 

I believe you can use partition by to get subset groups.