PHP Array Help

[AJLX]

Hello Guys, I have a database that is set up something like this:

 

Name                Type_a             Type_b

 

3 series               Car                  BMW

1 series               Car                  BMW

A class                Car                  Merc

C class                Car                  Merc

500                      Motorbike       XYZ

600                      Motorbike       XYZ

 

I want to pull this info out of my database and display it like this:

 

 

Cars:

          BMW:

                        1 Series

                        3 Series

 

          Merc      A Class

          Merc      C Class

 

Motorbikes:

      

          XYZ:

                  500

                  600

 

So far I have this:

$result = mysql_query("SELECT * FROM Cars")or die(mysql_error());


while ($row = mysql_fetch_array($result)) 
{
// we get all of the items in this project and put them into a 3 part Array....
$assets= array('Name' => $row['Name], type_a' => $row['Type_A'], 'Type_B' => $row['Type_B']);
}

print_r($assets);

Now this works fine, I end up with a 3 part array containing all of the information I need. The part I am struggling with, is how to get the information back out of the array in a way I can deal with, to produce the above example. Any help?

 

Thanks guys!

Edited January 9, 2014 by AJLX

[objnoob]

you can do this.... which makes a multidimensional array

$assets = array();
while ($row = mysql_fetch_array($result)) 
{
   $assets[$row['Type_A']][$row['Type_B']][] = $row['Name];
}

Then you can do:

foreach($assets as $type => $makes){
      echo $type . '<br />';
      foreach($makes as $make=>$series){
              echo '    ' . $make. '<br />';
             foreach($series as $model){
                         echo '      ' . $model. '<br />';
             }
      }
}

Edited January 9, 2014 by objnoob

[jazzman1]

Barand was posted a solution using a recursive function.

[ginerjm]

The way to do it is to sort your query by the type_a and type_b fields in that order.  Then in your output keep track of the header fields that are displayed and don't show them until they change again.

[Psycho]

ginerjm beat me to the explanation which I was writing code:

 

 

$query = "SELECT * FROM Cars ORDER BY Type_a, Type_b, Name"
$result = mysql_query($query )or die(mysql_error());

$currentTypeA = false;
$currentTypeB = false;
while ($row = mysql_fetch_array($result))
{
    //Output first level - if different than last
    if($currentTypeA != $row['Type_a'])
    {
        //Output first level
        $currentTypeA = $row['Type_a'];
        echo "<h1>{$currentTypeA}</h1><br>\n"
    }

    //Output second level - if different than last
    if($currentTypeB != $row['Type_b'])
    {
        //Output second level
        $currentTypeA = $row['Type_b'];
        echo "<h2>{$currentTypeB}</h2><br>\n"
    }

    //Ouput the name
    echo "{$row['Name']}<br>"
}

[ginerjm]

Hate to step in, but I think the following code will do the job correctly.

 
.....
//  to show data in sequence of cat_a,cat_b, cat_c but only show those fields at beginning
//  of the group.
$q = "select * from tablename order by cat_a, cat_b, cat_c";
if (!$qrslts= $pdo->query($q))
{
 echo "Error in query";
 exit();
}
//
// now begin the output process
//
$firstrow = true;
$last_a = "xxx";
$last_b = "xxx";
$last_c = "xxx";
// define and initialize total vars here
//
// start your output table here and setup headings.
//
while ($row = $qrslts->fetch(PDO::FETCH_ASSOC)))
{
 if ($firstrow)
 {
  output entire row of data from $row
  add_totals();
  $firstrow = false;
 }
 else
 {
  if ($cat_a <> $last_a)
  {
   show_totals('c');
   show_totals('b');
   show_totals('a');
   output entire row of data from $row
   add_totals();
  }
  else
  {
   if ($cat_b <> $last_b)
   {
    show_totals('c');
    show_totals('b');
    output row of data but skip column for cat_a
    add_totals();
   }
   else
   {
    if ($cat_c <> $last_c)
    {
     show_totals('c');
     output row of data but skip columns for both cat_a & cat_b
     add_totals();
    }
   }
  }
 }
 // save the last shown cat values after every output.
 $last_a = $row['cat_a'];
 $last_b = $row['cat_b'];
 $last_c = $row['cat_c'];
}
// finished with data - show final totals.
show_totals('c');
show_totals('b');
show_totals('a');
//**********************
/*  note 1 -  the show totals functions are only necessary if you need totals at the end of
      each category break(change).
    note 2 - the best way to output this kind of data is to use a table and simply output a blank td element when trying to not show a category/column.
*/

Edited January 9, 2014 by ginerjm

[jazzman1]

Redesign your table structure according my dump file, then use the Barand's script.

 

dump.sql

--
-- Table structure for table `auto`
--

DROP TABLE IF EXISTS `auto`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `parent` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `auto`
--

LOCK TABLES `auto` WRITE;
/*!40000 ALTER TABLE `auto` DISABLE KEYS */;
INSERT INTO `auto` VALUES (1,'AUTO',NULL),(2,'CAR',1),(3,'BMW',2),(4,'MERCEDEC',2),(5,'1 SERIES',3),(6,'3 SERIES',3),(7,'MOTORBIKE',1),(8,'500',7),(9,'600',7),(10,'A CLASS',4),(12,'C CLASS',4);

PHP Script:

<?php

$conn = mysql_connect('localhost', 'userName', 'userPass');

$db_name = mysql_select_db('test');

$sql = "SELECT id, name, parent
FROM `test`.`auto`";

$res = mysql_query($sql)  or die(mysql_error());

while (list($id, $name,$parent) = mysql_fetch_row($res)) {
    $data[$parent][] = array('id'=>$id, 'name'=>$name);
}

//echo "<pre>".print_r($data, true)."</pre>";
   
// function to print a autos then its child nodes 
function displayHierarchy(&$arr, $parent, $indent=0) {
    $ind = $indent * 50;
    if(isset($arr[$parent]))
    foreach($arr[$parent] as $rec) {
      echo "<div style='width:300px; margin-top:5px; margin-left: {$ind}px; padding:5px; border:1px solid gray;'>
        {$rec['name']}
        </div>"; 
      displayHierarchy($arr, $rec['id'], $indent+1);
    }
    
}

// call the recursive function
displayHierarchy($data, null);

Result:

Edited January 9, 2014 by jazzman1

[jazzman1]

Take a look at here too, to learn a little more.

[objnoob]

 

Hate to step in, but I think the following code will do the job correctly.

 
.....
//  to show data in sequence of cat_a,cat_b, cat_c but only show those fields at beginning
//  of the group.
$q = "select * from tablename order by cat_a, cat_b, cat_c";
if (!$qrslts= $pdo->query($q))
{
 echo "Error in query";
 exit();
}
//
// now begin the output process
//
$firstrow = true;
$last_a = "xxx";
$last_b = "xxx";
$last_c = "xxx";
// define and initialize total vars here
//
// start your output table here and setup headings.
//
while ($row = $qrslts->fetch(PDO::FETCH_ASSOC)))
{
 if ($firstrow)
 {
  output entire row of data from $row
  add_totals();
  $firstrow = false;
 }
 else
 {
  if ($cat_a <> $last_a)
  {
   show_totals('c');
   show_totals('b');
   show_totals('a');
   output entire row of data from $row
   add_totals();
  }
  else
  {
   if ($cat_b <> $last_b)
   {
    show_totals('c');
    show_totals('b');
    output row of data but skip column for cat_a
    add_totals();
   }
   else
   {
    if ($cat_c <> $last_c)
    {
     show_totals('c');
     output row of data but skip columns for both cat_a & cat_b
     add_totals();
    }
   }
  }
 }
 // save the last shown cat values after every output.
 $last_a = $row['cat_a'];
 $last_b = $row['cat_b'];
 $last_c = $row['cat_c'];
}
// finished with data - show final totals.
show_totals('c');
show_totals('b');
show_totals('a');
//**********************
/*  note 1 -  the show totals functions are only necessary if you need totals at the end of
      each category break(change).
    note 2 - the best way to output this kind of data is to use a table and simply output a blank td element when trying to not show a category/column.
*/

 

This code is not the only code that does the job correctly.  And, it looks like a rats nest.  You should clean that up some.  

And, you should define the functions show_totals and add_totals.

And, you should comment out ALL of the comments appropriately.

 

Your code is far from correct.

Edited January 9, 2014 by objnoob

[objnoob]

Redesign your table structure according my dump file, then use the Barand's script.

 

Jazzy Firefox toolbar configuration you have there, sir. :]

[ginerjm]

Actually my code is exactly correct - regardless of your opinion of aesthetics.  One thing I realized last night in bed was I left out the display of a line that did not make a category break.  The following needs to be added:

 

    if ($cat_c <> $last_c)     
    {      show_totals('c');
           output row of data but skip columns for both cat_a & cat_b
           add_totals();     
    } 
    ELSE
    {
         output row of data but skip cols for cat-a, cat-b & cat-c
         add_totals();
    }

 

Left out the simple case!

 

As for comments - the comments are there for a reason.  And the functions were not fleshed out since this is ONLY A TEMPLATE for the OP to use to achieve his goals.

Edited January 9, 2014 by ginerjm

[AJLX]

Thanks guys,

 

Plenty to be getting on with!