JCD JCD - 5 months ago 9
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.

<?php

include_once("connection.php");


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

echo $data['total'];


?>


Unfortunately this is not working.

Answer

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 
FROM 
invoicetable as i, 
logintable as l
WHERE
l.email='".$_SESSION['email']."'
&& 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
Comments