sql join and display help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

sql join and display help

Post by gurjit »

Hi,

I have three tables

Code: Select all

tbl_invoice_number
invnoid, int(11), primary
invno_date, date


tbl_invoice_payment
invid, int(11), primary
ivp_date_in, date
inv_payment_amount, float
frn_invnoid, int(11) // the_invoice_number which the payment belongs to

tbl_invoice_item 
iviid, int(11), primary
ivi_date_in, date 
ivi_unit_price, float // price of the product
ivi_product_code, varchar(100) // name of product brought
frn_invnoid, int(11) // the_invoice_number which the payment belongs to
What I want to do is when I pass through the "invnoid", I want to bring all the payments and items relating to this invnoid.

The output must come in date order.....

Any ideas how I can achieve this?????
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post by ol4pr0 »

User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: sql join and display help

Post by RobertGonzalez »

gurjit wrote:

Code: Select all

tbl_invoice_number
invnoid, int(11), primary
invno_date, date


tbl_invoice_payment
invid, int(11), primary
ivp_date_in, date
inv_payment_amount, float
frn_invnoid, int(11) // the_invoice_number which the payment belongs to

tbl_invoice_item 
iviid, int(11), primary
ivi_date_in, date 
ivi_unit_price, float // price of the product
ivi_product_code, varchar(100) // name of product brought
frn_invnoid, int(11) // the_invoice_number which the payment belongs to

Code: Select all

<?php
$sql = "SELECT n.*, p.*, i.* 
        FROM tbl_invoice_item i 
            INNER JOIN tbl_invoice_payment p 
                ON i.frn_invnoid = p.frn_invnoid
        INNER JOIN tbl_invoice_number n 
            ON p.frn_invnoid = n.invnoid 
        WHERE n.invnoid = $user_supplied_invoice_number";
?>
Will this work?
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

only brings out the invoice with a payment in tbl_invoice_payment and also brings the same inv_payment_amount fpr each record.

anyway of splitting the payments. I only want to pull out

inv_payment_amount and ivi_unit_price

as individual records

e.g.
ivi_unit_price, ivp_amount_paid
220.00,0.00
210.00,0.00
0.00,500.00
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Code: Select all

<?php
$sql = "SELECT p.inv_payment_amount, i.ivi_unit_price 
        FROM tbl_invoice_item i 
            INNER JOIN tbl_invoice_payment p 
                ON i.frn_invnoid = p.frn_invnoid
        WHERE i.frn_invnoid = $user_supplied_invoice_number";
?>
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

This works well, if a "inv_payment_amount" exists but if an invoice does not have a "inv_payment_amount" made to it then the items should still come out.....

do i need to use a outer join?
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

I have been trying but no luck anyone have an answer....

i tried all the joins

what I want is a display like this:

ivi_unit_price, ivp_amount_paid
220.00,0.00
210.00,0.00
0.00,500.00


if no ivi_unit_price OR ivp_amount_paid exist then a 0.00 get display.

There will always be a ivi_unit_price but not necessarly any ivp_amount_paid.

This is because a line item exists but no payments may have been made
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

gurjit wrote:This works well, if a "inv_payment_amount" exists but if an invoice does not have a "inv_payment_amount" made to it then the items should still come out.....

do i need to use a outer join?
I don't think this is going to happen. Since you are using a JOIN and a WHERE based on an invoice number, the database will return all JOINED records where the supplied invoice number statisfies the query based on the join. You are not going to be able to pull from two tables that are joined on a field where one of the fields returns empty.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

I tried this but it always brings out

ivi_unit_price, ivp_amount_paid
220.00,500.00
210.00,500.00
220.00,500.00

There is one payment of 500.00 in tbl_invoice_payment and 3 invoice items in tbl_invoice_item, for each item it brings the same ivp_amount_paid (500.00).....

Code: Select all

select i.ivi_unit_price,  p.ivp_amount_paid
from tbl_invoice_item i left join tbl_invoice_payment p
on i.frn_invnoid = p.frn_invnoid
WHERE i.frn_invnoid = 17 AND i.ivi_status = 1

What other method of bringing out the data would you suggest?

What i'm trying to do is produce an invoice with the unit price and payments made for the invoice and want to order the unit price and payment in date order........

should I use an array, put all the information in an array and then sort using php?
what array should I use??? I will be outputting something like this on invoice

Item name, credit, debit, balance
basinstoke 1, 220.00,,220.00
0001 transaction,,100.00,120.00
basingstoke 2,120.00,,240.00
Last edited by gurjit on Tue Mar 21, 2006 8:24 am, edited 1 time in total.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

That's my typical way of doing it. If it is not some outrageously large result set then I just grab the data that is closest to what I need and use my code to make it pretty.

Something to try... write one query to grab the invoice items. Write another query to grab the payments. Match them up code-side. See if it works.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

what type of array would you use to output the result I have described in the post before your last post?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I think your database table structure might need some work. As I was writing a small piece of code to loop through the values I noticed something...

There is nothing that ties your items table and payment table together except the invoice ID. This means that payments will be applied to an invoice, not an item. So to get what you want, you are going to have to modify your item table and/or payment table by adding a key for the two to join by. This is the only way you will be able to see payments against an item rather than an entire invoice.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

Hi,

the idea is to make a payment against an invoice and not a item.

A invoice will have many items
A invoice will have many payments
The payment will be made against an invoice.

So for example the display I WANT is as below in date order and mix records:
Invoice number: 0001 // this comes from tbl_invoice_number

Date,Item, unit price, debit, credit, total

19/03/2006,basingstoke 1, 220.00, 220.00,0.00, 220.00 // this comes from tbl_invoice_item

19/03/2006,payment,0.00,0.00,10.00,210.00 // this comes from tbl_invoice_payment

20/03/2006,basingstoke 2, 220.00, 220.00,0.00, 430.00 // this comes from tbl_invoice_item

21/03/2006,payment,0.00,0.00,30.00,400.00 // this comes from tbl_invoice_payment
the tables "tbl_invoice_item" and "tbl_invoice_payment" both have a date_in field which is the date, the record was inserted. I want to display the list in date order as above.

Thats the whole reason for writing this query, otherwise I could have output the tbl_invoice_item first and tbl_invoice_payment afterwards and the display would then be like:


THIS IS WHAT I DON'T WANT:
Invoice number: 0001 // this comes from tbl_invoice_number

Date,Item, unit price, debit, credit, total

19/03/2006,basingstoke 1, 220.00, 220.00,0.00, 220.00 // this comes from tbl_invoice_item

20/03/2006,basingstoke 2, 220.00, 220.00,0.00, 440.00 // this comes from tbl_invoice_item

19/03/2006,payment,0.00,0.00,10.00,430.00 // this comes from tbl_invoice_payment

21/03/2006,payment,0.00,0.00,30.00,400.00 // this comes from tbl_invoice_payment
Post Reply