Custom MySQL Insert Script - Perl

[DW]

To be honest, this was one of my first Perl scripts that accessed a MySQL database.  I am not very experienced with optimizing these calls or making them easy to edit.

I am not sure about the referring URL problem.  I could have made a mistake in the code.  One way to test it is to output the variables:

$list = $_[0];   $em = $_[1];   $fn = $_[2];   $ln = $_[3];   $u1 = $_[4];
        $u2 = $_[5];   $u3 = $_[6];    $u4 = $_[7];   $u5 = $_[8];   $u6 = $_[9];
        $u7 = $_[10];   $u8 = $_[11];   $u9 = $_[12];   $u10 = $_[13];   $sq = $_[14];
   $de = $_[15];   $ip = $_[16];   $ref = $_[17];

After the above code, try this:

print("ref=".$ref);

[BGSWebDesign]

Hi,

Back on this topic, I'm setting up my signup-listxx calls and was wondering if it's better (more efficient) to make the calls in my .procmailrc file using wget to call signup.php within signup-listxx, or would it be more efficient to call lminsert from within signup-listxx?

It seems to me that the call to lminsert would be more efficient, since we're already being called by perl when the call is made?  Any insight into this?  I don't feel comfortable using the 'wget' call as that has to run via the command line and it may be less secure too?

[inkras]

I have done these corrections.
But server reports about a following errors:
Undefined subroutine &main::mysql_fetch_row called at /home/.../mail/insert/LMinsert.pl line 97.

Thank
Pavel

[DW]

Pavel/inkras,

Try replacing this:

Code: [Select]

  if(!$allow_dupes){
                $cmd = "select id from lm_users where list = '$li''and email like '$em'";
                $quer = $dbh->prepare($cmd);
                $quer->execute();
                $rows = $quer->rows;
      #  duplicate found..
      if($rows>0){
         list($xid,$xcnf)=mysql_fetch_row($result);
With this:

Code: [Select]

  if(!$allow_dupes){
                $cmd = "select id,cnf from lm_users where list = '$li''and email like '$em'";
                $quer = $dbh->prepare($cmd);
                $quer->execute();
                $rows = $quer->rows;
      #  duplicate found..
      if($rows>0){
         my ($xid,$xnf) = $quer->fetchrow_array();
This is untested - I don't have time tonight.  Let me know if it doesn't work and I will test it thoroughly.

Brett/webshaman,

Back on this topic, I'm setting up my signup-listxx calls and was wondering if it's better (more efficient) to make the calls in my .procmailrc file using wget to call signup.php within signup-listxx, or would it be more efficient to call lminsert from within signup-listxx?

It seems to me that the call to lminsert would be more efficient, since we're already being called by perl when the call is made? Any insight into this? I don't feel comfortable using the 'wget' call as that has to run via the command line and it may be less secure too?

You're right - it would be more efficient to call lminsert from within the signup-listX.cgi scripts.  The only reason I use the (quite inefficient) wget calls in the .cgi scripts is to make installation easier - so you don't have to enter your MySQL details in both PHP and Perl.  This approach can cause problems with bounces, mostly, because it can flood the web server. I should probably think about optimizing this soon.  Perhaps I could read the ListMail config.php via Perl by having you set a simple relative path.

Regards

[BGSWebDesign]

Hi,

Regarding this:

Copy and paste the following code into a file named LMinsert.pl

Please read the notes near the top of the file for information about usage.

Code:

Does this need any changes to run with ver 1.8x?   I don't believe it does, as I'm using it now and it seems to work, but something is a funny, I'm getting fewer records added nightly to my lists?   Previously one list averaged about 240, and now I'm getting 160, another list averaged about 60-70 before and now I'm getting 50?

Any ideas on why this might be happening, or just that the number of visitors is down?

[DW]

I have noticed a decrease in traffic lately - I think it's because everyone is/was on vacation.  Apart from the standard tests, if you have access to your site's web server logs you might be able to take a closer look and verify whether or not there are reported errors.  You can also add the "-w" option to the top of the script to possibly output extra debug info.

#!/usr/bin/perl -w

Regards

[BGSWebDesign]

Hi DW,

No, I think it's more than this, did you change the way the COUNT displays in the upper Right of Screen, or did you change the SQL for that COUNT by any chance?

Code: [Select]

Active Users 1-20 of 6409

I started going through my ENTIRE logs of entered users, but I don't want to go that far yet, I can do it for an entire day, but it seems like you may have changed how this Count appears and that NOW (vers. 1.8x) it is NOT displaying dupes that appear here and in other lists?  Or something else is wrong?

If that's not it, I don't know what to do except to do a Search on an entire day of entry - about 70 on one list, and see if they all appear, if they do, then the Count is not Updating properly from day to day?

Any other ideas - as I'm showing a good 20-30% difference (less) from what appears on the Count on my screen from what I am showing has been added - from previous day...

[DW]

Brett,

So you're saying the number of users in the User Database as compared to the number shown in the Dailymail Report is less?  I just want to make sure all bases are covered - are you sure you're not comparing the "totals" across all lists in the dailymail report with the users on a single list?

I wish I could say I have an idea about what's going on.  If you can provide more information through further testing please let me know.  One idea might be to send you an email every time someone subscribes and is inserted with the LMinsert.pl function.  (This would take a little custom perl code).  It might be worthwhile to compare, using PhpMyAdmin, a user entry made by LMinsert.pl and one made, say, by manually adding a user to make sure there are no differences.

Regards

[BGSWebDesign]

Hi DW,

users in the User Database as compared to the number shown in the Dailymail Report is less?

No, what I do is keep logs of users that are entered via LMInsert, those logs show me the email address/names of each user that is passed into LMP using the LMInsert function call (via a cronjob).

Those logs are NOT matching the COUNT that appears in the upper right, for example the difference on these two lists which I inserted last night is as follows:
List1 - 61 inserted, 96 in logs
List2 - 191 inserted, 253 in logs

So, you see, I've been going to my logs, and searching the database on the email address, I started going through the entire List1 yesterday, but posted this message first, so today, I could go through the entire 96 entries, and see which, if any do NOT appear.  I didn't find any yesterday that did not appear (in about 10 tries).

If all 96 (that are in my logs) also appear in LMP, then the COUNT (in the upper right) is not accurate for some reason??

I'll do that today, and let you know what I find...
Update
Ok, I just went through all 96 entries that were LMinserted into List1, and they are ALL in the database, so that tells me that there is a problem with the COUNT that appears at the top, or the Bounces/Removes are making up the 35 difference between the actual that were Inserted, and the total count - this MIGHT be a possibility, but I don't believe it is with List2, as that list does NOT have any followups being emailed, which means NO bouncing would take place, and I don't believe that 62 users have bounced/removed themselves from the list....  

What does that leave - I can try a DailyMail report to see....  any other suggestions?