两张桌子的发票问题

Question

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!

Answer 1

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.