I own several relatively small safelists: Giant Profit Ads and Mad Cow Ads.   Both of these are on the same server, so they share MYSQL engine, and cron.  When I signed up as a partner with several solo blaster networks, I found that my server seemed to be crawling.  It was taking forever to clear the SOLO Ad queues and in fact, it got totally out of control. I created the Promotion Magnet Awaiting Approval Report to help me manage my queue and give priority to Mad Cow Ads and Giant Profit Ads members in sending Solo ads.

The good folks at AFF looked into the problem, and identified that it was in fact the blaster programs that were feeding traffic in faster than the queue could send them out.  The way the Solo Send Queue works, is there is a command sendsolos.php placed in Linux scheduler called process “cron”, a system task that runs all the time in every Linux machine, and is used to execute a particular command either at a specific time or at a prescribed interval.   The items to be schedule are put into a “cron table” or “crontab” which is read by the cron process.   You can access cron for your server from CPANEL.  Only those items running in your partition of the Linux machine can be seen by you.

A temporary fix to this solo ad send queue backup was to change the crontab for sendsolos.php spedifying a shorter time interval and then monitor the server closely.   While this helped clear the logjam, it still took several days since the solo queue was well over 100.

The Fix

There were several things that we did that gave me more control.  For instance, the blaster programs (Promotion Magnet and Real Time Ad Blaster) both had options to either auto-approve, or manually approve.  The initial configuration was to have those blaster ad systems automatically approve the posts.  My thought was, they had to be approved by the admin on the system from which they originated, so then, when they were sent to my server, I would should not have to approve them again.   Again, my thought was, “why approve it twice?”

Manual Approve is Your Friend

By switching the blaster configurations to manual approval, it put control back in my hands for the ads originating from the blaster systems that were being added to the solo send queue.   It also created a heck of a lot of work for me to monitor and manually approve ads to maintain a reasonable queue depth so we don’t get emails rejected.

My New Approval Process

Promotion Magnet and Real Time Ad Blast systems are now set to manual approval.

I am getting tons of banners,  buttons, hot links, traffic links, and login ads, which takes some time to validate the image and/or text, validate the target URL, run framebreaker tests, and approve.  It is hard to distinguish which of these are coming from my own system or from the partner feeds, so I do go through with the manual approval process as stated above.

Solo ads are a different story.  When an blaster solo ad is sent from the blaster system, for tracking purposes, the target URL on the solo ad is cloaked with the root blaster system URL, which is fairly obvious to visually detect in the target URL field shown for solo ad approval.

I approve all locally generated solo ads first after running framebreaker.  Unless it is a massive rush, they get approved into the send queue right away.   In “Approve Ads” on the admin Nav menu, a number is displayed on that screen consisting of the count of approved ads in queue, waiting to send.  If that number is below 10, I then manually approve the blaster solo ads awaiting my approval until a send queue number not exceeding 10 is reached.

Good Night Gracey

I am a one-man shop, so I do have to sleep some amount of time.  Before I am getting ready to shut down my laptop for the night, I approve up to 50 blaster solo ads to fill the queue until I come back on admin duty.

This has worked out very nicely so far.  My Solo ads originating from members on my own systems are being given priority, the queues are not overloaded, and the crontab entries for sendsolos.php are back to the “normal” setting of once every hour, and there is no detrimental effects I have found.

What is missing?

I found that it would be helpful to know how many blaster solo ads are awaiting approval.  There was no report or facility in the software to accommodate this query.  Being familiar with the database structure and tables in the MySQL database, I was also comfortable enough to do some non-invasive queries using phpMyAdmin (available on CPANEL).  Once I formulated the SQL query, it was simple enough to create a php script that displays the date/time, system being queried, and count of solo ads from that blaster system awaiting approval.   The php script is run from the address bar in my  browser.  This is not fancy, and simply clears the screen to display this little report.  I call it the Promotion Magnet Awaiting Approval Report.

Multiple Systems in the Same Script

Because I have two safelists on the same server that share an MySQL database instance, it was easy to create the query code to allow multiple iterations of the report for each unique safelist.   I simply create a function in the PHP script that is called multiple times  from the mainline of the program, each call with the specific parameters required by each system database.

The Program: promotionmagnet-solos-in-queue.php

[code language=”php”]

<?php

// Copyright (c) 2015 Spaho Consulting

// Promotion Magnet Awaiting Approval Report
// Display the solo queue count for promotionmagnet
// for System1 and System2

//===========================================================
//Mainline Routine
//===========================================================

// Constants – change for each site
$ROOTUSERID = “your-root-userid”;
$ROOTPSWD = “your-root-password”;

// System1
$DBNAME = “system1db”;
$SAFELIST = “system1name “;
SoloQueueReport($DBNAME, $SAFELIST, $ROOTUSERID, $ROOTPSWD); // execute the report

// then System2
$DBNAME = “system2db”;
$SAFELIST = “system2name “;
SoloQueueReport($DBNAME, $SAFELIST, $ROOTUSERID, $ROOTPSWD); // execute the report

// end of mailine
//=============================================================

function SoloQueueReport($DBNAME, $SAFELIST, $ROOTUSERID, $ROOTPSWD)

{

// setup the log
$logfilename = “promotionmagnet-queue-count.log”;  //must exist before executing this report

// send output to log file
$handle = fopen (“$logfilename” , ‘a+’);
$date = new DateTime();
echo $SAFELIST . $date->format(‘Y-m-d H:i:s’) .”<br>”;
fwrite ($handle, $SAFELIST . ” ” . $date->format(‘Y-m-d H:i:s’) . “\n”);

// set up the database connection
$MYSQLI = new mysqli(“localhost”, “$ROOTUSERID”, “$ROOTPSWD”, “$DBNAME”,”3306″);

if ($MYSQLI->connect_errno) {
echo “Failed to connect to MySQL: ” . $MYSQLI->connect_errno . $MYSQLI->connect_error . “<br>”;
fwrite ($handle, “Failed to connect to MySQL: ” . $MYSQLI->connect_errno . $MYSQLI->connect_error . “\n”);
}
$result = null;

// see if there are any solos in queue
if ($result = $MYSQLI->query(“SELECT subject FROM solos where approved=0 and sent=0 and url like \”%promotionmagnet%\””))
{
printf(“Approval Queue = %d <br><br>”, $result->num_rows);
fwrite ($handle, “Approval Queue = ” . $result->num_rows . “\n\n”);
$result->close();
} else {
echo “No Promotion Magnet Solos in Queue <br><br>”;
fwrite ($handle, “No Promotion Magnet Solos in Queue \n\n”);
}
$MYSQLI->close();
fclose($handle);
}
?>

[/code]

Explanation of the Code

Mainline Code

This is the main program, Promotion Magnet Awaiting Approval Report, that sets up the parameters for querying the database for each system then calls the function  SoloQueueReport().

Constants

[code language=”php”]

// Constants – change for each site
$ROOTUSERID = “your-root-userid”;
$ROOTPSWD = “your-root-password”;

[/code]

Since both systems are co-located in separate subdomains of the same server, they share a common MYSQL database engine, but each have their own database that MYSQL installation.  Both would use the same Root UserID and Root Password to access those databases.

Set Up the Parameters

Each system has a separate database containing all of the tables needed to run that system.  We need to tell the database name through the variable $DBNAME, and the name of the system, which is used only in reporting but does not effect access to the database.

Once the Constants are set up, and the Unique Parameters are defined, we simply call the function, passing the variables containing those parameters to that function.

[code language=”php”]

// System1
$DBNAME = “system1db”;
$SAFELIST = “system1name “;
SoloQueueReport($DBNAME, $SAFELIST, $ROOTUSERID, $ROOTPSWD); // execute the report

[/code]

Inside the Function SoloQueueReport()

Set Up the Log File

[code language=”php”]

// setup the log
$logfilename = “promotionmagnet-queue-count.log”;

// send output to log file
$handle = fopen (“$logfilename” , ‘a+’);
$date = new DateTime();
echo $SAFELIST . $date->format(‘Y-m-d H:i:s’) .”<br>”;
fwrite ($handle, $SAFELIST . ” ” . $date->format(‘Y-m-d H:i:s’) . “\n”);

[/code]

This section defines the logfile name, then opens the file in APPEND mode

The  echo  writes the date to the screen, while the fwrite writes the date to the file.

Connect to the Database

[code language=”php”]

// set up the database connection
$MYSQLI = new mysqli(“localhost”, “$ROOTUSERID”, “$ROOTPSWD”, “$DBNAME”,”3306″);

if ($MYSQLI->connect_errno) {
echo “Failed to connect to MySQL: ” . $MYSQLI->connect_errno . $MYSQLI->connect_error . “<br>”;
fwrite ($handle, “Failed to connect to MySQL: ” . $MYSQLI->connect_errno . $MYSQLI->connect_error . “\n”);
}
$result = null;

[/code]

In order to do anything with a database, you must create a logical connection from the process to that database.  This also detects a failure and ends the procedure.

Build the SQL Query and Retrieve the Data

We are using a SELECT statement for the query.  The syntax is SELECT field FROM tablename WHERE field1 (comparative operator) value…

We are combining functions here: executing the query and performing an If Then Else test to ensure the result was returned.  If successful, then extract the number of rows returned by the query, and print the report both to the screen and the logfile.

If the query was not successful, or if the result was negative because there were no records retrieved that matched our criteria, then write an error message to both the screen and logfile.

[code language=”php”]

// see if there are any solos in queue
if ($result = $MYSQLI->query(“SELECT subject FROM solos where approved=0 and sent=0 and url like \”%promotionmagnet%\””))
{
printf(“Approval Queue = %d <br><br>”, $result->num_rows);
fwrite ($handle, “Approval Queue = ” . $result->num_rows . “\n\n”);
$result->close();
} else {
echo “No Promotion Magnet Solos in Queue <br><br>”;
fwrite ($handle, “No Promotion Magnet Solos in Queue \n\n”);
}

[/code]

The Finished Report

So that is it.  Here is what is shown on the screen.

[code language=”text”]

MadCowAds 2015-01-28 22:44:19
Approval Queue = 1

GiantProfitAds 2015-01-28 22:44:19
Approval Queue = 33

[/code]

Please feel free to create your own version of Promotion Magnet Awaiting Approval Report.  Be sure to adjust the database parameters and ensure your SQL table names are correct.

Rich Moyer AKA The Excel VBA Wizard is Principal Consultant (retired) of Spaho Consulting who brings you Life Balance Network , GiantProfitAds, Mad-Cow-Ads, Global Connections Ads, Empire Text Ads, and is a Senior Monitor for the WorldProfit Live Business Center and Platinum VIP member of WorldProfit, WebcastSource.com

1. Logon to CPANEL then access your MYSQL database using phpMyAdmin
2. Select the database
3. Select table Members
4. Open the SQL Tab
5. Copy/Paste this code into the SQL tab and click GO