Geek asks and answers

PHP - How to display data from an array in a tree or table?

Currently, I have data retrieved from SELECT and converted to a PHP array. So I got this array.

[0] => array
(
    [Gattung] => 'Gattung_A'
    [Untergattung] => null
    [Sektion] => null
    [Untersektion] => null
    [Serie] => null
    [Unterserie] => null
    [Art] => 'Art_A'
    [Unterart] => null
    [Varietaet] => null
    [SubVarietaet] => null
    [Form] => 'Form_A'
    [Unterform] => null
    [SpezialForm] => null
    [Hybride] => null
    [Blendling] => null
    [Sorte] => null
)
[1] => array
(
    [Gattung] => 'Gattung_A'
    [Untergattung] => null
    [Sektion] => null
    [Untersektion] => null
    [Serie] => null
    [Unterserie] => null
    [Art] => 'Art_B'
    [Unterart] => null
    [Varietaet] => 'Variant_G'
    [SubVarietaet] => null
    [Form] => 'Form_B'
    [Unterform] => null
    [SpezialForm] => null
    [Hybride] => null
    [Blendling] => null
    [Sorte] => null
)
[2] => array
(
    [Gattung] => 'Gattung_B'
    [Untergattung] => null
    [Sektion] => null
    [Untersektion] => null
    [Serie] => null
    [Unterserie] => null
    [Art] => 'Art_C'
    [Unterart] => null
    [Varietaet] => 'Variant_A'
    [SubVarietaet] => null
    [Form] => null
    [Unterform] => null
    [SpezialForm] => null
    [Hybride] => null
    [Blendling] => null
    [Sorte] => null
)
......... 
Nearly ~300k records

So far, I fixed my array into one dimensional array based on unique values ​​with array_unique. This is my result:

(
  [0] => 'Gattung|Gattung_A'
  [1] => 'Art|Art_A'
  [3] => 'Form|Form_A'
  [5] => 'Art|ArtB'
  [7] => 'Varietaet|Variant_G'
  [9] => 'Form|Form_G'
  [11] => 'Gattung|Gattung_B'
  [13] => 'Art|Art_C'
  [15] => 'Varietaet|Variant_A'
}

However, my result is poorly grouped. I need to display it on a page to something like building a hierarchical system.

Question: @Melsi I hope my second edit clears my request.

How to display data in the form tree (see section "Editing 2") or tables (see section "Editing" 1)?

Thank.

EDIT 1:

, , - FULLTEXT . : ( FK )

SELECT 
    tGa.Gattung AS Gattung, 
    tUG.Untergattung AS Untergattung,
    tSe.Sektion AS Sektion,
    tUS.Untersektion AS Untersektion,
    tSer.Serie AS Serie,
    tUser.Unterserie AS Unterserie, 
    tA.Art AS Art,
    tUa.Unterart AS Unterart,
    tV.Varietaet AS Varietaet, 
    tSV.SubVarietaet AS SubVarietaet,
    tF.Form AS Form, 
    tUF.Unterform AS Unterform,
    tSF.SpezialForm AS SpezialForm, 
    tH.Hybride AS Hybride, 
    tBL.Blendling AS Blendling, 
    tSo.Sorte AS Sorte 
FROM 
        botanischername AS tBot
    LEFT JOIN ( gattung AS tGa ) ON ( tBot.ID_Ga = tGa.ID_Ga )
    LEFT JOIN ( untergattung AS tUg ) ON (  tBot.ID_UG = tUg.ID_UG )
    LEFT JOIN ( sektion AS tSe ) ON ( tSe.ID_Se = tBot.ID_Se )
    LEFT JOIN ( untersektion AS tUS ) ON ( tUS.ID_US = tBot.ID_US )
    LEFT JOIN ( serie AS tSer ) ON ( tSer.ID_Ser = tBot.ID_Ser )
    LEFT JOIN ( unterserie AS tUSer ) ON ( tUser.ID_USer = tBot.ID_USer )
    LEFT JOIN ( art AS tA ) ON ( tA.ID_A = tBot.ID_A )
    LEFT JOIN ( unterart AS tUa ) ON ( tUa.ID_UA = tBot.ID_UA )
    LEFT JOIN ( varietaet AS tV ) ON ( tV.ID_V = tBot.ID_V )
    LEFT JOIN ( subvarietaet AS tSV ) ON ( tSV.ID_SV = tBot.ID_SV)
    LEFT JOIN ( form AS tF ) ON ( tF.ID_F = tBot.ID_F )
    LEFT JOIN ( unterform AS tUF ) ON ( tUF.ID_UF = tBot.ID_UF )
    LEFT JOIN ( spezialform AS tSF ) ON ( tSF.ID_SF = tBot.ID_SF )
    LEFT JOIN ( hybride AS tH ) ON ( tH.ID_H = tBot.ID_H )
    LEFT JOIN ( blendling AS tBL ) ON ( tBL.ID_BL = tBot.ID_BL )
    LEFT JOIN ( sorte AS tSo ) ON ( tSo.ID_So = tBot.ID_So )
    LEFT JOIN ( status AS tST 
                 CROSS JOIN pflanze AS tPfl) ON 
                        ( tST.ID_ST = tBot.ID_ST AND tPfl.ID = tBot.ID )

    WHERE 
            tGa.Gattung LIKE #1_Word#        OR 
            tUG.Untergattung LIKE #2_Word#  OR
    tSe.Sektion LIKE #3_Word# OR 
    tUS.Untersektion LIKE #4_Word# OR 
    tSer.Serie LIKE #5_Word# OR 
    tUser.Unterserie LIKE #6_Word# OR
    tA.Art LIKE #7_Word# OR
    tUa.Unterart LIKE #8_Word# OR
    tV.Varietaet LIKE #9_Word# OR 
    tSV.SubVarietaet LIKE #10_Word# OR
    tF.Form LIKE #11_Word# oR 
    tUF.Unterform LIKE #12_Word# OR 
    tSF.SpezialForm LIKE #13_Word# oR
    tH.Hybride LIKE #14_Word# oR
    tBL.Blendling LIKE #15_Word# OR
    tSo.Sorte LIKE #16_Word#

, LIKE %xxx% OR - . , .

:

________________________________________________________________________________
|      |             |       |______________________________Detail_____________| 
| Num  | Gattung     | Art   | Form      | Varietaet  | ...... | the other cols|
|------|-------------|-------|-----------|------------|--------|---------------|
| 1.   | Gattung A   | Art A | Form A    |            |        |               |
|      |             | <same | Form B    |            |        |               |
|      |    | data or Art A> |           |            |        |               |
|      |             | Art B | Form B    | Variant G  |        |               |
| 2.   | Gattung B   | Art C |           | Variant A  |        |               |
-------------------------------------------------------------------------------
                                                              Back 1 2 3   Next

2: .

,

                   ----- Art A ------ Form A
                   |               |
                   |               -- Form B
1.   Gattung A ----|---- Art B ------ Form B ---- Variant G

2.   Gattung B --------- Art C ------ Variant A 

 ... And so on.
+3
2

@exodream

.

, , coloumns - , .

<?php 

        //make the labels
        $labels=array('Gattung','Untergattung','Sektion','Untersektion','Serie',
    'Unterserie','Art','Unterart','Varietaet','SubVarietaet','Form','Unterform',
    'SpezialForm','Hybride','Blendling','Sorte' );  

    // Make a MySQL Connection
    mysql_connect("localhost", "root", "") or die(mysql_error());

    //select database
    mysql_query("drop database if exists `test_db`;")or die(mysql_error());  
    mysql_query("create database `test_db`;")or die(mysql_error());  
    mysql_select_db("test_db") or die(mysql_error());

    //create table 
    mysql_query(" drop table if exists `test_table`;")or die(mysql_error());  


    mysql_query("   
    CREATE TABLE `test_table` (
        `Gattung` char(30)  NULL  ,
        `Untergattung` char(30)  NULL  ,
        `Sektion` char(30)  NULL  , 
        `Untersektion` char(30)  NULL  ,
        `Serie` char(30)  NULL  ,
        `Unterserie` char(30)  NULL  ,
        `Art` char(30)  NULL  ,
        `Unterart` char(30)  NULL  ,    
        `Varietaet` char(30)  NULL  ,
        `SubVarietaet` char(30)  NULL  ,
        `Form` char(30)  NULL  ,
        `Unterform` char(30)  NULL  ,
        `SpezialForm` char(30)  NULL  ,
        `Hybride` char(30)  NULL  ,
        `Blendling` char(30)  NULL  ,
        `Sorte` char(30)  NULL   
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB;") or die(mysql_error());  



     mysql_query("INSERT INTO `test_table`  VALUES 
     ('Gattung_A',null,null,null,null,null,'Art_A',null,null,null,'Form_A',null,null,null,null,null  ) ") 
     or die(mysql_error());    

     mysql_query("INSERT INTO `test_table`  VALUES 
     ('Gattung_A',null,null,null,null,null,'Art_A',null,null,null,'Form_A',null,null,null ,null,null ) ") 
     or die(mysql_error());    

     mysql_query("INSERT INTO `test_table`  VALUES 
     ('Gattung_B',null,null,null,null,null,'Art_C',null,'Variant_A',null,null,null,null,null,null,null ) ") 
     or die(mysql_error());   

     mysql_query("INSERT INTO `test_table`  VALUES 
     (null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null ) ") 
     or die(mysql_error());   


     mysql_query("INSERT INTO `test_table`  VALUES 
     ('Gattung_Q',null,null,null,null,null,'Art_C',null,'Variant_A',null,'Form_W',null,null,null,null,null ) ") 
     or die(mysql_error());   

     mysql_query("INSERT INTO `test_table`  VALUES 
     ('Gattung_Q',null,'test','test','test','test','Art_S','test','Variant_Y','test','Form_H','test','test','test','test','last' ) ") 
     or die(mysql_error());   


        //get result        
        $result = mysql_query("SELECT * FROM `test_table` ;")
           or die(mysql_error());   



    normal_display();      
    non_empty_row_display();        
    non_empty_column_display(); 

    function non_empty_row_display()
    { 
        //get data
        global $result;
        $final_array=array();   

        global $result;
        mysql_data_seek ($result , 0);

        print '<b><br><h2>TABLE: 2 NON EMPTY VALUES OF EACH ROW</h2></b>';
        print '<table border=7 bordercolor=orange >';
        $index=0;
        $color=array('#66CCFF','#00FF00');
        //for every row of the table
        while($row = mysql_fetch_row($result))
        {
            $current_row='';
            //for every coloumn
            foreach ($row as  $value) 
                //if value is not null
                if($value!=null)//then keep it 
                    $current_row .=  $value.' | ';

            if($current_row!='')
                echo '<tr bgcolor=',$color[$index%2],'><th>ROW ',$index++,'</th><td>',$current_row,'</td></tr>',
                        '<tr><td colspan=2 height=5 bgcolor=pink></td></tr>'; 
        }//while
        echo '</table>'; 

    }//function


    function normal_display()
    {
        print '<b><h2>TABLE:1 Normal display</h2></b>';
        print '<table border=1><tr>';
        global $labels;
        foreach($labels as $value)
            echo '<th>',$value,'</th>';
        print '</tr>';
        global $result;
        while($row = mysql_fetch_row($result)) 
        {
            print '<tr>';
            foreach($row as $value)
                echo '<td>',$value,'&nbsp;</td>';
            print '</tr>';
        }//while
        print '</table>';
     }//function



     function non_empty_column_display()
    { 
        //get data
        global $result;   
        mysql_data_seek ($result , 0);

        //for every row of the table
        $final_column=array();
        while($row = mysql_fetch_row($result)) 
            //for every coloumn
            foreach ($row as  $key => $value) 
                //if value is not null
                if($value!=null)
                    //if $final_column[$key] exists append value to it, otherwise assign value 
                    $final_column[$key]=(isset($final_column[$key]))?$final_column[$key].=$value.'<br>':$value.' <br> ';


        echo '<br><br><h2>Array:1 </h2>The coloumn with index 6 is placed before the one with index 2 in this array 
        because its first non empty value is fetched earlier. Check for your self:<br>';
        echo '<pre>',strip_tags(print_r($final_column,true)),'</pre>';


        //for each mysql table coloumn name
        global $labels;
        foreach($labels as $key => $value)
            //if it exists in the result
            if(array_key_exists($key,$final_column))
                //keep it
                $label_header=(isset($label_header))?$label_header.='<th>'.$value.'</th>':'<th>'.$value.'</th>';


        ksort($final_column);
        echo '<br><br><h2>Array:2 </h2>We can order this. Check for your self:<br>';
        echo '<pre>',strip_tags(print_r($final_column,true)),'</pre>';

        echo '<br><b><h2>TABLE:3 </h2>THIS IS A TABLE WITH NON EMPTY COLOUMNS<b>';
        echo '<table border=1>',$label_header,'<tr>';
        foreach($final_column as $value)
                echo '<td valign=top>',$value,'</td>';
        echo '</tr></table>';



    }//function

    ?>

147 161 ( ), , , , :

147

$final_column[$key]=(isset($final_column[$key]))?$final_column[$key].=$value.'<br>':$value.' <br> ';

161:

$label_header=(isset($label_header))?$label_header.='<th>'.$value.'</th>':'<th>'.$value.'</th>';
+2

1), // SQL, SQL - PHP. SQL rDBMS .

2) PHP - .

3), ,

4) ,

while ($r=mysql_fetch_assoc($result)) {
    foreach($r as $key=>$val) {
      if ($val || $val===0 || $val===false) {
         $array[$r['Art']][$key]=$val;
      }
    }
}
+1

More articles:


All Articles