0

I have two database tables with a structure as follows:

page_info

  • page_id (int)
  • parent_page_id (int)
  • page_title (varchar)

page_order

  • page_order_id (int)
  • parent_page_id (int)
  • page_id (int)
  • page_order (int)

(FYI the page_info table has been heavily cut down from what is actually there, these are the only fields at play here however.)

Is there a way to pull out the entire nested structure in a flat structure in the correct order with one, relatively fast SQL query?

The actual problem I am trying to solve is the current implementation we have is far too slow and coupled too closely to our CMS. A heavy, ugly recursive PHP function is currently used to build the data and it's causing bottlenecks in our application, therefore perhaps an SQL query to speed it up isn't necessarily the solution, but I'd love to see people thoughts nonetheless.

davo0105
  • 117
  • 8
  • MySQL can’t return nested result sets. You query the database, you get some results. If your PHP script that generates the site map is slow, consider executing it on an queue, and then caching the result. – Martin Bean Jan 11 '16 at 16:11
  • Well aware of the fact that "MySQL can’t return nested result sets". I should have been clearer in that case - I require the result set in a flat structure. – davo0105 Jan 12 '16 at 10:56
  • Is this what you want? http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Neville Kuyt Jan 12 '16 at 11:01

1 Answers1

0

Below query will return set of all pages and then using php you can use it.

SELECT page_info.page_id AS page_id, parent_page_id, page_title, page_order_id, parent_page_id, page_order
    FROM page_info INNER JOIN page_order
    ON page_info.page_id = page_order.page_id

Here you need nested loop to read it.

[NOTE : If it's not related to your question then let me know]

Kamaldeep singh Bhatia
  • 732
  • 6
  • 18