Databases
StackOverflow2013
Postsdbo
POGenerate a tree with recursive array combining data from two tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGenerate a tree with recursive array combining data from two tables
    primarykey
    data
    text
    <p>I have to generate a tree from two MySQL tables in PHP and print it with HTML. My main problem is that I've two tables where to get data. One table is <strong>pages</strong> and other <strong>translations</strong> because I've 3 languages in the website and all the translations remains on the <strong>translations</strong> table.</p> <p>The schema of this tables is:</p> <p><strong>pages</strong></p> <pre><code>id INT id_user INT id_parent INT order INT template INT image VARCHAR flag_active INT flag_extra INT edited INT created INT </code></pre> <p><strong>translations</strong></p> <pre><code>id INT id_user INT locale VARCHAR module VARCHAR pk INT label VARCHAR value TEXT edited INT </code></pre> <p>When I add a page I the values <code>title, slug and text</code> of pages (not on the schema, because there are depending of the locale) into the translations:</p> <pre><code>INSERT INTO pages (id_user, template, image, flag_active, flag_extra, edited, created) VALUES (1, 0, 'test.jpg', 1, 1, 12345, 12345) &lt;!-- the pages.id is 4, for example, for next insert: --&gt; INSERT INTO translations (id_user, locale, module, pk, label, value, edited) VALUES (1, 'en', 'pages', 4, 'title', 'This is the title in English', 12345) INSERT INTO translations (id_user, locale, module, pk, label, value, edited) VALUES (1, 'en', 'pages', 4, 'slug', 'this-is-the-title-in-english', 12345) INSERT INTO translations (id_user, locale, module, pk, label, value, edited) VALUES (1, 'en', 'pages', 4, 'text', 'This is the text in English', 12345) INSERT INTO translations (id_user, locale, module, pk, label, value, edited) VALUES (1, 'es', 'pages', 4, 'title', 'Este es el titulo en Español', 12345) INSERT INTO translations (id_user, locale, module, pk, label, value, edited) VALUES (1, 'es', 'pages', 4, 'slug', 'este-es-el-titulo-en-espanol', 12345) INSERT INTO translations (id_user, locale, module, pk, label, value, edited) VALUES (1, 'es', 'pages', 4, 'text', 'Este es el contenido en Español', 12345) </code></pre> <p>And then when I've to access to a page in a certain language, in PHP first I select the page from the table <code>pages</code> and then look in <code>translations</code> for: <code>WHERE module='pages' AND pk='4' AND locale='en'</code> and I get all the information I need from the page and the translated text values.</p> <p>I've explained how it works my translations system. Now I've a problem in the backend (and frontend) because <strong>I need to build a tree from this pages in one language</strong>. My idea is to go throw a recursive array but I don't know how to merge the data because I think this will be a PHP thing and not a MySQL one.</p> <p>I haven't build the tree function and I think I need to tree functions because:</p> <ol> <li>I need to generate and array from the MySQL query/ies, combining the page data and the translation.</li> <li>I need to generate a <code>&lt;ol /&gt;</code> list in HTML with the nested tree.</li> </ol> <p>For the first first tree array, the MySQL one, <em>I need to make a MySQL for EACH entry</em>? Or this can be done with only one MySQL query? Code example, not tested and written directly:</p> <pre><code>function mysql_tree($parent_id = 0) { $return = array(); $query = "SELECT * FROM pages WHERE id_parent=$parent_id"; $result = mysql_query($query); if(mysql_num_rows($result) != 0) { $i = 0; while($row = mysql_fetch_array($result)) { $return[$i] = array( 'id' =&gt; $row["id"]; //etc ); // Time to merge the data for each page from translations???? $return[$i]["childs"] = mysql_tree($row["id"]); $i++; } } return $return; } </code></pre> <p>For the second tree function I suppose that something similar to the MySQL?</p> <p>Thank you in advance!</p> <p><strong>UPDATE:</strong> example of nested list requested by Vincent</p> <pre><code>Array( [0] =&gt; Array( [id] =&gt; 4 [id_user] =&gt; 1 [id_parent] =&gt; 0 [order] =&gt; 0 [template] =&gt; 1 [image] =&gt; NULL [flag_active] =&gt; 1 [flag_extra] =&gt; 0 [edited] =&gt; 12345 [created] =&gt; 12345 [title] =&gt; This is the title in English [slug] =&gt; this-is-the-slug-in-english [childs] =&gt; Array( [0] =&gt; Array( [id] =&gt; 5 [id_user] =&gt; 1 [id_parent] =&gt; 4 [order] =&gt; 0 [template] =&gt; 1 [image] =&gt; NULL [flag_active] =&gt; 1 [flag_extra] =&gt; 0 [edited] =&gt; 12345 [created] =&gt; 12345 [title] =&gt; This is the title in English 2 [slug] =&gt; this-is-the-slug-in-english-2 [childs] =&gt; NULL ) ) ) ) </code></pre> <p>And the HTML tree:</p> <pre><code>&lt;ol&gt; &lt;li class="id_4"&gt;&lt;a href="/pages/this-is-the-slug-in-english"&gt;This is the title in English&lt;/a&gt; &lt;ol&gt; &lt;li class="id_5"&gt;&lt;a href="/pages/this-is-the-slug-in-english-2"&gt;This is the title in English 2&lt;/a&gt;&lt;/li&gt; &lt;/ol&gt; &lt;/li&gt; &lt;/ol&gt; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USipalaus
    1. USipalaus
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COUmmmm.... What?
      singulars
      1. POGenerate a tree with recursive array combining data from two tables
      1. USLightness Races in Orbit
    2. CO@Tomalak: haha I tried to explain all the whole thing because people usually says that I don't describe and answer well. Resuming: I've two tables for a nested tree, that can not be joined in MySQL because it uses another procedure for storing languages, and I want to know how to do a generate the tree with the less MySQL queries possible.
      singulars
      1. POGenerate a tree with recursive array combining data from two tables
      1. USipalaus
    3. COCan you give an example of what the resulting nested list will look like? Do you just want the page titles to be displayed and indent all children under each page title?
      singulars
      1. POGenerate a tree with recursive array combining data from two tables
      1. USVincent Ramdhanie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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