Mysql -> getting results from two tables?

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-21-2009

[eluser]BobbyB[/eluser]
Hi,
I am wondering how to get the results from two of my database tables.
They are structured like this:

Table 1(users):

Code:
uid | username | first_name...

Table 2(invoices):

Code:
id | username | date_created | date_due | paid...

Now what I am trying to do is to get the user info and to get all invoices of the user(and therefore be able to see if there are invoices that are due).
I have tried to do it like this:

Code:
$query = $this->db->query('SELECT users.*, invoices.date_due, invoices.paid FROM users LEFT JOIN invoices ON users.username = invoices.username');

But if a user has two invoices, there will also be two entries of the same user in the array.
I am kinda lost...

Thanks in advance!

Cheers

Mysql -> getting results from two tables? - El Forum - 05-21-2009

[eluser]alejandra[/eluser]
Yes when you join two tables with one to many relatioship you will get multiple records, but what is it that you want to do? What is the output you need?

Mysql -> getting results from two tables? - El Forum - 05-21-2009

[eluser]Dam1an[/eluser]
If you just want a list of users with invoives, you could use the DISTINCT keyword, but when joining, you expect to get multiple rows

Mysql -> getting results from two tables? - El Forum - 05-21-2009

[eluser]BobbyB[/eluser]
Hey,
thanks for your replies.
The output that I need would be all of the users in an array.
In addition to that, each user should have his invoices' data(date_due,paid),if there is any, included in the array.

I probaly need an array inside the array?

So I can go:

Code:
foreach($users as $user)

{...

and then inside the foreach I want to check if the specific user has any invoices that are due.
Like this:

Code:
if( !empty($user['date_due'])){ 

$date1 = $user['date_due'];

$date2 = time();

$dateArr = explode("-",$date1);

$date1Int = mktime(0,0,0,$dateArr[1],$dateArr[2],$dateArr[0]);

if(($date1Int-$date2)<0 && $users['paid']=='NO'){echo 'due';}else{echo 'not due';};

Thanks for your help.

Mysql -> getting results from two tables? - El Forum - 05-21-2009

[eluser]alejandra[/eluser]
Yes, from your description it seems like you will need an array inside and array but if your displaying the information you don't need to put it an array...

Code:
//assuming that $query_result has the result from your query

$query_result = $query_result->result();

$prev_username = '';

foreach($users as $user)

{

  //echo user information

  //get the username

    if($prev_username != $user->username)

    {

        //Display User information

         echo $user->first_name.' ';

         echo $user->last_name.'<br />';

       //display invoice data

         echo $user->date_created.' ';

         echo $user->date_due.' ';

         echo $user->paid.'<br />';

    }

    else //if your processing a record from the same user just print the invoice for that record

    {

        //display other invoice from same user        

         echo $user->date_created.' ';

         echo $user->date_due.' ';

         echo $user->paid.'<br />';

    }

    $prev_username = $row->username //getting the current user name to compare with the next user name

}

Make sure that records from same users are toguether ...

Hope this helps you Smile

Mysql -> getting results from two tables? - El Forum - 05-22-2009

[eluser]BobbyB[/eluser]
Hey alejandra,
thanks for your help.
This looks like an interesting solution.
Why do I never come up with sth like that? Smile

I will investigate further and will try to get that array inside array solution working.

Thank you very much for your time and insight.

Cheers

Bob

Mysql -> getting results from two tables? - El Forum - 05-22-2009

[eluser]Johan André[/eluser]
I would do it like this:

users
id
username
...
...

invoices
id
user_id
...
...

Using an id in the invoices table to refer users is more correct. What if a user with many invoices changes his username? Then you would have to update the username-colum in all invoices.

Get this list of invoices for a user with:

Code:
$this->db->select('*');

$this->db->from('invoices')

$this->db->join('users', 'users.id = invoices.user_id', 'inner');

$this->db->where('users.id', $user_id);

$result = $this->db->get()->result_array();

And then in view:

Code:
foreach($result AS $item) : 

...

endforeach;

Mysql -> getting results from two tables? - El Forum - 05-22-2009

[eluser]BobbyB[/eluser]
Hi Johan,
thanks for your reply.
The usernames are unique and cannot be changed so that is not a problem.
Basically all I want to do is to display if the user has any invoices that are due.
I actually dont even need to display all the invoices I just want to check, if there are any, that are due.

I want to print out all the users:

user1 : first_name : last_name : due_invoices=yes
user2 : first_name : last_name : due_invoices=no
user3 : first_name : last_name : due_invoices=yes

and so on...

Therefore, I somehow have to include all invoices' data(date_due,...) of a user in the user array without duplicating the user(like I wrote in my first post).
Then I was going to check for each user if he has invoices that are due or not.

I hope I am being clear.

Thanks for your help.

Mysql -> getting results from two tables? - El Forum - 05-22-2009

[eluser]Johan André[/eluser]
well...

Code:
TABLE: users

id

username

TABLE: invoices

id

user_id

amount

is_due

Code:
$this->db->select('*');

$this->db->from('users');

$this->db->join('invoices', 'invoices.user_id = users.id', 'inner');

$this->db->where('users.id', $user_id); // Remove this to list all users

$this->db->where('invoices.is_due', 1); 

$result = $this->db->get()->result_array();

EDIT: Haha, just read your post more carefully... Instead of checking is_due select all invoices that has a due-date prior to todays date!

Mysql -> getting results from two tables? - El Forum - 05-22-2009

[eluser]BobbyB[/eluser]
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!