PHP array to table

[james182]

I am trying to display my array data in a table, but am having issues displaying a certain way.
My Query

<?php

// GET PLANT STOCK LIST
$plistSQL = "SELECT plItemID, plBotanicalName, plSize, plQty FROM plant_list_stock WHERE plID = '$pl_listid' AND plContID = '$contid'";
$plist_result = mysql_query($plistSQL,$connection) or die("Couldn't get Botanical Name. - " . mysql_error());

$nurseries = array();
$i=0;
while ($arrRow = mysql_fetch_assoc($plist_result))
{
	$stockitemID 	= $arrRow['plItemID'];

	$it_supSQL = "SELECT * FROM item_supplier WHERE ItemID = '$stockitemID' ORDER BY ValidFrom Desc";
	$it_sup_result = mysql_query($it_supSQL,$connection) or die("Couldn't run Item/Supplier loop. - " . mysql_error());

	$it_sup_num = mysql_numrows($it_sup_result);

	// COMPARE AGAINST ITEM / SUPPLIER TABLE
	while ($arrRowc = mysql_fetch_array($it_sup_result))
	{
		// GET SUPPLIER DETAILS
		$supSQL = "SELECT * FROM suppliers WHERE SupplierID = '$arrRowc[SupplierID]'";
		$sup_result = mysql_query($supSQL,$connection) or die("Couldn't run Supplier loop. - " . mysql_error());

		$sup_num = mysql_numrows($sup_result);

		$s=0;
		while ($arrRows = mysql_fetch_array($sup_result))
		{			
			$nurseries[$s][''] = $arrRowc["BotanicalName"];
			$nurseries[$s][$arrRows['SupplierName']] = $arrRowc["Price"];
			//$nurseries[$i][''] = $arrRowc["BotanicalName"];
			//$nurseries[$i][$arrRows['SupplierName']] = $arrRowc["Price"];
		}
		$s++;
	}
	$i++;

}


print_r($nurseries);
?>

the above returns this:

Array
(
    [0] => Array
        (
            [] => Festuca glauca
            [E Plants Noosa] => 4.00
            [Greenstock Nurseries] => 150.00
            [Pallara Trees] => 145.00
            [Plants Direct Queensland] => 145.00
            [Pacific Trees Qld Pty Ltd] => 145.00
            [Mad About Plants] => 135.00
            [Oxford Park Nursery] => 2.00
            [Botanica Nurseries] => 4.95
            [Andreasens Green] => 4.75
            [All Green Nursery] => 3.59
            [Meyer Nurseries] => 3.85
            [Alderwood Nursery] => 5.00
            [Exotic Nurseries and Landscaping] => 0.00
            [Cabbage Tree Nursery] => 5.00
            [Murphy & Nowland Nursery] => 3.50
            [Carstens Gardens] => 4.75
            [Logans Nursery] => 3.80
            [Kenthurst Nursery] => 4.70
        )

)

If i use the $i instead i get the below, which would be fine if all the nuseries were there and 0.00 added to those with no price.

Array
(
    [0] => Array
        (
            [] => Delonix regia
            [E Plants Noosa] => 145.00
            [Greenstock Nurseries] => 150.00
            [Pallara Trees] => 145.00
            [Plants Direct Queensland] => 145.00
            [Pacific Trees Qld Pty Ltd] => 145.00
            [Mad About Plants] => 135.00
        )

[3] => Array
    (
        [] => Poa labillardieri 
        [Oxford Park Nursery] => 1.50
    )

[5] => Array
    (
        [] => Festuca glauca
        [Botanica Nurseries] => 4.95
        [E Plants Noosa] => 4.00
        [Andreasens Green] => 4.75
        [All Green Nursery] => 3.59
        [Meyer Nurseries] => 3.85
        [Alderwood Nursery] => 5.00
        [Exotic Nurseries and Landscaping] => 0.00
        [Cabbage Tree Nursery] => 5.00
        [Murphy & Nowland Nursery] => 3.50
        [Carstens Gardens] => 4.75
        [Logans Nursery] => 3.80
        [Kenthurst Nursery] => 4.70
        [Oxford Park Nursery] => 2.00
    )

)

but what i need it to return is this:

$data[0][''] = 'Festuca glauca';
$data[0]['E Plants Noosa'] = '4.00';
$data[0]['Greenstock Nurseries'] = '150.00';
$data[0]['Pallara Trees'] = '145.00';
$data[0]['Plants Direct Queensland'] = '145.00';
$data[0]['Pacific Trees Qld Pty Ltd'] = '145.00';
$data[0]['Mad About Plants'] = '135.00';
$data[0]['Oxford Park Nursery'] = '2.00';
$data[0]['Botanica Nurseries'] = '4.95';
$data[0]['Andreasens Green'] = '4.75';
$data[0]['All Green Nursery'] = '3.59';
$data[0]['Meyer Nurseries'] = '3.85';
$data[0]['Alderwood Nursery'] = '5.00';
$data[0]['Exotic Nurseries and Landscaping'] = '0.00';
$data[0]['Cabbage Tree Nursery'] = '5.00';
$data[0]['Murphy & Nowland Nursery'] = '3.50';
$data[0]['Carstens Gardens'] = '4.75';
$data[0]['Logans Nursery'] = '3.80';
$data[0]['Kenthurst Nursery'] = '4.70';

$data[1][''] = 'Festuca glauca';
$data[1]['E Plants Noosa'] = '';
$data[1]['Greenstock Nurseries'] = '0.00';
$data[1]['Pallara Trees'] = '5.00';
$data[1]['Plants Direct Queensland'] = '15.00';
$data[1]['Pacific Trees Qld Pty Ltd'] = '1.00';
$data[1]['Mad About Plants'] = '0.00';
$data[1]['Oxford Park Nursery'] = '20.00';
$data[1]['Botanica Nurseries'] = '40.95';
$data[1]['Andreasens Green'] = '0.75';
$data[1]['All Green Nursery'] = '0.59';
$data[1]['Meyer Nurseries'] = '385';
$data[1]['Alderwood Nursery'] = '5.00';
$data[1]['Exotic Nurseries and Landscaping'] = '0.00';
$data[1]['Cabbage Tree Nursery'] = '5.00';
$data[1]['Murphy & Nowland Nursery'] = '3.50';
$data[1]['Carstens Gardens'] = '4.75';
$data[1]['Logans Nursery'] = '3.80';
$data[1]['Kenthurst Nursery'] = '4.70';

It needs to out all the nurseries and if no price is available then 0.00 is there.
How can this be done ?

[james182]

Anyone please i am stuck...

[sneakyimp]

It is a bad idea for performance reasons to run a query and then for each record run another query (and then for each of THOSE records run yet another query). If each query return 100 records every time, you could end up running a million queries for a single run of the page.

You can often avoid this sort of thing by using a JOIN between two tables. Here's your outermost query:

$query1 = "SELECT plItemID, plBotanicalName, plSize, plQty FROM plant_list_stock WHERE plID = '$pl_listid' AND plContID = '$contid'";

Here's the next query:

$query2 = "SELECT * FROM item_supplier WHERE ItemID = '$stockitemID' ORDER BY ValidFrom Desc";

where $stockitemID is plItemID from the first result.

You can combine those two queries and the last query using a join

$join_query = "SELECT pls.plItemID, pls.plBotanicalName, pls.plSize, pls.plQty, is.*, s.*
FROM plant_list_stock pls
LEFT JOIN item_supplier is
ON is.ItemID=pls.plItemID
LEFT JOIN suppliers s
ON s.SupplierID=is.SupplierID
WHERE pls.plID = '$pl_listid' AND pls.plContID = '$contid'";

This will result in one query returning a list of the plant list stock along with the supplier (if any). You'll have to be a bit more clever about how you loop through it to get the array you are after, but it should be more efficient.

Also, you should use [man]mysqli[/man] instead of [man]mysql[/man]. mysql is going away and mysqli is the new stuff.