Multi Dimensional Array Into Table

[jarvis]

Hi All,

 

I'm hoping someone can help.

 

For arguments sake, I have the following array:

$cars = array(
  array("Volvo",1994,18),
  array("BMW",2010,13),
  array("Saab",2015,2),
  array("Land Rover",2017,20,15)
);
  
echo '<pre>';
print_r($cars);
echo '</pre>';  

This outputs the following:

Array
(
    [0] => Array
        (
            [0] => Volvo
            [1] => 1994
            [2] => 18
        )

    [1] => Array
        (
            [0] => BMW
            [1] => 2010
            [2] => 13
        )

    [2] => Array
        (
            [0] => Saab
            [1] => 2015
            [2] => 2
        )

    [3] => Array
        (
            [0] => Land Rover
            [1] => 2017
            [2] => 20
            [3] => 15
        )

)

What I'm trying to do, is create the following table structure:

<table id="datatable" class="display" cellpadding="2" cellspacing="2">
    <thead>
        <tr>
            <th></th>
            <th>1994</th>
            <th>2010</th>
            <th>2015</th>
            <th>2017</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <th>Volvo</th>
            <td>18</td>
            <td></td>
            <td></td>
            <td></td>            
        </tr>
        <tr>
            <th>BMW</th>
            <td></td>
            <td>13</td>
            <td></td>
            <td></td>            
        </tr>
        <tr>
            <th>Saab</th>
            <td></td>
            <td></td>
            <td>2</td>
            <td></td>            
        </tr>
        <tr>
            <th>Land Rover</th>
            <td></td>
            <td></td>
            <td>20</td>
            <td>15</td>       
        </tr>
    </tbody>
</table> 

My array stems from:

$array = array();
$array[] = array('manufacturer' => $manufacturer, 'year' => $year, 'sales' => $value);

I thought something like this would be the right lines:

<table id="datatable" class="display" cellpadding="2" cellspacing="2">
    <thead>
        <tr>
            <th></th>
			<?php            
            foreach ($array as $car){
                echo "<th>".$car['year']."</th>";
            }
            ?>
            <th></th>
        </tr>
    </thead>
    <tbody>
    
<?php        
foreach ($array as $car){
    echo "<tr><th>".$car['manufacturer']."</th><td>".$car['year']."</td><td>".$car['sales']."</td></tr>";
}
?>

    </tbody>
</table>

However, that goes pearshaped

 

I think I'm missing the obvious, so wonder if someone can kindly assist?

 

Thanks

[mac_gyver]

in the case of the Land Rover, your sample data doesn't match your stated array build code. there would be two entries, one for 2015 and one for 2017. can you confirm your actual data?

[Psycho]

in the case of the Land Rover, your sample data doesn't match your stated array build code. there would be two entries, one for 2015 and one for 2017. can you confirm your actual data?

 

@OP: Just to clarify mac_gyver's question:

 

Your array has this for the land rover:

array("Land Rover",2017,20,15)

Based on the other records, the arrays contain a car model, year and quantity. This record has a fourth value which is apparently another quantity. How are those two quantity values supposed to be correlated to years?

 

What is even more confusing is that in your sample output you state you want the land rover entry to be created like this:

        <tr>
            <th>Land Rover</th>
            <td></td>
            <td></td>
            <td>20</td>
            <td>15</td>       
        </tr>

The 4th column is supposed to be for year 2015 (has a value of 20) and the 5th column for year 2017 (has a value of 15). The array entry for that vehicle shows a year of 2017 - how do the two values of 20 and 15 logically fall into those years.

 

More importantly - where is this array data coming from. If you are running a DB query and creating this array - then the array is likely being constructed incorrectly.

Edited April 5, 2018 by Psycho

[jarvis]

Hi Both,

 

Many thanks for the reply.

 

@mac_guyver That's correct, Land Rover should have 2 entries, so it would look like this: https://pasteboard.co/HfdO5ZX.png 

 

@Psycho Some Manufacturers can appear in multiple years. I'd messed up my array so should actually be like this:

Array
(
    [0] => Array
        (
            [0] => Volvo
            [1] => 1994
            [2] => 18
        )

    [1] => Array
        (
            [0] => BMW
            [1] => 2010
            [2] => 13
        )

    [2] => Array
        (
            [0] => Saab
            [1] => 2015
            [2] => 2
        )

    [3] => Array
        (
            [0] => Land Rover
            [1] => 2015
            [2] => 20
        )

    [4] => Array
        (
            [0] => Land Rover
            [1] => 2017
            [2] => 15
        )

)

Hopefully that helps clarify things?

[Psycho]

You didn't answer my question regarding the source of the data. If this is from a DB query, then you can pull the data in a format that is already structured for your needs to make the output very simple. Otherwise, you have to build some non-trivial code to process the data at least a couple of time. So, are you pulling this from a DB query? If so, please show the query and I can provide a query that makes this very simple.

[jarvis]

Sorry Psycho

$contract_date = get_terms('contract_date');
$array = array();

	foreach ($contract_date as $date) :
		echo 'Year: '.$date->name.'<br/>';
		

			$manufacturers = get_posts(array(
                'post_type'	=> 'cars',
                'taxonomy'	=> 'contract_date',  
                'term'		=> $date->name, 			
				'nopaging'	=> true
			));

			foreach($manufacturers as $manufacturer): #begin cycle through posts of this taxonmy
			setup_postdata($manufacturer); 

				$manufacturer = get_the_term_list( $manufacturer->ID, 'manufacturer' );			
				
				$values = get_posts(array(
					'post_type'	=> 'cars',
					'tax_query' => array(
						'relation' => 'AND',
						array(
							'taxonomy' => 'manufacturer',
							'field' => 'name',
							'terms' => $manufacturer
						),
						array(
							'taxonomy' => 'contract_date',
							'field' => 'name',
							'terms' => $date->name
						)
					),								
					'nopaging'	=> true
				));					

				foreach($values as $value): #begin cycle through posts of this taxonmy
				setup_postdata($value); 
					$value = get_field( 'value_m',$value->ID );
					$array[] = array('manufacturer' => $manufacturer, 'year' => $year, 'sales' => $value);
				endforeach;

			endforeach;


	 endforeach; 

It's based on Wordpress, so the data is stored as taxonomies for a custom post type

[Psycho]

Can you not run "real" queries instead of that funky WP framework? At the very least you need to find out how to use JOINs in the WP framework - all those looping queries are performance hogs. If you can provide the table structure I can provide a query that makes this very simple. Barring that - here is a complicated solution:

 

<?php
$cars = array(
  array("Volvo",1994,18),
  array("BMW",2010,13),
  array("Saab",2015,2),
  array("Land Rover",2015,20),
  array("Land Rover",2017,15)
);
 


//Create an array of unique years
$years = array();
foreach($cars as $record)
{
    //Add to years if not already there
    if(!in_array($record[1], $years)) { $years[] = $record[1]; }
}
//Sort years
sort($years);

 
//Loop through records to create structured array
$outputData = array();
foreach($cars as $record)
{
    //See if this vehicle has been added previously
    if(!isset($outputData[$record[0]]))
    {
        //Create an initial empty array for all years
        $outputData[$record[0]] = array_fill_keys($years, '');
    }
    //Add the current record quantity for the specified year
    $outputData[$record[0]][$record[1]] = $record[2];
}
 
//Create headers for table
$headers = "<th>Model</th>\n";
foreach($years as $year)
{
    $headers .= "<th>{$year}</th>\n";
}
//Create the content for output
$output = '';
foreach($outputData as $model => $years)
{
    $output .= "<tr>\n";
    $output .= "<td>{$model}</td>\n";
    foreach($years as $year => $quantity)
    {
        $output .= "<td>{$quantity}</td>\n";
    }
    $output .= "</tr>\n";
}

 
?>
<html>
<body>
<table border='1'>
    <tr>
        <?php echo $headers; ?>
    </tr>
    <?php echo $output; ?>
</table>
</body>
</html>

[jarvis]

Hi Pyscho,

 

That's very kind and very helpful!

 

I can run SQL queries, so will go rummage around the tables and see what I can do. At the very least,  you've given me a really great starting point

 

Thank you