0

I'm trying to build url's from Word Press Post/Pages. I'm able to connect to the database and retrieve the data I need. However some pages have parents, which are just other pages, for example:

|id|post_name|post_parent|post_status|url
|1 |post_1   |0          |publish    |post_1
|2 |post_2   |3          |publish    |post_3/post2
|3 |post_3   |0          |draft      |post_3

What I want to be able to do is build the url column using a MySQL query. At the moment I'm using some c# code to do it, however this should be possible using SQL? The version of MySQL is 14.14 Distrib 5.1.73. I don't have any control over the version of MySQL.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
markpirvine
  • 1,485
  • 1
  • 23
  • 54
  • Unfortunately, MySQL doesn't support recursive queries until version 8.0, so if you can't upgrade, your options are pretty limited. I show an example of a recursive query and a workaround in my answer to https://stackoverflow.com/a/192462/20860 but the workaround requires storing all paths through the tree in a separate table. – Bill Karwin Dec 03 '19 at 21:42
  • You can also search past answers with the 'mysql' and 'hierarchical-data' tags: https://stackoverflow.com/questions/tagged/mysql+hierarchical-data – Bill Karwin Dec 03 '19 at 21:44
  • @BillKarwin, many thanks for your reply and the link - at the moment I'm using joins to go 4 levels deep. It's horrible, but it does whats required at the moment. Hopefully I'll have some time over the holidays to refactor! – markpirvine Dec 04 '19 at 14:09

0 Answers0