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