title: SQL Select id's from PHP serialised array
body: <p>I want to create a database view to act as a pivot table in a many to many relationship between <code>pages</code> and <code>posts</code>.</p>
  <p>The data is stored by Wordpress Advanced custom fields as a serialised array:</p>
  <p><code>wp_postmeta</code> tabel:</p>
  <pre><code>+---------+---------+---------------------+-----------------------------------------------+
  | meta_id | post_id | meta_key            | meta_value                                    |
  +---------+---------+---------------------+-----------------------------------------------+
  | 155     | 47      | page_content_blocks | a:3:{i:0;s:2:&quot;29&quot;;i:1;s:2:&quot;42&quot;;i:2;s:2:&quot;32&quot;;} |
  +---------+---------+---------------------+-----------------------------------------------+
  </code></pre>
  <p>What the &quot;pivot&quot; view needs to look like:</p>
  <pre><code>+----+---------+---------+
  | id | page_id | post_id |
  +----+---------+---------+
  | 1  | 47      | 29      |
  | 2  | 47      | 42      |
  | 3  | 47      | 32      |
  +----+---------+---------+
  </code></pre>
  <p>I have tried getting there with this:</p>
  <pre class="lang-sql prettyprint-override"><code>select post_id as page_id,
         regexp_substr(meta_value, '(?&lt;=i:[0-9];s:[0-9]:&quot;)([0-9]+)(?=&quot;;)') as post_id
  from wp_postmeta
  where meta_key = 'page_content_blocks';
  </code></pre>
  <p>But that gives me only a row for the first match. I have no idea how to loop through all matches and create a row for each.</p>
  <p>Does anyone have any idea how to get this working? I know it can easily be done in PHP but that is not an option in this case.</p>