First of all, this question is in regards to PHP and MySQLI have two database tables:The \[code\]People\[/code\] table:\[code\]person_id | field_1 | field_2 | other_fields... \[/code\]And the \[code\]Notes\[/code\] table:\[code\]note_id | person_id | created_timestamp | other_fields... \[/code\]The \[code\]People\[/code\] table has a one to many relationship with the \[code\]Notes\[/code\] table...
Everytime a note is created in the \[code\]Notes\[/code\] table, a timestamp is attached to it, also a \[code\]person_id\[/code\] foreign key is assigned.Now...
I need to find all \[code\]people\[/code\] who haven't had a \[code\]note\[/code\] against them in the last 30 days.
The way I do it now is: [*]Get all notes from the \[code\]Notes\[/code\] table with a distinct \[code\]person_id\[/code\] and a \[code\]created_timestamp\[/code\] > \[code\]'time(31*86400)'\[/code\] (not precise.. I Know, but suits my needs)[*]Loop through the results and add the \[code\]person_id\[/code\] to a temporary array \[code\]$temp\[/code\][*]Get all records from the \[code\]People\[/code\] table[*]Loop through each record and do an \[code\]in_array()\[/code\] comparison of the \[code\]person_id\[/code\] with \[code\]$temp\[/code\]This isn't very efficient and cripples the application when there are a lot of \[code\]People\[/code\] or \[code\]Notes\[/code\].Has anyone got a better solution to this. Ideally something that can be achieved using just one SQL query.Thanks for looking
Everytime a note is created in the \[code\]Notes\[/code\] table, a timestamp is attached to it, also a \[code\]person_id\[/code\] foreign key is assigned.Now...
I need to find all \[code\]people\[/code\] who haven't had a \[code\]note\[/code\] against them in the last 30 days.
The way I do it now is: [*]Get all notes from the \[code\]Notes\[/code\] table with a distinct \[code\]person_id\[/code\] and a \[code\]created_timestamp\[/code\] > \[code\]'time(31*86400)'\[/code\] (not precise.. I Know, but suits my needs)[*]Loop through the results and add the \[code\]person_id\[/code\] to a temporary array \[code\]$temp\[/code\][*]Get all records from the \[code\]People\[/code\] table[*]Loop through each record and do an \[code\]in_array()\[/code\] comparison of the \[code\]person_id\[/code\] with \[code\]$temp\[/code\]This isn't very efficient and cripples the application when there are a lot of \[code\]People\[/code\] or \[code\]Notes\[/code\].Has anyone got a better solution to this. Ideally something that can be achieved using just one SQL query.Thanks for looking