PHP - Need Help With Logic

Question

Hello all,

I'm currently working on a PHP/MySQL script that does the following, in this order:

1) Checks DB for any videos that need converting 2) Once determined that a video needs to be converted, it begins to convert *3) Notifies the "creator" of video that it's been created. *4) Notifies all users who are "receivers" of the video, that they have a new video.

Note, I'm looking for help on the starred numbers above.

Here's my basic db/table structure (some parts intentionally left out, because I don't want to type them:

video_data:
  id
  creator_id
  needs_process  

video_info
  id
  video_id (relational to 'id' in table above).
  receiver_id

tblusers
  usrID
  usrFirst
  usrLast
  usrEmail

Here's what I currently have in my script:

<?php
   require("connection.php");
   $rs = mysql_db_query($DBname,$sql,$link); 
   $data = mysql_query("SELECT * FROM video_data WHERE needs_process=1 LIMIT 1") or die(mysql_error());

 while($row = mysql_fetch_array( $data ))
   { 
$id = $row['id'];
$sender = $row['sender_id'];


        //Convert The Video Here (I've already written this code).


        //Maybe Create a Thumbnail Too (I've already written this code).

    }

My question is: In the code above, how would I both email the "creator" (always 1 person) and email the receivers (sometimes multiple receivers). Moreover, these would probably be "separate" or "different" emails. For example, the creator would receive an email saying "Thanks for uploading!", while the receivers would receive one saying "You've got a new video."

Any help on this would be great!

Answer 1

It's something like this:

<?php
   require("connection.php");
   $rs = mysql_db_query($DBname,$sql,$link); 
   $data = mysql_query("SELECT * FROM video_data WHERE needs_process=1 LIMIT 1") or die(mysql_error());

 while($row = mysql_fetch_array( $data ))
   { 
$id = $row['id'];
$sender = $row['sender_id'];
        //Convert The Video Here (I've already written this code).
        //Maybe Create a Thumbnail Too (I've already written this code).

        //mail the creator

        $data2 = mysql_query("SELECT * FROM tblusers WHERE usrID=$row[creator_id] LIMIT 1") or die(mysql_error());

        $creatordata = mysql_fetch_array( $data2 );

        mailtocreator($creatordata['userEmail'], $creatordata['usrFirst'].' '.$creatordata['usrLast']);
        //mail the receiver
        $data3 = mysql_query("SELECT * FROM tblusers t, video_info vi WHERE vi.video_id = $row[id] AND vi.receiver_id = t.usrID") or die(mysql_error());
        while($row2 = mysql_fetch_array($data3))
        {
       mailtocreator($row2['userEmail'], $row2['usrFirst'].' '.$row2['usrLast'], $creatordata['usrFirst'].' '.$creatordata['usrLast']);
       }

    }


function mailtocreator($toemail, $toname)
{
    //do send mail routine
    $subject = 'video processed';
    $message = 'hello '.$toname.',

Thank you for uploading video.';
    $headers = 'From: [email protected]' . "\r\n" .
      'Reply-To: [email protected]' . "\r\n" .
      'X-Mailer: PHP/' . phpversion();
    mail($toemail, $subject, $message, $headers);
}

function mailtoreceiver($toemail, $toname, $fromname)
{
    //do send mail routine
    $subject = 'new video received';
    $message = 'hello '.$toname.',

You just got new video from '.$fromname.'.';
    $headers = 'From: [email protected]' . "\r\n" .
      'Reply-To: [email protected]' . "\r\n" .
      'X-Mailer: PHP/' . phpversion();
    mail($toemail, $subject, $message, $headers);
}

Basically, it's just around this query:

$data2 = mysql_query("SELECT * FROM tblusers WHERE usrID=$row[creator_id] LIMIT 1") or die(mysql_error());

which retrieve detail info of current uploader (taken from $row[creator_id]) and

$data3 = mysql_query("SELECT * FROM tblusers t, video_info vi WHERE vi.video_id = $row[id] AND vi.receiver_id = t.usrID") or die(mysql_error());

which retrieve all receiver by querying from table tblusers and video_info then send those information to mailtocreator and mailtoreceiver respectively.