Creating a table from loaded data

benton · 04-12-2008, 12:27 AM

In this scenario, there are three fields in a database, group, name and data, that consists of comma spearated lists. I am trying to read those in, sort them and make a three column table from the data. So I have something like this after reading in the data:

PHP Code:


			
$group = explode(",", mysql_group_column);

$name = explode(",", mysql_name_column);

$data = explode(",", mysql_data_column);



for ($i = 0; $i < count($group); ++$i)

{

  $extraArray[] = array('group' => $group[$i], 'name' => $name[$i], 'data' => $data[$i]);

}

After the above, I have an array like

HTML Code:

Array
(
    [0] => Array
        (
            [group] => Metal Information
            [name] => Cut
            [data] => Clear
        )
    [1] => Array
        (
            [group] => Metal Information
            [name] => type
            [data] => Strong
        )
    [2] => Array
        (
            [group] => Stone Information
            [name] => Length
            [data] => 4\"
        )
    [3] => Array
        (
            [group] => Stone Information
            [name] => Color
            [data] => White
        )
    [4] => Array
        (
            [group] => Stone Information
            [name] => Width
            [data] => 3\"
        )
    [5] => Array
        (
            [group] => Stone Information
            [name] => Type
            [data] => Diamond
        )
)

I want to be able to output a table that looks something like

HTML Code:

 Stone Information
  Length 4"
  Color  white
  Type   Diamond
  
 Metal Information
  Cut    Clear
  Type   Strong

I think my problem is that the initial data shouldn't all be read into one array. But since the group item can be anything and any number of groups, I can't see how to programatically read this in and separate it right from the start. Can someone please provide some ideas on how I can accomplish this?

Aaron · 04-12-2008, 02:00 AM

When the information comes out of the database it is already in an array... Why make it harder to process?

Just put the group, name, and data all in a different array, and then do something like,

PHP Code:


			
$count = 0;
while($whatever}
{
echo '<tr>';
echo '<td>' . $name[$count] . '</td>';
echo '</tr>';
echo '<tr>';
$forcount = 0;
  foreach($data)
  {
  echo '<td>' . $data[$count] . '<td>';
  $forcount++;
    if($forcount == 2)
    {
      $forcount = 0;
      echo '</tr>\n<tr>' #bust out a new row so you only have two "data" in a row.
    }
  }
echo '</tr>';
count++;
}

Just do something like that, and modify it to your formatting needs.

benton · 04-12-2008, 03:01 AM

Maybe I'm missing the point, or didn't make mine, but that won't give any contol over the table I need to create. The user can input the items in the database in any order - group1, group3, group2 and so on. I need to be able to display all of the common groups under one heading. If I just read them in and display them, it will just display whatever it reads in (any order).

Durux · 04-13-2008, 12:21 PM

You problem here is the complete lack of refrence from the DB and like i read it, you should change the way it's being written into the DB.
If there is more than one information on "Metal Information" or "Stone Information" there is no way to sort out what belong to where.

EX:

HTML Code:

Array
(
    [0] => Array
        (
            [group] => Metal Information
            [name] => Cut
            [data] => Clear
        )
    [1] => Array
        (
            [group] => Metal Information
            [name] => type
            [data] => Strong
        )
    [2] => Array
        (
            [group] => Stone Information
            [name] => Length
            [data] => 4\"
        )
    [3] => Array
        (
            [group] => Stone Information
            [name] => Color
            [data] => White
        )
    [4] => Array
        (
            [group] => Stone Information
            [name] => Width
            [data] => 3\"
        )
    [5] => Array
        (
            [group] => Stone Information
            [name] => Type
            [data] => Diamond
        )
    [6] => Array
        (
            [group] => Metal Information
            [name] => Cut
            [data] => Rough
        )
    [7] => Array
        (
            [group] => Metal Information
            [name] => type
            [data] => Weak
        )
)

In this case you would not know if the:
[group] => Metal Information | [name] => Cut | [data] => Clear
would go with:
[group] => Metal Information | [name] => type | [data] => Strong
or
[group] => Metal Information | [name] => type | [data] => Weak

Try posting how you write the data to the DB and the form being used by the user. Also post how your table looks...

benton · 04-14-2008, 05:13 PM

OK. I'll try to explain it better. The database table has, among other items, the following columns

HTML Code:

table stone {
group TEXT null,
text  TEXT null,
data  TEXT null
}

When filling in the information to be stored, there is an html table the user completes

HTML Code:

Group Text Data //title of table columns
G1    T1   D1   //user input
G2    T2   D3   //uer input

The user has to enter in all three or the data is not accepted. The data for each one is then imploded so that each field in the database is added as

HTML Code:

group = "g1","g2" 
text = "t1","t2" 
data = "d1","d2"

and so on. When the data is read back out of the database, all three entries are there and match to one another (group[0] = text[0] = data[0]). If I added a sort order column to the database for each row, that would probably solve the problem but it would add extra work for the user. I was hoping for a way around that. I'm stuck at how to read in the data and group it by the groups. So if the database contained data as

HTML Code:

table stone {
group "g1, g2, g4, g2, g1",
text  "t1, t2, t4, t5, t6",
data  "d1, d2, d4, d5, d6"
}

it would need to be displayed as

HTML Code:

g1
 t1 d1
 t6 d6
g2
 t2 d2
 t5 d5
g4
 t4 d4

Durux · 04-20-2008, 11:48 AM

First off, sorry for the long answering time.

If you have not found a solution(requires PHP5) yet i would suggest to change a couple of things so that it would be easier to sort through the DB.

First if you do not have it yet you will need a id field with NOT NULL, INT, AUTO_INCREMENT in your stone table.

Second of change the group TEXT null to group_id INT NOT NULL

Third make a new table called group.

HTML Code:

CREATE TABLE `group` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`group` VARCHAR( 255 ) NOT NULL
);

Now when the user inputs data into the DB, your script will go in and check the group table to see what the id is from there and then input the data into your table, the only difference is that insted of inputting the g1 and g2 it inputs the id from group into group_id in the stone table.

Now use this to read you table data:

PHP Code:


			
class DataArray

{

    public $Data;

    public $iCount;

    public $jCount;

    private $link;

    private $db;

    

    private function init(){

        $sql = "SELECT * FROM group";

        $result = mysql_query($sql,$this->link);

        if(!$result = mysql_query($sql,$this->link)){die('Invalid Query: '.mysql_error());}

        

        $this->iCount = mysql_num_rows($result);

        

        for($i=0;$i < $this->iCount; $i++){



            $j = 0;

            

            $sql = "SELECT text, data FROM stone WHERE group_id = '$i' ORDER BY id ASC";

            if(!$result = mysql_query($sql,$this->link)){die('Invalid Query: '.mysql_error());}

            

            while($row = mysql_fetch_assoc($result)){

                $this->Data[$i][$j]['text'] = $row['text'];

                $this->Data[$i][$j]['data'] = $row['data'];

                $j++;

            }

            

            $this->jCount[$i] = $j;                

            

        }

    }

    

    public function GroupName($id){

        $sql = "SELECT group FROM group WHERE id = '$id'";

        $result = mysql_query($sql,$this->link);

        if(!$result = mysql_query($sql,$this->link)){die('Invalid Query: '.mysql_error());}

        

        while($row = mysql_fetch_assoc($result)){

            return $row['group'];

        }

    }

    

    function __construct(){

         $this->link = mysql_connect('localhost','username','password');

         if(!$this->link){die('No Connection: '.mysql_error());}

         $this->db = mysql_select_db('your_db_name',$this->link);

         if(!$this->db){die('No such Database: '.mysql_error());}

         

         $this->init();

    }

    

    function __destruct(){

        mysql_close($this->link);

    }

}

Now you have all data sorted by group in the DataArray class.
You have all the information you need in $data->Data, $data->iCount and $data->jCount.

$data->Data[number][number]['text']/['data'] - Is containing the sorted data in an array.
$data->iCount - Is containing the amount of groups in the array.
$data->jCount[number] - Is countaining the amount of data in the second array of $data->Data

From here you can extract the data from the array and put into tables.

EX:

PHP Code:


			
$data = new DataArray;



$display = "<table border=\"0\">";



for($i=0;$i < $data->iCount;$i++){

    

    $display .= "<tr><td colspan=\"2\">".$data->GroupName($i)."</td><tr><td>Text</td><td>Data</td><tr>";

    

    for($j=0;$j < $data->jCount;$j++){

        $display .= "<tr><td>".$data->Data[$i][$j]['text']."</td><td>".$data->Data[$i][$j]['data']."</td></tr>";

    }

    

}



$display .= "</table>";

Hopes this was the solution you where looking for.