Mysql -> getting results from two tables? - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Mysql -> getting results from two tables? (/showthread.php?tid=18893) Pages:
1
2
|
Mysql -> getting results from two tables? - El Forum - 05-22-2009 [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'); Mysql -> getting results from two tables? - El Forum - 05-22-2009 [eluser]Johan André[/eluser] Heres a raw sql-query to get you started: Code: SELECT 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... Mysql -> getting results from two tables? - El Forum - 05-22-2009 [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 Thank you all for your patience! Mysql -> getting results from two tables? - El Forum - 05-22-2009 [eluser]Johan André[/eluser] Hey! No problem! I like to help! Code: SELECT 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. Mysql -> getting results from two tables? - El Forum - 05-22-2009 [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 Which I think works and gives me an Code: is_paid=NO So I can check with: Code: !empty Yesssssssss! Thanks again - I really learned a lot by your mysql queries. That will be really useful for me in future projects. Cheers Mysql -> getting results from two tables? - El Forum - 05-22-2009 [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! Glad you got it rolling! Cheers! Mysql -> getting results from two tables? - El Forum - 08-20-2009 [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.... god bless you all |