0

I have these three tables,

page table

page_id    page_title
1          a

content table

content_id   content_text
1            text one
2            text two

content structure table

page_id     content_id     order_in_page
1           1              1
1           2              2

my working sql,

SELECT 
    p.*,
    c.*,
    x.*

FROM pages AS p

LEFT JOIN pages_structures AS x
ON x.page_id = p.page_id

LEFT JOIN  pages_contents AS c
ON c.content_id = x.content_id

WHERE p.page_url = 'a'

result,

page_id   page_title   content_text    order_in_page
1         a            text one        1    
1         a            text two        2

the result I am after

page_id   page_title   content_1   content_2    content_3    content_4
1         a            text one    text two     null         null

How can I make the multi-row result into a single row result?

Or maybe the multi rows result is better and faster than a single row result that I want?

EDIT:

the reason I want a single row result is that I can just call the content_# by doing this,

echo $page['content_1'];

just like I call the title,

echo $page['page_title'];
Run
  • 54,938
  • 169
  • 450
  • 748
  • 1
    Why would you want to do this? The proper way would be to do it as your result is returned and just loop through the results handling them proper that way. As far as I am aware, without doing a query for each item, what you are after is not possible and if it was would be slower than molasses. Perhaps explaining what you are after will yield you with a better solution. – Jim Feb 17 '12 at 21:44
  • Thanks for the reply. Please see my edit above. Let me know if it does not make sense. Thanks. – Run Feb 17 '12 at 21:50
  • 1
    Why would you want to do this? Because a set-based solution is often preferable to a recursive one. This is the difference between thinking of a solution in a set-based way and the standard recursive programming approach. – Ben English Feb 17 '12 at 22:06
  • sorry, what is a 'recursive one' and 'standard recursive programming approach'? Anywhere I can look into these further? – Run Feb 17 '12 at 22:09

2 Answers2

2

Again, your thinking is in the wrong spot, this, if it is possible to do, would be discouraged, inefficient and probably is not possible.

Instead, handle the data in the loop and do with it what you want there, it may seem like it is more inefficient in the loop, but I assure it is not.

foreach ($dataFromSQL as $data) {
    $page[] = $data;
}

// Later on when you want to access it:
echo $page[0]['page_title'];
echo $page[0]['content_text'];

Where 0 would be the first row, 1 would be the second etc.

Jim
  • 18,673
  • 5
  • 49
  • 65
  • Thanks Brad. Maybe I should do this way. I am considering it. – Run Feb 17 '12 at 21:58
  • It is definitely possible to do and more efficient than a cursor/loop that you are proposing. – Ben English Feb 17 '12 at 21:59
  • @Ben, I see. I guess it all depends on if he plans on just having the two contents or not, I do not see the MAX IF being feasible if it is dynamic content that can continue growing, but I could be wrong. – Jim Feb 17 '12 at 22:01
  • If you want dynamic use `GROUP_CONCAT` – Ben English Feb 17 '12 at 22:04
  • @Ben I didn't downvote you...and who needs to grow up with a comment like that? – Jim Feb 17 '12 at 22:54
  • @Ben I did? That is interesting, mind showing me the proof of that, a screen shot would suffice too, just for giggles, I posted my view of the page, which of course I could have "Retracted" my mythical downvote, but then you would see it huh? Here is a screen shot of the page for my proof: http://imgur.com/txVtD Thanks for acting childish over a downvote though. – Jim Feb 17 '12 at 23:00
1

You can do this quite easily using GROUP BY with an IF statement for each column you want to transpose. When MAX evaluates it will always evaluate a non-null value as greater than NULL. 

SELECT
   page_id,
   page_title, 
   MAX(IF(order_in_page = 1, content_text, NULL)) AS content_1,
   MAX(IF(order_in_page = 2, content_text, NULL)) AS content_2,
   .
   .
   .
FROM 
   pages AS p LEFT JOIN 
   pages_structures AS x ON x.page_id = p.page_id LEFT JOIN  
   pages_contents AS c ON c.content_id = x.content_id
WHERE
   p.page_url = 'a'
GROUP BY page_id
Ben English
  • 3,900
  • 2
  • 22
  • 32
  • Thanks Ben. It looks great! Going to try it out! – Run Feb 17 '12 at 21:58
  • this should generally be faster than a recursive approach. – Ben English Feb 17 '12 at 22:07
  • sorry, I have just asked this question above - what is a 'recursive approach'? Is looping like Brad's a 'recursive approach'? – Run Feb 17 '12 at 22:10
  • 1
    the standard approach or a recursive approach is something you are likely already familiar with. This is generally in programming when you loop through data and apply operations to each data item. A set based approach is what you are doing when you are manipulating data sets with a sql query. With a set based approach you are applying your logic to the set as a whole, essentially defining what will be contained in the set. Here's a decent [link on set based thinking](http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/thinking-set-based-or-not.aspx) – Ben English Feb 17 '12 at 22:31
  • Thanks for explaining this! I think set-based is better :-) – Run Feb 17 '12 at 22:49
  • @Ben, I am not sure on this, tbh. It seems like you are trying to make a relational database into a spreadsheet, albiet that seems to be what the OP wants. I plan on reading that set-based thinking to try and wrap my head around it more. But where I am getting hung up on is where does it end? content_1002 ? And how would you pull that data, an example would be nice just so I can maybe get my head wrapped around it more. Sure if the OP just wants 1-4 content's I can see this approach being better. But yea, just trying to see where you are coming from / going to with this. – Jim Feb 17 '12 at 22:52
  • 1
    The problem you're proposing (how is this going to look if he wants 1000+ pages or something) isn't the set based problem. The set-based problem/solution is how can I get the desired result from this database using set based logic. Too many developers that learned their OOP in another language come to SQL and try and apply the same logic and problem solving methods. This is the wrong approach. Whenever possible always try and use a set-based method to solve a query problem. – Ben English Feb 17 '12 at 23:02
  • There are dynamic set-based solutions, they're just more complex. For the problem he proposed (4 columns transposed) this is the simplest solution. There are certainly more complex (and sometimes less ideal) solutions that don't require abandoning set based logic. [group_concat](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) for one, and there are others (XML PATH, PIVOT,...) Now in some cases a more dynamic solution might be better off implemented outside of SQL in the Application Logic, but for a simple case like the OP it's not necessary. – Ben English Feb 17 '12 at 23:05
  • 1
    Thanks for the extra information, I am still confused on it all, but will get back after I give some items a reading and look into a bit more. As a side note of something I found, wouldn't php pivot tables be a better approach here? – Jim Feb 17 '12 at 23:08
  • I agree with Brad here. Pivoting it in the SQL like this is extremely fragile and subject to losing data should there come a time when there is more than X number of pages returned. Or heck, what happens if two pages are both page 2 by mistake? You take the `MAX` of it! By pivoting in the data access layer of the application, you don't need to care about how many comments there are, you can build it programmatically. Which will also bring you back your data much faster than the suggested approach here. – judda Feb 17 '12 at 23:18
  • for four columns? I wouldn't say it was better. For a dynamic solution maybe. @judda while that may be more bulletproof it's definitely not going to be faster and you can PIVOT in sql. – Ben English Feb 17 '12 at 23:27
  • @Ben, regarding the 4 columns, I guess we both are/were assuming something that the OP failed to clarify. I am going on the basis that there will be more than 4 columns at any given time, you are going off that it will always be 4. So yea :) Since you were marked as solved that does seem to be the answer for him and power to ya for coming up with it. Thanks for the information. – Jim Feb 17 '12 at 23:32
  • Thanks for all of these. I only set the content up to 4 currently, so set-based works great for now. I am also interested in the solution where the content may grow in the future. Cheers – Run Feb 17 '12 at 23:55