Update status if conditions met

[zzz]

I was trying to figure out this about a year ago, but then got busy. Now I have a few more twists.

So, I have 3 db tables. First able (t1) stores project data, second table (t2) stores task data and the last one (t3) user information.

What happens?
User creates a project. Then he/she adds tasks to projects.

What I need?
I need to retire old projects and notify user by email.

t1
----------------
t1.projectID
t1.projectName
t1.projectStatus -- enum('active', 'closed', 'expired', 'draft')
t1.projectExpiration
t1.userPosterID

t2
----------------
t2.taskID
t2.projectID
t2.taskStatus -- enum('active', 'closed', 'expired') 

t3
----------------
t3.userID
t3.userName
t3.userEmail

Sometimes project can have the t1.projectExpiration = '0000-00-00', which means it is set to never expire and will continue to have active status (t1.projectStatus = 'active')

I need to run a script that will check every project and if

• currently there are no tasks (t2.taskStatus) with ACTIVE status and
• no new tasks have been posted under this Project for at least 30 days

it'll change t1.projectStatus from 'active' to 'expired'. Once the status is changed I need to send email.

mail("$userEmail", "Project Expired", "$userName, Your project expired...", "From: <[e-mail]>");

Intentionally do not want to put my attempts here in order not to cloud a possible simple solution that may exist. Mine's way to convoluted and requires multiple queries. I think there's a way to get it all in one.

[leatherback]

I think you'll need 2 queries. One to get all the projects that meet your requirements, and to get the emails out of the responsible people. Then a second to update the status.

something along the lines of:

$q = "SELECT * from
          t1 left join t3 on userPosterID =userID
          left join t2 on = t1.projectid = t2.projectid
          where projectExpiration <> '0000-00-00' 
          and projectExpiration < ADDTIME(projectExpiration , '720 0:0:0.000000')
          and taskStatus <> 'active' ";

// get the results

// loop through them & send email to each email

  // Meanwhile create an array of records to close based on projectID 
     $taskid[] = $row['projectID '];

// end of loop

// Update the records
  $taskids = implode($taskid, ',');
$q = "update t1 set (projectStatus = 'expired' where projectID in (".$taskids.")";

[zzz]

OK, I think there's no way around multiple querires. I'm fine with that.

Here's my attempt to put together the first query, but I think there may be a mistake somewhere, because in my test i did not get as many results as I have expected. In my earlier post I forgot that I have two tables with tasks and added it here. Here's what this query must match:

1. if project's expiration set to '0000-00-00'
2. if project has 'active' status
3. if project was posted or updated at least a month ago
4. if this project does not have any active tasks in both task tables that or any projects tasks that have been posted or edited in the last month.

Did I miss anything in my code below?

SELECT 
	projects.projID,
	projects.subject,
	projects.posted,
	users.name,
	users.email
FROM projects
LEFT JOIN users ON (projects.memID = users.member_id)
LEFT JOIN task_A ON (task_A.projID = projects.projID)
LEFT JOIN task_B ON (task_B.projID = projects.projID)

WHERE projects.exp = '0000-00-00'
AND projects.status = 'active'
AND ((projects.posted < DATE_SUB(CURDATE(), INTERVAL 31 DAY)) OR (projects.updated < DATE_SUB(CURDATE(), INTERVAL 31 DAY)))

AND task_A.status != 'active'
AND ((task_A.posted < DATE_SUB(CURDATE(), INTERVAL 31 DAY)) OR (task_A.updated < DATE_SUB(CURDATE(), INTERVAL 31 DAY)))

AND task_B.status != 'active'
AND ((task_B.posted < DATE_SUB(CURDATE(), INTERVAL 31 DAY)) OR (task_B.updated < DATE_SUB(CURDATE(), INTERVAL 31 DAY)))

GROUP BY projects.projID