Accounting Database Relation Problem.?!?

Relational Database Problems

[Sherif]

Hey Guys,


First of all I would like to thank all of those people here for all of the tremendous efforts that you spend in helping us here around.

I am currently facing 2 problems,

1) When i declare a variable and set a value for it using the

$variable="value";

Concept, the problem that I am facing is that when I go to another page, the value isn't stored on the server...

I was wondering if there is anything special that i should do to keep the values stored for later reference other than using session variables?


2) I am working on an accounting system for a company.. the problem that i am facing is that for example they are selling expensive products, and therefore they divide the total price of the product for multiple installments.

After that, the employees should enter the invoices and values of the installments with the corresponding date so that the prices and invoices cancel out.

The problem is that sometimes a customer can come, and pay the installments on two parts on different days... how can i set the database structure so that i can compare the multiple inputs with the primary actual input so that i can calculate the delays?


Thank you so much for your support, and i hope to hear a responses and solutions soon.



Sincerely,
Sherif

[Sherif]

Hey Guys...

It's been a while since we have been posting here....

Until now everything seems to be working well in terms of the date calculations...

I am currently facing a problem in my MySQL Query.

SELECT invoices.* , payments.* FROM invoices FULL JOIN payments ON invoices.invoice_id = payments.invoice_id AND invoices.product_id = payments.product_id AND invoices.category_id = payments.category_id WHERE invoices.product_id = 1 AND invoices.category_id = 101

I got the concept of the

FULL JOIN

from [w3schools.com ]


but when i test the query in DreamWeaver, i get the following error :

MySQL Error#: 1051
Unknown Table 'invoices'

The thing is that when i substitute the

FULL JOIN

with

LEFT JOIN

The Query works fine with no problem since the tables actually exist.

The thing is that usually the payments are associated with the invoices, but sometimes the customer pays a down payment, which is not associated with any invoice, that's why i need a full join that will join the items from both tables in to one table based on the invoice_id, product_id, and category_id, since there is a unique product id for each product.


Thanks for your support.


Sincerely,
Sherif.

[Sherif]

Hey Guys...

I've managed to reach a good solution regards the FULL JOIN Command.

I use the LEFT JOIN, UNION ALL, RIGHT JOIN, and A WHERE col_name is null condition to avoid duplicates...


The output of the query is correct but the main problem that i have is that when there is no match ex. left tables has values and the right table doesn't have joining values, then the output is displayed correctly, but the values of the joined columns do not appear even having them in the LEFT JOIN command.

Does anyone have any ideas to make MySQL display the values of the joined columns even if there is no match? since if there is a match, the output is displayed correctly.

Thanks a lot for you support.

Sincerely,
Sherif