Databases
StackOverflow2013
Postsdbo
POMySQL join across two tables with serialized data, recursion?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL join across two tables with serialized data, recursion?
    text
    copied!<p>I'm having some major issues trying to grab all the data I need for a SQL query. I'm still new with queries, so I will try to describe this as best as possible. </p> <p>I am trying to do a cross query with the Wordpress plugin NextGen Gallery. Basically there are two tables <code>nggalbum</code> and <code>nggallery</code>. What I'm attempting to do is create a nested list of all albums and galleries.</p> <p>The data in <code>nggalbum</code> contains columns <code>id</code>, <code>name</code>, <code>slug</code>, <code>previewpic</code>, <code>albumdesc</code>, <code>sortorder</code>, and <code>pageid</code>. The only values that I'm interested in are <code>id</code>, <code>name</code>, <code>slug</code>, and <code>sortorder</code>. The value for <code>sortorder</code> is serialized data that has the relationship data of this entry and all other album and gallery entries. For example: <code>a:2:{i:0;s:2:"a2";i:1;s:2:"a6";}</code> This basically means the current query item has two sub-albums (their corresponding id prefixed with an "a"): <code>a2</code> and <code>a6</code>. If it has galleries, the number doesn't have an <code>a</code> prefix and is the <code>ngggallery</code> <code>gid</code> (will cover that in a second).</p> <p>I use this to grab all the data from the <code>nggalbum</code> table:</p> <pre><code> $albums = $wpdb-&gt;get_results("SELECT * FROM $wpdb-&gt;nggalbum" , OBJECT_K ); foreach ($albums as $key =&gt; $value) { $albums[$key]-&gt;id = 'a' . $key; $albums[$key]-&gt;galleries = empty ($albums[$key]-&gt;sortorder) ? array() : (array) unserialize($albums[$key]-&gt;sortorder) ; $albums[$key]-&gt;name = stripslashes( $albums[$key]-&gt;name ); $albums[$key]-&gt;albumdesc = stripslashes( $albums[$key]-&gt;albumdesc ); } </code></pre> <p>Sample data:</p> <pre><code>Array ( [1] =&gt; stdClass Object ( [id] =&gt; a1 [name] =&gt; Image Gallery [slug] =&gt; image-gallery [previewpic] =&gt; 0 [albumdesc] =&gt; [sortorder] =&gt; a:2:{i:0;s:2:"a2";i:1;s:2:"a6";} [pageid] =&gt; 0 [galleries] =&gt; Array ( [0] =&gt; a2 [1] =&gt; a6 ) ) [2] =&gt; stdClass Object ( [id] =&gt; a2 [name] =&gt; ALBUM 1 - High res [slug] =&gt; album-1-high-res [previewpic] =&gt; 0 [albumdesc] =&gt; [sortorder] =&gt; a:2:{i:0;s:1:"2";i:1;s:1:"3";} [pageid] =&gt; 0 [galleries] =&gt; Array ( [0] =&gt; 2 [1] =&gt; 3 ) ) </code></pre> <p>I add an <code>a</code> prefix to all of these id's because they are albums and I <em>figured</em> this may help later. Since I'm not sure what I'm doing, this may not be the case.</p> <p><code>nggallery</code> contains columns <code>gid</code>, <code>name</code>, <code>slug</code>, <code>path</code>, <code>title</code>, <code>galdesc</code>, <code>pageid</code>, <code>previewpic</code>, and <code>author</code>. The only relevant columns are <code>gid</code>, <code>name</code>, <code>slug</code>, <code>path</code>, and <code>title</code>.</p> <p>I use this to grab all the data from the <code>nggallery</code> table:</p> <pre><code>$galleries = $wpdb-&gt;get_results( "SELECT SQL_CALC_FOUND_ROWS * FROM $wpdb-&gt;nggallery", OBJECT_K ); foreach ($galleries as $key =&gt; $value) { $galleriesID[] = $key; $galleries[$key]-&gt;counter = 0; $galleries[$key]-&gt;title = stripslashes($galleries[$key]-&gt;title); $galleries[$key]-&gt;galdesc = stripslashes($galleries[$key]-&gt;galdesc); $galleries[$key]-&gt;abspath = WINABSPATH . $galleries[$key]-&gt;path; } </code></pre> <p>Sample data:</p> <pre><code>Array ( [2] =&gt; stdClass Object ( [gid] =&gt; 2 [name] =&gt; new_collection [slug] =&gt; new-collection [path] =&gt; wp-content/gallery/new_collection [title] =&gt; NEW COLLECTION [galdesc] =&gt; [pageid] =&gt; 0 [previewpic] =&gt; 8 [author] =&gt; 1 [counter] =&gt; 0 [abspath] =&gt; /Applications/MAMP/htdocs/igal/wp-content/gallery/new_collection ) [3] =&gt; stdClass Object ( [gid] =&gt; 3 [name] =&gt; cha-collection [slug] =&gt; cha-collection [path] =&gt; wp-content/gallery/cha-collection [title] =&gt; CHA COLLECTION [galdesc] =&gt; [pageid] =&gt; 0 [previewpic] =&gt; 15 [author] =&gt; 1 [counter] =&gt; 0 [abspath] =&gt; /Applications/MAMP/htdocs/igal/wp-content/gallery/cha-collection ) </code></pre> <p>Now this is where I get stuck. I would really love to be able to write some code to parse through each album's <code>galleries</code> array and associate it with the corresponding albums and/or galleries from <code>nggallery</code>. </p> <p>Eventually I would love to achieve a nested list of albums/galleries, such as:</p> <pre><code>(a1) [link] Title (a2) [link] Title 1 [link] Title 2 [link] Title 3 [link] Title (a3) [link] Title 1 [link] Title [...] </code></pre> <p>I'm not entirely sure how to even start going about this. I tried looping some things through some <code>foreach</code> statements and have been largely unsuccessful. I would search google for this but I have no idea what this technique is even called.</p> <p>I would <strong>REALLY</strong> like to understand how to do something like this, so if you could please shed any light upon this technique, I would be greatly indebted. Links to similar tutorials or just basic concepts would be very beneficial to me. I don't expect anyone to do all the code for me, but any step in the right direction would be greatly appreciated ( and if you want to do the code, with some steps, I won't argue of course ;) ). </p> <p>Thanks so much ahead of time!</p> <p>Tre</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload