Lottery Code

[jiminma50]

Hello,

I just started using PHPList, and am amazed at what it can do.

I understand that by putting brackets around [USERTRACK] you can trace back any read emails.

Here is what I'm hoping to accomplish:

We own a small mom-pop lunch store, and send out daily menu items to our customers. Fridays are usually our slowest day, so to help us draw more people, I'd like to have [LOTTERY] placed on the Friday menu.

Using the same [USERTRACK] function, I would like to have the users ID added to the daily_lottery_db.

database:

id date message_id users_ids
1 1/1/2014 29 5,9,34,127,15
2 1/2/2014 35 14,92,5,15,127,263,2

So now I could place a small php file on the root directory to randomly draw from the list of contestants today, and print out a page to hang in the window "Bill Clinton is the daily winner". The lottery would only take place on the messages that have [LOTTERY] attached to them.

I hope i explained this correctly. I tried doing this by myself:

in sendemaillib.php added:

Code: Select all

#    $GLOBALS["tables"]["templateimage"],$cached[$messageid]["templateid"]));

  $htmlmessage = eregi_replace("\[USERID\]",$hash,$htmlmessage);
  $textmessage = eregi_replace("\[USERID\]",$hash,$textmessage);
  $htmlmessage = preg_replace("/\[USERTRACK\]/i",'<img src="'.$GLOBALS['scheme'].'://'.$website.$GLOBALS["pageroot"].'/ut.php?u='.$hash.'&m='.$messageid.'" width="1" height="1" border="0">',$htmlmessage,1);
  $htmlmessage = eregi_replace("\[USERTRACK\]",'',$htmlmessage);
 
  // [LOTTERY] code similar to [USERTRACK] when customer opens the email,they are entered into daily lottery
  $htmlmessage = preg_replace("/\[LOTTERY\]/i",'<img src="'.$GLOBALS['scheme'].'://'.$website.$GLOBALS["pageroot"].'/ut.php?u='.$hash.'&m='.$messageid.'" width="1" height="1" border="0">',$htmlmessage,1);
  $htmlmessage = eregi_replace("\[LOTTERY\]",'',$htmlmessage);
  // end of lottery edit ---------------

and in ut.php I tried using :

Code: Select all

if ($_GET["u"] && $_GET["m"]) {
  $_GET['u'] = preg_replace('/\W/','',$_GET['u']);
  $userid = Sql_Fetch_Row_Query(sprintf('select id from %s where uniqid = "%s"',
    $GLOBALS["tables"]["user"],$_GET["u"]));
  if ($userid[0]) {
    Sql_Query(sprintf('update %s set viewed = now() where messageid = %d and userid = %d',
      $GLOBALS["tables"]["usermessage"],$_GET["m"],$userid[0]));
    Sql_Query(sprintf('update %s set viewed = viewed + 1 where id = %d',
      $GLOBALS["tables"]["message"],$_GET["m"]));
    
     // -------- [LOTTERY] code
     $lottery_id = Sql_Fetch_Row_Query(sprintf('select id from %s where message_id = "%s"', $GLOBALS["tables"]["lottery"],$_GET["m"]));
     if($lottery_id[0]){
      $users_ids = Sql_Fetch_Row_Query(sprintf('select users_ids from %s where id = "%s"', $GLOBALS["tables"]["lottery"],$lottery_id[0]));
      $check = explode($users_ids);
      $unique_user_id = true;
      for($check as $key => $value){
         if($value == $userid[0]) $unique_user_id = false;
      }
      if($unique_user_id){
         // unique user, so add to the list of users
         $users_ids .= ",".$userid[0];
         Sql_Query(sprintf('update %s set users_ids = %s where id = %s',$GLOBALS["tables"]["lottery"],$users_ids,$lottery_id[0]));
      }
     }else{
      // if no lottery message id, then create a new one
      Sql_Query(sprintf('insert into %s (id,message_id,users_id) values("",%d,"%d")', $GLOBALS["tables"]["lottery"],$_GET["m"],$userid[0]));
     }
     // -------- end [LOTTERY] code
  }
}

my database is :

Code: Select all

CREATE TABLE IF NOT EXISTS `phplist_lottery` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `message_id` int(10) NOT NULL,
  `users_ids` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

Any help with this would be greatly appreciated.

[jiminma50]

Ok,so after reviewing more about PHPList, I discovered that using [USERTRACK] will write to the database when emails are read.
If you need a webpage to display all emails that were read today, you could use the following code...

Code: Select all

<!DOCTYPE html>
<html lang="en">
   <head>
      <meta charset="utf-8" />   
      <title>Who read emails today</title>   
      <link rel="stylesheet" type="text/css" href="css/style.css" >
   </head>
   <body>
      <div id="wrapper">   
      <?php               
         $database_host = "localhost";
         $database_name = "what is the name of the database we are using?";
         $database_user = "who do we log in as?";
         $database_password = 'what password do we use?';
         if(!mysql_connect($database_host,$database_user,$database_password) || !mysql_select_db($database_name) ){die(mysql_error());}
         
         $query = mysql_query("
                  SELECT  a.userid, a.viewed, a.messageid,
                        b.id, b.email
                  FROM    phplist_usermessage a
                  
                  INNER JOIN phplist_user_user b
                        ON a.userid = b.id
                  WHERE   DATE(a.viewed) = CURDATE()
                  ORDER BY a.viewed ") or die(mysql_error());
                  
         echo "<div id='emails_read_today'>";
         
         echo "<table width='100%'><tr><td>id</td><td>mess_id</td><td>f_name</td>
              <td>l_name</td><td>company</td><td>email</td><td>viewed</td></tr>";
         while( $row = mysql_fetch_assoc($query)){
            $id = $row['userid'];
            $mess_id = $row['messageid'];
            $email = $row['email'];
            $viewed = $row['viewed'];
            
            $query2 = mysql_query(" SELECT  a.attributeid, a.userid, a.value
                              FROM    phplist_user_user_attribute a
                              WHERE   a.userid = $id
                              ORDER BY a.attributeid ") or die(mysql_error());
                                $f_name = $l_name = $company = "";
            while( $row2 = mysql_fetch_assoc($query2)){
               if($row2['attributeid'] == 3) $first_name = $row2['value'];
               if($row2['attributeid'] == 4) $last_name = $row2['value'];
               if($row2['attributeid'] == 5) $company = $row2['value'];
            }
            echo "<tr>
                  <td>$id</td>
                  <td>$mess_id</td>
                  <td>$first_name</td>
                  <td>$last_name</td>
                  <td>$company</td>
                  <td>$email</td>
                  <td>$viewed</td></tr>";
         }
         echo "</table></div>";
         mysql_close();
      ?>
      </div>
   </body>
</html>

This seems much easier than trying to hack into phplist to create a [LOTTERY] feature. Hope this helps someone out.