Combine results for SQL query

Question

I am in a bit of a pinch. I am using WHMCS and building a custom report but am running into trouble with one of my queries. Here is an overview:

I have 2 tables; tblinvoices which has the subtotal, tax, total, etc. of the invoice and tblinvoiceitems which has the individual line items that appear on the invoice. I am wanting to run a query that returns all of the individual line items and their price which I have been able to do. I run into problems when I do 'GROUP BY' and group the results by invoice numder, then it only returns the first line item for each invoice. I want to group them by invoice number so it only has 1 line in the report. Here is my query:

$query = "SELECT date_format(tblinvoices.datepaid,'%m-%d-%Y') AS datepaid,
    tblinvoices.userid,
    tblinvoices.id,
    tblinvoices.subtotal,
    tblinvoices.credit,
    tblinvoices.tax,
    tblinvoices.tax2,
    tblinvoices.total,
    tblinvoices.taxrate,
    tblinvoices.taxrate2,
    tblinvoiceitems.description,
    tblinvoiceitems.amount,
    tblinvoices.status,
    FROM tblinvoices
    INNER JOIN tblinvoiceitems ON tblinvoices.id = tblinvoiceitems.invoiceid
    GROUP BY tblinvoices.id";
$result = mysql_query($query);

# Math Operations
$statement = array();
$count = 0;

if ($result !== false) {
    while ($data = mysql_fetch_object($result)) {
        $invoiceid = $data->id;
        $datepaid = $data->datepaid;
        $description = $data->description;
        $item_amount = $data->item_amount;
        $subtotal = $data->subtotal;
        $credit = $data->credit;
        $tax = $data->tax;
        $tax2 = $data->tax2;
        $total = $data->total;

        if ($export != true) {
            $client_link = '<a href=clientssummary.php?userid='.$data->userid.'>'.$data->userid;
            $invoice_link = '<a href=invoices.php?action=edit&id='.$data->id.'>'.$data->id;
        }
        else {
            $client_link = $data->userid;
            $invoice_link = $data->id;
        }
        if (strpos($description, 'Setup') !== false) {
            $setup = $item_amount;
        }
        else {
            $setup = 0;
        }
        if (strpos($description, 'Addon') !== false) {
            $addon = $item_amount;
        }
        else {
            $addon = 0;
        }
        if (strpos($description, 'Tax Guide: No => Yes') !== false) {
            $taxguide = $item_amount;
        }
        else {
            $taxguide = 0;
        }
        if (strpos($description, 'Reading Rack Bundle') !== false) {
            $reading = $item_amount;
        }
        else {
            $reading = 0;
        }
        if (strpos($description, 'Toolkit Bundle') !== false) {
            $toolkit = $item_amount;
        }
        else {
            $toolkit = 0;
        }
        $hosting = $subtotal - $setup - $addon - $taxguide - $reading - $toolkit;

        $statement[$invoiceid."_".$count] = array($datepaid,$client_link,$promo,$dtn,$company,$state,$invoice_link,$setup,$addon,$taxguide,$reading,$toolkit,$hosting,$subtotal,$credit,$tax+$tax2,$total);
        $count++;
    }
}

foreach ($headings AS $k=>$v) {
    $reportdata["tableheadings"][] = $v;
}

//ksort($statement);
foreach ($statement AS $invoiceid=>$entry) {
    $reportdata["tablevalues"][] = array(
        $entry[0], // datepaid
        $entry[1], // clientid
        $entry[2], // promocode
        $entry[3], // dtn
        $entry[4], // companyname
        $entry[5], // state
        $entry[6], // invoiceid
        formatCurrency($entry[7]), // setup
        formatCurrency($entry[8]), // addon
        formatCurrency($entry[9]), // taxguide
        formatCurrency($entry[10]), // reading
        formatCurrency($entry[11]), // toolkit
        formatCurrency($entry[12]), // hosting
        formatCurrency($entry[13]), // subtotal
        formatCurrency($entry[14]), // credit
        formatCurrency($entry[15]), // tax
        formatCurrency($entry[16]) // total
    );
}
mysql_free_result($result);

I will be happy to provide any additional information/code if it helps. I though this might be a more general type question...thanks!

Your question is unclear. Can you give some example data, show what result you get when your table contains only that example data, and show what result you want? Maybe you could even create a http://sqlfiddle.com/ — Mark Byers, Jun 11 '12 at 15:46
Your question isn't clear. Post some examples (input and expected output). — aF., Jun 11 '12 at 15:51
Needs clarification. You only want one line, but you want multiple line items per line? Seeing what you want and what you get as Mark Byers suggested would be helpful. — colonelclick, Jun 11 '12 at 15:52
So you can see all of my if statements that look at the item description then assign the item amount based on the description. — Drewness, Jun 11 '12 at 16:30

score 1 · Answer 1 · answered Jun 11 '12 at 15:52

1

So try group by invoice.id and invoice.userid, and then maybe invoice.subtotal....

May run into some issues if you later decide to aggregate anything but this should do it.

answered Jun 11 '12 at 15:52

Hituptony

2,740
3
22
44

score 0 · Accepted Answer · answered Jun 11 '12 at 15:53

0

With the result you are describing your result set would look like:

userid | id | subtotal | credit | tax | status | desc | amount
______________________________________________________________

     1 |  1 |       20 |      0 |  .7 |      1 | item1|     10
     1 |  1 |       20 |      0 |  .7 |      1 | item2|     10
     1 |  2 |       30 |      0 | 2.1 |      1 | item3|     15
     1 |  2 |       30 |      0 | 2.1 |      1 | item3|     15
     1 |  3 |       10 |      0 |  .7 |      0 | item1|     10
     1 |  4 |        1 |      0 | .07 |      0 | item4|      1

Is this what you are looking for?

answered Jun 11 '12 at 15:53

Mike Mackintosh

13,917
6
60
87

This is what I get if I do not include the 'GROUP BY' statement. I would like to get all of the items to appear on one line. – Drewness Jun 11 '12 at 16:24
For that you should look into a UNION ALL – Mike Mackintosh Jun 11 '12 at 17:37
Could you give me an example? Currently I only have 1 SELECT statement...thanks! – Drewness Jun 11 '12 at 17:42
Look through this, http://stackoverflow.com/questions/4027212/how-to-combine-results-from-multiple-tables-with-different-columns it may give you some pointers! – Mike Mackintosh Jun 11 '12 at 18:00
Well six, this got me 10 times closer than I was. Thanks for the help! – Drewness Jun 13 '12 at 14:34
No problem, next time I'll get you an actual answer :D – Mike Mackintosh Jun 13 '12 at 20:47

Combine results for SQL query

2 Answers2

Linked