Display Database records as columns

[Webville312]

Hi Guyz,

I have hit a snag with the code I have been working on.
I am required to fetch database records from 3 tables and display them in both rows and columns.
For example, the output of the records on the web page is required to be as follows;

******************************************************************
*SCHOOL ADDRESS* SCHOOL NO: * EXTRA INFO               *
*************** ************* ************ ************ **********
*2013:02:01    * 001         * School 1   * District 1 * Micheal *
*2013:02:02    * 001         * School 2   * District 2 * Ronald  *
****************************************** ************ **********

So, the Invoice date is being gotten from one table called 'invoices' while Products is being gotten from a table called 'products'. These two are being linked using a common product_id.
I have tried out the following code, but it does not bring the results that I want. Here's the code I am working with;

<?php
    error_reporting(0);
    include('connect.php');

    $query="select * from school_info, school WHERE school_info.school_id=school.school_id";
    $result=mysql_query($query) or die(mysql_error());

    $cols=5;        // Here we define the number of columns
    echo "<table>";  // The container table with $cols columns
    do
    {
    echo "<tr>";
    for($i=1;$i<=$cols;$i++)
    {   
    $row=mysql_fetch_array($result);
    if($row)
    {
    ?>
    <td>
    <table>
    <tr valign="top">
    <td> </td> <!-- columns can have both text and images -->
    <td>
    <b><?php echo $row['school_name']; ?></b><br />
    <?php echo $row['district']; ?><br />
    <?php echo $row['person_in_charge']; ?><br />
    </td>
    <td width="50"> </td> <!-- Create gap between columns -->
    </tr>
    </table>
    </td>
    <?php
    }
    else
    {
    echo "<td> </td>";    //If there are no more records at the end, add a blank column
    }
    }
    } 
    while($row);
    echo "</table>";
    ?>

Thank you in advance

[veedeoo]

have you look into join as described here?

Can you show us your tables?

[Webville312]

The tables are as follows;

// The table school
CREATE TABLE IF NOT EXISTS `school` (
  `school_id` int(12) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(50) NOT NULL,
  `district` varchar(50) NOT NULL,
  `person_in_charge` varchar(50) NOT NULL,
  `in_charge_title` varchar(50) NOT NULL,
  `in_charge_contact` varchar(10) NOT NULL,
  `date_added` date NOT NULL,
  PRIMARY KEY (`school_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

// The table school_info
CREATE TABLE IF NOT EXISTS `school_info` (
  `school_id` int(12) NOT NULL,
  `address` varchar(500) NOT NULL,
  `telephone` varchar(10) NOT NULL,
  `badge` longblob NOT NULL,
  `badge_name` varchar(255) NOT NULL,
  PRIMARY KEY (`school_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[Webville312]

I have looked at Joins, however they are not doing what I expect.
Just as illustrated in the required output, the EXTRA INFO section is being gotten from another table, and unlike the mainstream display or records which is in form of rows, for this one; it is required to be displayed as columns.

I hope you understand what I mean.

[drjohn]

Line 5 of your code is a join, using the "where x=y" syntax.
The syntax used doesn't matter, it's a join.

But you then exclusivley use the info from a single table!

Why don't you show us this invoice table and product table as well, and then write out what you actually want to display.

ALSO why on earth have you got two tables, school and school_info ??? There is no reason to split the data like that, all the data in school_info is functionally dependant on the school_id, the primary key of the school table, unless a school has more than one address, or more than one badge. There should only be one table, school , with all the fields of the two tables in it.

Follow standard normalisation rules or your entity relationship diagram. There is an entity called school with a set of attributes which there is no need to split in two and create a second entity, school_info - you might as well stick person-in-charge in school_info and badge_name in school, it would make no difference. And no sense either.

Your database design is flawed, and will force you to do unnecessary joins like this, complicating the code and thus making errors more likely.

[drjohn]

ALSO
You say you have one table called invoices and one called products, joined via the common product_id.

Which suggests one invoice will be for one product, and every product ordered, even if they are ordered at the same time, will have a separate invoice. That's not how things work in real life.

When I order several times, they are all listed in the one invoice, each on a separate line, which lists product name and number bought, sometimes item unit cost, as well as total cost. Think about that and consider changing your database design. Cos it's wrong, based on what you say.

I don't do people's homework for them, but I am willing to help when they try to do it and need pointing in the correct direction. (This is a standard homework problem from your brief description, we've all done this one at some point in time ;-) )

[Webville312]

Apologies for the mixup. I got confused between two projects that I am doing.
I have 2 tables, Beer_Tax_Invoice_From_UBL which has the following structure;

`beer_tax_invoice_from_ubl_id` int(11) NOT NULL AUTO_INCREMENT,
  `document_number` bigint(20) NOT NULL,
  `our_reference` int(11) NOT NULL,
  `order_date` datetime NOT NULL,
  `document_date` datetime NOT NULL,
  `purchase_order_number` varchar(30) NOT NULL,
  `dispatch_date` datetime NOT NULL,
  `invoice_address` varchar(160) NOT NULL,
  `invoice_branch_id` int(11) NOT NULL,
  `account_number` varchar(12) NOT NULL,
  `payment_due_date` datetime NOT NULL,
  `consignment_note_number` int(12) NOT NULL,
  `delivery_note_number` int(12) NOT NULL,
  `order_type` varchar(20) NOT NULL,
  `tax_point_date` datetime NOT NULL,
  `arrival_date` datetime NOT NULL,
  `delivery_address` varchar(160) NOT NULL,
  `delivery_branch_id` int(11) NOT NULL,
  `customer_vat_number` varchar(12) NOT NULL,
  `supplier_tin_number` varchar(20) NOT NULL,
  `supplier_vat_number` varchar(12) NOT NULL,
  `payment_terms` varchar(160) NOT NULL,
  `vehicle_number` varchar(16) NOT NULL,
  `haulier` varchar(160) NOT NULL,
  `order_created_by` varchar(30) NOT NULL,
  `dispatched_from` varchar(160) NOT NULL,
  `delivery_terms` varchar(30) NOT NULL,
  `line_values_total` double NOT NULL DEFAULT '0',
  `taxable_value` double NOT NULL,
  `vat_rate` double NOT NULL,
  `tax_amount` double NOT NULL,
  `currency` varchar(3) NOT NULL,
  `total_due` double NOT NULL,
  `number_of_line_entries` int(11) NOT NULL,
  PRIMARY KEY (`beer_tax_invoice_from_ubl_id`)

And the second table Beer_Tax_Invoice_From_UBL_Entry with the structure below;

`beer_tax_invoice_from_ubl_entry_id` int(11) NOT NULL AUTO_INCREMENT,
  `beer_tax_invoice_from_ubl_id` int(11) NOT NULL,
  `product_number` int(11) NOT NULL,
  `product_description` varchar(60) NOT NULL,
  `product_abv_percent` double NOT NULL,
  `quantity` int(11) NOT NULL,
  `uom` varchar(6) NOT NULL,
  `base_price` double NOT NULL,
  `customer_discount` double NOT NULL,
  `promotional_discount` double NOT NULL,
  `net_price_per_unit` double NOT NULL,
  `line_value` double NOT NULL,
  `vat_rate` double NOT NULL,
  PRIMARY KEY (`beer_tax_invoice_from_ubl_entry_id`)

These two tables have one field in common 'beer_tax_invoice_from_ubl_id' which I am using to get the results I need. When it comes to the display part, I want the table to be displayed as follows;

    ********************************************************************
    *INVOICE DATE  * RECEIVED DATE * BELL LAGER * TUSKER     * SENATOR *   
    *************** *************** ************ ************ **********
    *2013:02:01    * 2013:02:01    * 135        * 200        * 160     *
    *2013:02:02    * 2013:02:02    * 100        * 150        * 170     *
    ****************************************** ************ ************

The products [Bell, Tusker and Senator] are gotten from the Beer_Tax_Invoice_From_UBL_Entry table. while the invoice date ans received dates are gotten from the Beer_Tax_Invoice_From_UBL table.
I have tried, but I fail to get the matching quantities of the different products. Here is the code that I am using;

<?php
        $start_date = $_POST['start_date'];
        $end_date   = $_POST['end_date'];
        $branch_name = $_POST['branch_name'];

        // If the fields are empty, display an error message
        if($start_date == '' || $end_date== '')
        {
        // Display an error message
        include('../src/Messaging/show_error_message.php');
        echo $missing_report_fields;
        }
        else
        {

        $query = "SELECT * FROM Beer_Tax_Invoice_From_UBL,Beer_Tax_Invoice_From_UBL_Entry WHERE Beer_Tax_Invoice_From_UBL.document_date >= '$start_date' AND Beer_Tax_Invoice_From_UBL.document_date <= '$end_date' AND Beer_Tax_Invoice_From_UBL.invoice_address LIKE '%$branch_name%' AND Beer_Tax_Invoice_From_UBL.beer_tax_invoice_from_ubl_id = Beer_Tax_Invoice_From_UBL_Entry.beer_tax_invoice_from_ubl_id";

        // Execute the SQL query using the MYSQLi connection
        $result = $mysqli->query($query);

        // Find out if any results have been returned by counting the number of rows
        $num_rows = $result->num_rows;

        if($num_rows ==0)
        {
        echo "<div id='record_not_found'>Record not found.</div>";
        }
        else
        {   
        echo "<table width=\"922\" border=\"0\" class=\"listing\" cellpadding=\"0\" cellspacing=\"0\"> 
        <tr>
    <td width=\"106\"><strong>INVOICE NUMBER</strong></td>
    <td width=\"97\"><strong>INVOICE DATE</strong></td>
    <td width=\"148\"><strong>RECEIVED DATE</strong></td>";
    // Get the product information
    $get_product_info = "SELECT DISTINCT product_description,net_price_per_unit FROM Beer_Tax_Invoice_From_UBL_Entry";
    $product_results = $mysqli->query($get_product_info);

    while($product_data = $product_results->fetch_array(MYSQLI_BOTH))
    {
    $product = $product_data['product_description'];
    $net_price_per_unit = $product_data['net_price_per_unit'];
    //$no_of_subjects = $return_data['no_of_subjects'];
    echo "<td width=\"256\">".$product."</td>";
    }

  echo "</tr>";
                        // Create a variable that will determine the Background colors to display for each row
                        $num=1;

                        // Do while loop for out put records. 
                        while($report_data = $result->fetch_array(MYSQLI_BOTH))
                        {
                        $invioce_date = $report_data['tax_point_date'];
                        $received_date = $report_data['arrival_date'];
                        $product_description = $report_data['product_description'];
                        $quantity = $report_data['quantity'];
                        //$net_price_per_unit = $report_data['net_price_per_unit'];
                        $vat_rate = $report_data['vat_rate'];
                        $taxable_value = $report_data['taxable_value'];
                        $total_due = $report_data['total_due'];
                        $line_values_total = $report_data['line_values_total'];
                        $invoice_address = $report_data['invoice_address'];
                        $vat_amount = ($vat_rate/100)*$taxable_value;
                        // Plus 1 at $num.
                        $num++;
                        // Use modulus by 2 in $num value and set the value of "$bg" if result equal 0 or not. 
                        if(($num%2)!=0){
                        $bg="#FFFF99";
                        }else{
                        $bg="#FFFFFF";
                        }
        echo "<tr>
    <td width=\"106\">$invoice_address</td>
    <td width=\"97\">$invioce_date</td>
    <td width=\"148\">$received_date</td>
    <td width=\"148\">$quantity</td>
  </tr>";
        }
        }

        }

Thanx in advance

[Webville312]

I would have loved to do it differently, but my boss wants it in a different format ... so. I am stuck

[veedeoo]

Hi,

Please allow me to use the first database you have presented. Keep in mind the query is pretty much the same, regardless if we use the second database that you posted. Just replace the column names.

In my example that should represent the school_id on the first table and school_id on the second.

Simple rule I made for myself only. I always assign my query to some variable for easy update and modification.

Here we go. Say we have these tables school and school_info as shown above.

I would do this in a long-hand version for clarifications, but you are always welcome to do it differently.

Itimize all pertinent columns from both tables.

$db_filters = "school_id,school_name,district,persion_in_charge,in_charge_title,in_charge_contact, date_added,address,telephone,badge,badge_name";

I only use school_id once because both tables have the same column name.

We define our query

$get_items = ("SELECT ".$db_filters." FROM YOUR_DATABASE LEFT JOIN school_info ON(school.school_id = school_info.school_id) ORDER by `date_added`");

We execute our query... WARNING! please USE PDO. I don't have any PDO reference at this moment, so I will use the OLD mysql. $conn is the database instantiated or persistent connection

$result = mysql_query($get_items, $conn) or trigger_error("SQL", E_USER_ERROR);

loop the result

echo 'put your table tags here';
while($row = mysql_fetch_array( $result )) {

    /*
    * the column name can be from either table.
    */

    echo $row['column_name'];

    }

If you are happy with the result, slowly build your html around them to achieve the presentation style your boss wanted.

I am not really sure if your company encourages the use of MySQL Workbench from Oracle. It is a lot faster to develop mysql application using this framework. Most importantly in

Database Design & Modeling
SQL Development (replacing MySQL Query Browser)
Database Administration (replacing MySQL Administrator)
Database Migration
Query Optimization

One final advice, use javascript or jquery in alternating table row colour..

Something like this..

<script>
function alt_colour(id){
 if(document.getElementsByTagName){  
   var table = document.getElementById(id);  
   var rows = table.getElementsByTagName("tr");  
   for(i = 0; i < rows.length; i++){          
 //manipulate rows
     if(i % 2 == 0){
       rows[i].className = "even";
     }else{
       rows[i].className = "odd";
     }      
   }
 }
}
</script>

On your html

<body onload="alt_colour('res_table')"> 

On your iteration table

<table id = "res_table">

that's pretty much it, for me right now. Don't forget to create a css style for the classes 'odd' and 'even'.

[diafol]

Ever thought of using less verbose table names? Descriptive is good, but this is confusing.

Isn't this a pivot-crosstab query?

Are the number of products constant - i.e. are there always 3 columns (3 products) in the invoices?

[diafol]

I'd use a stored procedure for this I think.

I'll use some related tables with real data to give you an idea...

Products

product_id | product
--------------------
1          | Senator
2          | Tusker
3          | Bell Lager

Companies

company_id | company
--------------------
1          | Company1
2          | Company2
3          | Company3

Orders

order_id | dated      | company_id
-----------------------------
1        | 2014-03-05 | 1
2        | 2014-03-04 | 2
3        | 2014-03-03 | 1
4        | 2014-03-02 | 3
5        | 2014-03-01 | 3

OrderItems

orderitems_id | order_id | product_id | units
---------------------------------------------
1             | 1        | 1          |  50
2             | 1        | 2          |  34
3             | 1        | 3          |   2
4             | 2        | 2          |   5
5             | 2        | 3          |  67
6             | 3        | 1          | 100

OK, pretty standard so far.

The next thing is to create your Stored Procedure. In the SQL window of phpmyadmin (haven't tried this - I use SQLyog) or in your usual SQL GUI...

Run this:

DELIMITER$$
CREATE PROCEDURE `daniweb`.`crosstab`(_COMPANY_ID INT)
BEGIN
SET @SQL = NULL;
SELECT 
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'GROUP_CONCAT((CASE oi.product_id WHEN ',
            oi.product_id,
            ' THEN oi.units ELSE NULL END)) AS `',
            p.product,
            '`'
        )
    ) INTO @SQL
 FROM orderitems AS oi LEFT JOIN products AS p ON oi.product_id = p.product_id;

 SET @SQL = CONCAT(
                'SELECT o.order_id, o.dated, ',
                @SQL,
                ' FROM orders AS o LEFT JOIN orderitems AS oi ON o.order_id = oi.order_id WHERE o.company_id = ',
                _COMPANY_ID,
                ' GROUP BY o.order_id, o.dated'
            );    

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$

That will actually create the SP called "crosstab".

Then in a separate query (again in the SQL window) run...

CALL crosstab(1);

The parameter refers to the company_id for which you want to retrieve data.
Of course you can create all manner of different crosstabs for 'by date' etc.

So,

$result = mysql_query("CALL crosstab($company_id)");

You can get the field (product names) for headings from the first row (array keys).

This little snip worked for me...

<?php 
$conn = mysql_connect("localhost","root","");
mysql_select_db("daniweb",$conn);

$result = mysql_query("CALL crosstab(1)");
echo "<pre>";
while($data = mysql_fetch_assoc($result))
{
    print_r($data); 
}
echo "</pre>";
?>

Obviously use mysqli or PDO if you can.

[Webville312]

Thanx Guys, Lemme try it out now. Will post the results.

[Webville312]

@diafol, I tried it out, but it did not work the way it's required.
I realiesed that in the tables u displayed the information in form of rows. Now, if you run my script, you realise that the product_description[Which is being picked from the 'Beer_Tax_Invoice_From_UBL_Entry' database table] is occupying the top row, and the product_description header is generated from the first while loop.
Just as illustrated below;

    --------------------------------------------------------------------
    Invoice date | Invoice_number | Senator      | Bell Lager | Tusker | 
    --------------------------------------------------------------------
    2013:03:04   | 001            |                                    |
    --------------------------------------------------------------------
    2013:03:05   | 002            |                                    |
    --------------------------------------------------------------------

Just as illustrated, the product_description [Senator, Bell and Tusker] are generated from the 'Beer_Tax_Invoice_From_UBL_Entry' table, while the invoice_date and the invoice_number are gotten from the 'Beer_Tax_Invoice_From_UBL' table.
The code I wrote was able to bring out the above output. However, when it came to getting the different product quantities in each invoice; it is not displaying.
So, according to my boss, the output should be like this;

--------------------------------------------------------------------
Invoice date | Invoice_number | Senator      | Bell Lager | Tusker | 
--------------------------------------------------------------------
2013:03:04   | 001            | 500          | 350        | 180    |
--------------------------------------------------------------------
2013:03:05   | 002            | 450          | 200        | 200    |
--------------------------------------------------------------------

Thanx once againg.

Regards.

[Webville312]

@Diafol, Its not that there are always three products. I am just using three products just for illustration. I actually have 8 products in the database, and there is a possibility of adding more products.

[diafol]

I don't understand qwhy it didn't work for you. It worked perfectly for me.
Obviously you're using different tables, but the process is the same.

That's displayed for CALL crosstab(1)

Products as column names.
This layout could be useful for comparisons (for you the supplier), but I don't see much value in it for the customer. For that I'd use a normal list.

[Webville312]

Oh ... perhaps my execution was wrong. Lemme revisit the code.

Thanx once again.

[Webville312]

Hey Guyz,

I did the layout that diafol advised, and it worked. But my boss says that he'd prefer me to use arrays rather than the stored procedures. In that case, after consulting a couple of friends, they halped me come up with the code below;

<?
/*
 * Function to prepare the Purchases VAT detail
 *
 */

function prepare_purchases_vat_detail($input_array)
{

$start_date = $input_array["start_date"];
$end_date   = $input_array["end_date"];
//$mysqli     = $input_array["mysqli"];
$mysqli = connect2DB3();

//Create the array in which we will store the Purchases VAT detail table
$purchases_vat_detail_table = array();

/*
This table is essentially represented as follows:
rows = invoices by date
The first row is the header row containing the titles of the table. The first row looks like this:
Invoice Number | Invoice Date | Received Date | <short_product_description_n> Quantity | Total Quantity | <short_product_description_n> cost | Total Cost | VAT
Columns contain the information as represented by the headers in the first row
*/

/////////////////////////////////////////////START CREATING FIRST ROW////////////////////////////////
//Creating the first row
$column_number = 0;
$purchases_vat_detail_table[0][$column_number++] = "Invoice Number";
$purchases_vat_detail_table[0][$column_number++] = "Invoice Date";
$purchases_vat_detail_table[0][$column_number++] = "Received Date";

//Query the products
$query  = "SELECT * FROM Products";
$result = $mysqli->query($query);
$number_of_products = $result->num_rows;

//Loop through the products to create the additional column headers
//<short_product_description_n> Quantity
if($number_of_products > 0)
{
    while($product_data = $result->fetch_array(MYSQLI_BOTH))
    {
        $product_number            = $product_data["product_number"];
        $short_product_description = $product_data["short_product_description"];

        //Create the header
        $purchases_vat_detail_table[0][$column_number] = $short_product_description." Quantity";

        //Create the Products to column map for quantities
        $products_quantity_to_column_map["$products_number"] = $column_number;

        //Create a Products array
        $products_array["$product_number"] = $product_data;

        $column_number++;
    }
}

//Still creating the first row
$total_quantity_column_number = $column_number;
$purchases_vat_detail_table[0][$column_number++] = "Total Quantity";

//Loop through the products to create the additional column headers
//<short_product_description_n> Cost
for($product_index = 0; $product_index < $number_of_products; $product_index++)
{
    $product_number            = $products_array[$product_index]["product_number"];
    $short_product_description = $product_data[$product_index]["short_product_description"];

    //Create the header
    $purchases_vat_detail_table[0][$column_number] = $short_product_description." Cost";

    //Create the Products to column map for the cost
    $products_cost_to_column_map["$products_number"] = $column_number;

    $column_number++;
}

//Still creating the first row by adding the Total Cost
$total_cost_column_number = $column_number;
$purchases_vat_detail_table[0][$column_number++] = "Total Cost";
$vat_column_number = $column_number;
$purchases_vat_detail_table[0][$column_number++] = "VAT";

/////////////////////////////////////////////FINISHED CREATING FIRST ROW////////////////////////////////


//Query the Beer Tax Invoice
$query = "SELECT * ";
$query.= "FROM Beer_Tax_Invoice_From_UBL ";
$query.= "WHERE document_date > '$start_date' AND document_date < '$end_date'";

$result = $mysqli->query($query);
$number_of_invoices = $result->num_rows;


//Loop through the Beer Tax Invoices
//Invoice Number | Invoice Date | Received Date | <short_product_description_n> Quantity | Total Quantity | <short_product_description_n> cost | Total Cost | VAT
$row = 1;

if($number_of_invoices > 0)
{
    while($invoice_data = $result->fetch_array(MYSQLI_BOTH))
    {
        $column_number = 0;
        $purchases_vat_detail_table[$row][$column_number++] = $invoice_data["document_number"];
        $purchases_vat_detail_table[$row][$column_number++] = $invoice_data["document_date"];
        $purchases_vat_detail_table[$row][$column_number++] = $invoice_data["arrival_date"];

        $beer_tax_invoice_from_ubl_id = $invoice_data["beer_tax_invoice_from_ubl_id"];

        //Query the Beer Tax Invoice Entry
        $entry_query = "SELECT * ";
        $entry_query.= "FROM Beer_Tax_Invoice_From_UBL_Entry ";
        $entry_query.= "WHERE beer_tax_invoice_from_ubl_id = '$beer_tax_invoice_from_ubl_id'";
        $entry_result = $mysqli->query($entry_query);
        $number_of_entries = $entry_result->num_rows;

        //For PHP this is probably not necessary but I feel safe resetting the values to 0
        //so that we can start on a clean slate
        //Reset all the values to 0 to avoid adding onto random initialized data
        for($j = $column_number; $j < $count($purchases_vat_detail_table[0]) ; $j++)
        {
            $purchases_vat_detail_table[$row][$j] = 0;
        }

        //Loop through the Beer Tax Entries
        $total_invoice_quantity = 0;
        $total_invoice_cost = 0;
        $total_invoice_vat = 0;
        while($entry_data = $entry_result->fetch_array(MYSQLI_BOTH))
        {
            //Obtaining the column number in which the product quantity should be recorded
            $product_number = $entry_data["product_number"];
            $product_quantity_column_number = $products_quantity_to_column_map["$products_number"];

            //Store the product quantity in the correct column and increment to data for the same product 
            //in the same invoice
            $purchases_vat_detail_table[$row][$product_quantity_column_number]+= $entry_data["quantity"];
            $total_invoice_quantity+= $entry_data["quantity"];

            //Obtain the column number in which the product cost should be recorded
            $product_cost_column_number = $products_quantity_to_column_map["$products_number"];

            //Store the product cost in the correct column and increment to data for the same product 
            //in the same invoice
            $purchases_vat_detail_table[$row][$product_cost_column_number]+= $entry_data["line_value_entered"];
            $total_invoice_cost+= $entry_data["line_value_entered"];

            //Calculate the total VAT
            $total_invoice_vat+= $entry_data["line_value_entered"] * $entry_data["vat_rate"];
        }

        //Store the Total Invoice Quantity
        $purchases_vat_detail_table[$row][$total_quantity_column_number]+= $total_invoice_quantity;

        //Store the Total Invoice Cost
        $purchases_vat_detail_table[$row][$total_cost_column_number]+= $total_invoice_cost;

        //Store the Total Invoice VAT
        $purchases_vat_detail_table[$row][$vat_column_number]+= $total_invoice_vat;

        $row++;
    }
}

$input_array["purchases_vat_detail_table"] = $purchases_vat_detail_table;

return $input_array;

}

?>

Now, I have never used multidimnsional arrays, and I am wondering how do I display thse records as a table.

[diafol]

Is he a sadist?

IMO there are things that php should do and there are things that mysql should do. I would use the SP. Any reason why he insists on an MD array?

[Webville312]

I have no idea, but what I know is that he is becoming difficult to manage ...

So, any idea on how to display the array above as a table?

[diafol]

Sorry, that's just so time-consuming. Anybody else.

[Webville312]

Ok. Thanx anyways.

[Webville312]

In case anyone else fcaes the same problem; here is the code I used and it worked like a charm;

<?php
/*
 * Function to prepare the Purchases VAT detail
 *
 */

//function prepare_purchases_vat_detail($input_array)
//{

$start_date = $_POST["start_date"];
$end_date   = $_POST["end_date"];
$branch_name = $_POST['branch_name'];

$mysqli = connect2DB3();

//Create the array in which we will store the Purchases VAT detail table
$purchases_vat_detail_table = array();

/*
This table is essentially represented as follows:
rows = invoices by date
The first row is the header row containing the titles of the table. The first row looks like this:
Invoice Number | Invoice Date | Received Date | <short_product_description_n> Quantity | Total Quantity | <short_product_description_n> cost | Total Cost | VAT
Columns contain the information as represented by the headers in the first row
*/

/////////////////////////////////////////////START CREATING FIRST ROW////////////////////////////////
//Creating the first row
$column_number = 0;
$purchases_vat_detail_table[0][$column_number++] = "Invoice Number";
$purchases_vat_detail_table[0][$column_number++] = "Invoice Date";
$purchases_vat_detail_table[0][$column_number++] = "Received Date";

//Query the products
$query  = "SELECT * FROM Products";
$result = $mysqli->query($query);
$number_of_products = $result->num_rows;

//Loop through the products to create the additional column headers
//<short_product_description_n> Quantity
if($number_of_products > 0)
{
    while($product_data = $result->fetch_array(MYSQLI_BOTH))
    {
        $product_number            = $product_data["product_number"];
        $short_product_description = $product_data["short_description"];

        //Create the header
        $purchases_vat_detail_table[0][$column_number] = $short_product_description." Quantity";

        //Create the Products to column map for quantities
        $products_quantity_to_column_map["$products_number"] = $column_number;

        //Create a Products array
        $products_array["$product_number"] = $product_data;

        $column_number++;
    }
}

//Still creating the first row
$total_quantity_column_number = $column_number;
$purchases_vat_detail_table[0][$column_number++] = "Total Quantity";

//Loop through the products to create the additional column headers
//<short_product_description_n> Cost
for($product_index = 0; $product_index < $number_of_products; $product_index++)
{
    $product_number            = $products_array[$product_index]["product_number"];
    $short_product_description = $product_data[$product_index]["short_product_description"];

    //Create the header
    $purchases_vat_detail_table[0][$column_number] = $short_product_description." Cost";

    //Create the Products to column map for the cost
    $products_cost_to_column_map["$products_number"] = $column_number;

    $column_number++;
}

//Still creating the first row by adding the Total Cost
$total_cost_column_number = $column_number;
$purchases_vat_detail_table[0][$column_number++] = "Total Cost";
$vat_column_number = $column_number;
$purchases_vat_detail_table[0][$column_number++] = "VAT";

/////////////////////////////////////////////FINISHED CREATING FIRST ROW////////////////////////////////


//Query the Beer Tax Invoice
$query_beer_invoices = "SELECT * 
FROM Beer_Tax_Invoice_From_UBL 
WHERE document_date >= '$start_date' AND document_date <= '$end_date'
AND invoice_address LIKE '%$branch_name%'
";

$beer_invoice_result = $mysqli->query($query_beer_invoices);
$number_of_invoices = $beer_invoice_result->num_rows;

//echo $query_beer_invoices;
//Loop through the Beer Tax Invoices
//Invoice Number | Invoice Date | Received Date | <short_product_description_n> Quantity | Total Quantity | <short_product_description_n> cost | Total Cost | VAT
$row = 1;

if($number_of_invoices > 0)
{
    while($invoice_data = $beer_invoice_result->fetch_array(MYSQLI_BOTH))
    {
        $column_number = 0;
        $purchases_vat_detail_table[$row][$column_number++] = $invoice_data["document_number"];
        $document_number_column = $invoice_data["document_number"];
        $purchases_vat_detail_table[$row][$column_number++] = $invoice_data["document_date"];
        $document_date_column = $invoice_data["document_date"];
        $purchases_vat_detail_table[$row][$column_number++] = $invoice_data["arrival_date"];
        $arrival_date_column = $invoice_data["arrival_date"];

        $beer_tax_invoice_from_ubl_id = $invoice_data["beer_tax_invoice_from_ubl_id"];

        //Query the Beer Tax Invoice Entry
        $entry_query = "SELECT * ";
        $entry_query.= "FROM Beer_Tax_Invoice_From_UBL_Entry ";
        $entry_query.= "WHERE beer_tax_invoice_from_ubl_id = '$beer_tax_invoice_from_ubl_id'";
        $entry_result = $mysqli->query($entry_query);
        $number_of_entries = $entry_result->num_rows;

        //For PHP this is probably not necessary but I feel safe resetting the values to 0
        //so that we can start on a clean slate
        //Reset all the values to 0 to avoid adding onto random initialized data
        //for($j = $column_number; $j < $count($purchases_vat_detail_table[0]); $j++)
        //{
         // $purchases_vat_detail_table[$row][$j] = 0;
        //}
        //Loop through the Beer Tax Entries
        $total_invoice_quantity = 0;
        $total_invoice_cost = 0;
        $total_invoice_vat = 0;

        while($entry_data = $entry_result->fetch_array(MYSQLI_BOTH))
        {
            //Obtaining the column number in which the product quantity should be recorded
            $product_number = $entry_data["product_number"];
            $product_quantity_column_number = $products_quantity_to_column_map["$products_number"];

            //Store the product quantity in the correct column and increment to data for the same product 
            //in the same invoice
            $purchases_vat_detail_table[$row][$product_quantity_column_number]+= $entry_data["quantity"];
            $total_invoice_quantity+= $entry_data["quantity"];

            //Obtain the column number in which the product cost should be recorded
            $product_cost_column_number = $products_quantity_to_column_map["$products_number"];

            //Store the product cost in the correct column and increment to data for the same product 
            //in the same invoice
            $purchases_vat_detail_table[$row][$product_cost_column_number]+= $entry_data["line_value_entered"];
            $total_invoice_cost+= $entry_data["line_value_entered"];

            //Calculate the total VAT
            $total_invoice_vat+= $entry_data["line_value_entered"] * $entry_data["vat_rate"];
        }

        //Store the Total Invoice Quantity
        $purchases_vat_detail_table[$row][$total_quantity_column_number]+= $total_invoice_quantity;

        //Store the Total Invoice Cost
        $purchases_vat_detail_table[$row][$total_cost_column_number]+= $total_invoice_cost;

        //Store the Total Invoice VAT
        $purchases_vat_detail_table[$row][$vat_column_number]+= $total_invoice_vat;

        $row++;
    }
}

$input_array["purchases_vat_detail_table"] = $purchases_vat_detail_table;

//return $input_array;

//}
// Display the table starting with row 1
echo "<table width=\"922\" border=\"0\" class=\"listing\" cellpadding=\"0\" cellspacing=\"0\"><tr>";
$first_row = 0;
echo "<td>".$purchases_vat_detail_table[$first_row][0]."</td>";
echo "<td>".$purchases_vat_detail_table[$first_row][1]."</td>";
echo "<td>".$purchases_vat_detail_table[$first_row][2]."</td>";

// Get the total number of products and add them to the index of columns so far used. This is to get the condition for our for loop
$total_number_of_product_columns = 3+$number_of_products;
//echo $total_number_of_columns;
$num_of_cols = 3;
    // Loop through the products
    for($col = $num_of_cols; $col < $total_number_of_product_columns; $col++)
    {
        echo "<td>".$purchases_vat_detail_table[$first_row][$col]."</td>";
    }
echo "<td>".$purchases_vat_detail_table[$first_row][$total_quantity_column_number]."</td>";
echo "<td>".$purchases_vat_detail_table[$first_row][$total_cost_column_number]."</td>";
echo "<td>".$purchases_vat_detail_table[$first_row][$vat_column_number]."</td>";
echo "<tr>";
/////////////////////////////////////////////FINISHED CREATING FIRST ROW OF THE TABLE////////////////////////////////

// Create the second row of the display table
// First initialise the row index as 1 and loop through the records.

for ($table_row = 1; $table_row < $number_of_invoices; $table_row++)
{ 
echo "<tr>";
// Create the first column as well
    for ($table_column = 0; $table_column < 11; $table_column++)
    {
        echo "<td>".$purchases_vat_detail_table[$table_row][$table_column]."</td>";

    }
    echo "</tr>";
}
echo "<table>";
?>

Thanks to everyone for the help.

[diafol]

Thank you webville.