Welcome Guest, Not a member yet? Register   Sign In
Mysql -> getting results from two tables?
#11

[eluser]Johan André[/eluser]
[quote author="BobbyB" date="1243010202"]Thanks again for your help.
But what if a user does not have any invoices yet?
I am also still getting double entries in the users array if a user has more than 1 invoice.

The closest I got was using the query in my first post.
But using "join" I always produce duplicate entires in my array(if a user has more than 1 invoice).

I will probably have to create a sub-array(as alejandra said) with all the invoices a user has and then check for due ones.
Kinda like this:

array(user1=>first_name=>last_name=>sub_array(invoices_user1=>invoice1=>invoice2))user2=>....

Any hints on how to do this?

Thanks in advance![/quote]

Try adding
Code:
$this->db->group_by('users.id');
in the query. That should give you one row per user, but only users having invoices that are past due...
#12

[eluser]Johan André[/eluser]
Heres a raw sql-query to get you started:

Code:
SELECT
    *,
    SUM(invoices.amount) AS total_amount_due,
    COUNT(invoices.id) AS number_of_due_invoices
FROM
    users
INNER JOIN
    invoices ON users.id = invoices.user_id
WHERE
    invoices.due_date < NOW()
GROUP BY
    users.id
ORDER BY
    total_amount_due DESC,
    users.username ASC

It will bring you a list off all users that have invoices that are past due. It will also show the totalt amount due and the number of invoices that has passed the due-date. The list is sorted by the username that has the largest amount due first. If two username has an equally amount due they are listed in alphabetically order.

I've checked it locally and it works...
#13

[eluser]BobbyB[/eluser]
Hey Johan,
thanks for spending so much time trying to help me.
Is it possible to also include the users that have no invoices at all or no invoices past due?

I was trying to display all users and then just mark the ones with invoices past due.
I dont actually need to display invoices or the total amount and stuff like that.

For example(userlist):
USER1 | username | first_name <- gets green background because no invoices past due
USER2 | username | first_name <- gets green background because no invoices past due
USER3 | username | first_name <- gets RED background because he has invoices past due

Ufff, I did not think that this would be so hard Smile

Thank you all for your patience!
#14

[eluser]Johan André[/eluser]
Hey!

No problem! I like to help! Smile

Code:
SELECT
    *,
    (SELECT SUM(invoices.amount) FROM invoices WHERE invoices.due_date < NOW() AND invoices.user_id = users.id) AS total_amount_due,
    (SELECT SUM(invoices.amount) FROM invoices WHERE invoices.user_id = users.id) AS total_amount
FROM
    users
LEFT JOIN
    invoices ON users.id = invoices.user_id
GROUP BY
    users.id
ORDER BY
    users.username ASC

This code will list all your users. "total_amount_due" is the total of all invoices due. "total_amount" is the total of all invoices (including the ones that past the due-date). If a user does not have any invoices the "total_amount_due" and "total_amount" will be NULL.
#15

[eluser]BobbyB[/eluser]
You sure do :-)

I just tried:
Code:
$query = $this->db->query('SELECT users.*, (SELECT invoices.paid FROM invoices WHERE invoices.date_due < NOW() AND invoices.paid = "NO" AND invoices.username = users.username) AS is_paid
FROM
    users
LEFT JOIN
    invoices ON users.username = invoices.username
GROUP BY
    users.username
ORDER BY
    users.username ASC ');

Which I think works and gives me an
Code:
is_paid=NO
in my users array everytime an invoice is past due and not paid.
So I can check with:
Code:
!empty
if the user has unpaid/due invoices.

Yesssssssss!

Thanks again - I really learned a lot by your mysql queries.
That will be really useful for me in future projects.

Cheers
#16

[eluser]Johan André[/eluser]
[quote author="BobbyB" date="1243029343"]You sure do :-)

Thanks again - I really learned a lot by your mysql queries.
That will be really useful for me in future projects.

Cheers[/quote]

No problem! I actually learn myself alot by helping others! Smile

Glad you got it rolling!

Cheers!
#17

[eluser]bluepicaso[/eluser]
Thank you so much people. you guys are great....My Internet was down for 2 days. and was stuck in getting results from 2 tables... thank you guys.... Smile
god bless you all Smile




Theme © iAndrew 2016 - Forum software by © MyBB