JCD JCD - 1 year ago 62
MySQL Question

MySQL Query that counts from one table where the email on table 1 is the same from table 2

I have two tables:

  • Login Table

  • Invoice Table

Login table has a column labeled email for the user logged in. The Invoice table also has a column labeled email for the user that filled out for the form.

I want find out how many invoices the login user filled out.



$result = mysql_query("SELECT count(*) as total FROM invoicetable, logintable WHERE email='".$_SESSION['email']."'");

echo $data['total'];


Unfortunately this is not working.

Answer Source

The issue with your query was that you didn't JOIN them by any field nor reference them in your WHERE clause. I think Gordon's suggestion of never using commas over JOIN in the FROM clause is more a matter of preference and using either won't effect performance of your query.

To write this query as using implicit joins by comma would look like this (i'm including alias):

SELECT count(distinct i.id) as total 
invoicetable as i, 
logintable as l
&& l.email = i.email

I'm just assuming you are using the email as a key to join your logintable with the invoicetable here. If you use another key, such as user_id, just replace "email" field with that in:

&& l.email = i.email
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download