CODEWITHSUNDEEP
phpmysqlsqljoin
AleksanderK
AleksanderK

Reputation: 31

Problems with invoice from two tables

I am stuggling with a SQL query that has been bothering me for the last couple of days. And I'm hoping that you guys can help me!

I am going to create a invoice for a shopping cart. The invoices are saved in two tables. The first table "ordre" have the info about the user, whether the invoice is paid or not and the date, etc.. The second table "ordreelementer" hold all the items that belongs to that invoice.

Here comes my problem. How on earth do I get alle the information needed to print a invoice with details about the customer, and the items he/she purchased? I have tried different SQL JOINS but that didn't work as expected. Any help here would be highly appreciated! I'm coding i PHP and running a MySQL database.

This is my latest SQL statement

$sql = "SELECT ordre.id, 
               ordre.uid, 
               ordre.dato, 
               ordre.status, 
               ordre.betalt, 
               ordre.navn, 
               ordre.adresse, 
               ordreelementer.oid,
               ordreelementer.navn AS elnavn,  
               SUM(ordreelementer.ant * ordreelementer.stkpris) AS tot
          FROM ordre 
          INNER JOIN ordreelementer 
               ON ordre.id=ordreelementer.oid 
          WHERE ordre.uid = ".brukerInfo('id')." AND ordre.id = ".$id."";

All help is highly appreciated!

Upvotes: 3

Views: 255

Answers (3)

Alden W.
Alden W.

Reputation: 1439

This will give you the sum of the total order, and your order elements will come out as comma separated values.

SELECT ordre.id, 
           ordre.uid, 
           ordre.dato, 
           ordre.status, 
           ordre.betalt, 
           ordre.navn, 
           ordre.adresse, 
           ordre.id,
           GROUP_CONCAT(ordreelementer.navn) AS elnavn,  
           SUM(ordreelementer.ant * ordreelementer.stkpris) AS tot
      FROM ordre 
      INNER JOIN ordreelementer 
           ON ordre.id=ordreelementer.oid 
      WHERE ordre.uid = ".brukerInfo('id')." AND ordre.id = ".$id.""
      GROUP BY ordre.id;

Alternately you could group it by the order element id, and use the WITH ROLLUP option. This would give you a separate record for each element and would generate a grand total row for you at the end.

SELECT ordre.id, 
       ordre.uid, 
       ordre.dato, 
       ordre.status, 
       ordre.betalt, 
       ordre.navn, 
       ordre.adresse, 
       ordreelementer.oid,
       ordreelementer.navn AS elnavn,  
       SUM(ordreelementer.ant * ordreelementer.stkpris) AS eltot
  FROM ordre 
  INNER JOIN ordreelementer 
       ON ordre.id=ordreelementer.oid 
  WHERE ordre.uid = ".brukerInfo('id')." AND ordre.id = ".$id.""
  GROUP BY ordereelementer.id WITH ROLLUP;

This last grouping being a pseudo group, as each group will have only 1 element item.

Upvotes: 1

Suvash sarker
Suvash sarker

Reputation: 3170

$sql = "SELECT ordre.id, 
               ordre.uid, 
               ordre.dato, 
               ordre.status, 
               ordre.betalt, 
               ordre.navn, 
               ordre.adresse, 
               ordreelementer.oid,
               ordreelementer.navn AS elnavn,  
               ordreelementer.ant as val1,
               ordreelementer.stkpris AS val2
          FROM ordre 
          INNER JOIN ordreelementer 
               ON ordre.id=ordreelementer.oid 
          WHERE ordre.uid = ".brukerInfo('id')." AND ordre.id = ".$id."";

this query return all values.Now you have to calculate your total value when you generate your invoice through a loop.

Upvotes: 0

Chad Moore
Chad Moore

Reputation: 764

Since you are simply populating an invoice, my thoughts are to break this query down into several queries unless you need it to be one query for some reason whether purist or practical. This will keep you from duplicated data for all of that ordre information on every line of your results set. Regardless, to get you off and running and printing invoices the following should work.

First, pull the data you only need in one row from the ordre table, that is, your basic order information:

$sqlOrdreInfo = "SELECT ordre.id, 
                        ordre.uid, 
                        ordre.dato, 
                        ordre.status, 
                        ordre.betalt, 
                        ordre.navn, 
                        ordre.adresse
                FROM ordre 
                WHERE ordre.uid = ".brukerInfo('id')." AND ordre.id = '.$id.'";

Second, pull the individual order lines:

$sqlOrdreLines = "SELECT ordreelementer.oid,
                         ordreelementer.navn AS elnavn
                  FROM ordreelementer 
                  WHERE  ordreelementer.oid = '.$id.'";

Third, pull the sum that you wanted:

$sqlOrdreTotal = "SELECT SUM(ordreelementer.ant * ordreelementer.stkpris) AS tot
                  FROM ordreelementer 
                  WHERE  ordreelementer.oid = '$id.'
                  GROUP BY ordreelementer.oid";

That will give you 3 results sets and you can easily print the data you need on the invoice.

Upvotes: 0

Related Questions