加入3表mysql php

Question

Im not sure on the terminology but i basically have a 2 table left join right now like this:

Snippets Table
snippet_id PK
snippet_text

snippets_link_email_id Table
snippet_id * (Links to Snippets Table)
email_id * (Links to emails Table)

emails Table
email_id 
version_no

lots of other various data

So whats happening is a log is parsed and inserted into the emails table, each record has details about the log text, the errors found and various information about the user.

The main info i need from each log is the error, each unique error is stored in the snippets Table.

Since there might be many emails with the same error i like to run the following query to see how many unique errors there were:

$total_exception_check = mysql_query("SELECT * FROM snippets");
$total_exceptions = mysql_num_rows($total_exception_check);

while ($row = mysql_fetch_array($total_exception_check))
{
$i = $row['snippet_id'];
//Need to find total occurrences of this error

$feedback_query = mysql_query("SELECT * FROM snippets LEFT JOIN snippets_link_email_id ON snippets.snippet_id = snippets_link_email_id.snippet_id WHERE snippets_link_email_id.snippet_id = $i AND snippet_date BETWEEN CURRENT_TIMESTAMP - INTERVAL '$num_days' HOUR AND CURRENT_TIMESTAMP");
}
$tot_snippets = mysql_num_rows($feedback_query);

So i basically loop through each unique error, then search the snippets_link_email_id Table to see how many times that error was recorded i.e how many parsed emails had that error.

tot_snippets tells me the total amount of each. So this is great i can now build a table of each error and how many times it was recorded.

However in the emails table there is a lot of information including version number, what if i wanted to get total unique errors where the value of version_no in the emails table was = 2000

how would i edit this code to see that?

SELECT *
FROM snippets
LEFT JOIN snippets_link_email_id
    ON snippets.snippet_id = snippets_link_email_id.snippet_id
WHERE
    snippets_link_email_id.snippet_id = $i
    AND snippet_date BETWEEN
        CURRENT_TIMESTAMP - INTERVAL '$num_days' HOUR
        AND CURRENT_TIMESTAMP

I tried this from other similar posts but im guessing im miles off:

 SELECT *
 FROM snippets, emails
 LEFT JOIN snippets_link_email_id
     ON snippets.snippet_id = snippets_link_email_id.snippet_id
     AND snippets_link_email_id.email_id ON emails.email_id
 WHERE snippets_link_email_id.snippet_id = $i
 AND snippet_date BETWEEN
     CURRENT_TIMESTAMP - INTERVAL '$num_days' HOUR AND CURRENT_TIMESTAMP

Answer 1

Will joining properly to email resolve it?

 SELECT *
 FROM snippets
 LEFT JOIN snippets_link_email_id
 ON snippets.snippet_id = snippets_link_email_id.snippet_id
 LEFT JOIN emails
 ON snippets_link_email_id.email_id = emails.email_id
 WHERE snippets_link_email_id.snippet_id = $i
 AND snippet_date BETWEEN CURRENT_TIMESTAMP - INTERVAL '$num_days' HOUR AND CURRENT_TIMESTAMP");

Also, I notice your very first query doesn't use a time stamp. That would return many more rows than you really need (I guess). You could filter that first query by timestamp, and then your loops would be substantially smaller:

SELECT * FROM snippets
     WHERE snippet_date BETWEEN CURRENT_TIMESTAMP - INTERVAL '$num_days' HOUR AND CURRENT_TIMESTAMP");