• OSTicket – free with most hosting accounts
• Unlimited Hosting – $39 per year
• MyWebSQL – free – upload zip to public_html, extract then setup bookmark: http://site.com/mywebsql
• Roboform – absolutely priceless
• MyLiveChat – free Live Chat for admin – GREAT.  Put the <script></script> in footer of each site

• Approve Ads – every 1-2 hours.  Be sure Solos are being sent once approved
• Check SuperNetworks to be sure ads are being sent – twice per day
• Check IPN logs – every day
• Check  Transaction Logs – every day
• Check Webmail and resolve bounces.  I ignore over quotas unless they become a problem
• Clean up Banners, Buttons, Blocks, AdsPlus, Login Ads etc. weekly (send back to member if graphics don’t display)
• Backup using CPANEL at LEAST monthly – separate for Forwarders, Database, and Home Directory – store OFFLINE on local storage like a USB drive or a CLOUD account OTHER than your site hosting.

Most admins are not comfortable with SQL, the language used to do “queries”  to get information and update information in the MYSQL database that drives most safelists and blogs on the internet.

SQL is not new and has been an established query language for many years, but there are many “flavors” – MYSQL, Oracle, Microsoft SQL Server, etc.  SQL is not particularly “user friendly”, is very particular about syntax, and has many options when entering commands.  And, accessing the database requires a “user interface” that basically gives you access to a “command line” where you enter the query commands.

The secret is to have a user interface that allows easy access to the database and tables, and allows “ad hoc” editing of the data, in addition to providing that necessary command line.

All CPANEL accounts generally provide phpMyAdmin, a free user interface to the MYSQL databases. A very respectable tool that I use extensively.  But, it requires signing on to CPANEL whenever you need to use it.

I found a free program called MyWebSQL, that once installed, allows almost the same level of access to an established MYSQL database as phpMyAdmin, but does not require sign-in to CPANEL to use it.  There are other features I like that are not found in phpMyAdmin – “sticky” SQL commands that are retained across sessions, multiple query windows, and simple export of query results.

Installation

Installation is simple.  The software is free.  Simply download the required version from MyWebSQL.com.

It does requires one-time access to CPANEL to install it, and the database userid and password to access the database.

Once you download the MyWebSQL zip file, login to CPANEL, open File Manager and navigate to /public_html.

• Upload the Zip file by clicking UPLOAD
• Refresh the File Manager display
• Find the zip file you just uploaded
• Right Click, then Extract
• Done.  You can now access MyWebSQL from your browser: http://yoursite.com/mywebsql

You will need the database userid and password.  This can be found for most safelists in /public_html/connect.php.  For other systems, I recommend creating a new database userid and password that has limited privileges (Contact me for help in setting this up).

• in CPANEL File Manager, navigate to /public_html
• Find the file connect.php
• Right click and select VIEW
• Copy down the database userid and password, and store it in a SECURE place

Just launch MyWebSQL from your browser (and bookmark it), enter the database userid and password, and you are good to go!

Rich
—
Rich Moyer
1-484-902-8819 m-f 8am-5pm EST
Email: richard.moyer.1953@gmail .com
I am a CONSULTANT FIRST and FOREMOST.
Paying It Forward…

See this blog for helpful queries to manage your safelists, and ways to package queries you run all the time so they can be run over and over right from your TAE Admin panel.

Other Related Articles:

SQL Queries for TAE Admins

SQL for TAE Admins

SQL Query for User Accounts with No Paypal

Advanced TAE Admin Topics – Cleaning up Banners

I’ve had this ongoing issue with many of the premium ad types: blocks, billboards, adsplus, featured ads, premium ads

One problem is that these ad types could not be edited, well, let’s be realistic, they could but the poorly written software didn’t support it.

I’m a purist when it comes to passing the minimum amount of information over  global variables like $_SESSION, $_POST, and $_GET.  Back in the days we were slinging code in the tarpits, I was taught to call a procedure and explicitly pass each element, and use only local variables within that procedure.  Results were to be passed back through a RETURN variable or array.

PHP is no different than any other programming language, so the same basic programming discipline and protocol should also be applied.  

What I found was the calling program (advertise.php) was passing control and ALL the data to the called program through global variables, for example, editadsplus.php.  Once the called program took control, it was ASSUMED that the data was passed correctly.  The common denominator on all the above ad types, was that they utilized a  “textarea”  for the description field, “adbody”.  Because there were only arbitrary size limits (enforced through the editor) I’m guessing that at some point we were exceeding the global variable buffer size.  

Why it was happening is not important.  The fix is easy:  only pass the ID, and re-query to obtain the fill data for the edit program  form <input> fields.

I also encountered pure sloppy programming.  In the sql “update” query, none of the data was explicitly delimited.  I’ve noticed, since the later versions of PHP were released, that some of the “lax” delimiting practices were no longer tolerated.  

My goal was not only to fix the functional issues, but also to universally improve the user experience by providing “preview” modal windows so the user could visualize the ad before submitting.  Without preview, there was a universe full of ugly ads being submitted.

OK.  So, let’s get to the fixes:

editbillboards.php

• missing $_POST for bannerurl
• Sloppy $query delimiters
• missing update for field bannerurl
• There was a problem in advertise.php also

After line 10, add this line

$bannerurl=$_POST[‘bannerurl’];

Replace the query (~ line 44) with

$query = “update billboards set subject='”.$subject.”‘, adbody='”.$adbody.”‘, url='”.$url.”‘, bannerurl='”.$bannerurl.”‘, textcolor='”.$textcolor.”‘, bgcolor='”.$bgcolor.”‘, titlecolor='”.$titlecolor.”‘, added=1, approved=0 where id=”.$id;

After <center><H2>Edit Billboard Ad</H2><br> insert this code:

<?
$query=”select * from billboards where id='”.$id.”‘”;
$result=mysql_query($query);
$line=mysql_fetch_array($result);
$subject = $line[‘subject’];
$adbody = $line[‘adbody’];
$url = $line[‘url’];
$bannerurl = $line[‘bannerurl’];
$textcolor = $line[‘textcolor’];
$bgcolor = $line[‘bgcolor’];
$titlecolor = $line[‘titlecolor’];
?>

After  <p><b>Please double check your billboard ad.</b></p> insert this code

<SCRIPT LANGUAGE=”JavaScript”>
function previewad(bannerurl,url,subject,adbody,bgcolor,textcolor,titlecolor)
{
var win
win = window.open(“”, “win”, “height=250,width=500,toolbar=no,directories=no,menubar=no,scrollbars=yes,resizable=yes,dependent=yes”);
win.document.clear();
win.document.write(‘<body bgcolor=”‘+bgcolor+'”>’);
win.document.write(‘<a href=”‘+url+'”><img src=”‘+bannerurl+'” border=”0″></a>’);
win.document.write(‘<center><br><font color=”‘+titlecolor+'”>’+subject+'</font><br><br>’);
win.document.write(‘<font color=”‘+textcolor+'”>’+adbody+'<br></center></font></body>’);
win.focus();
win.document.close();
}
</SCRIPT>
<INPUT TYPE=”button” class=”form-button” value=” Preview Button ” onClick=”previewad(bannerurl.value, url.value, subject.value, adbody.value,bgcolor.value,textcolor.value,titlecolor.value)”>

Nothing worse than browsing a site seeing ad after ad with bad graphics.  

Cleanup can be a very time consuming task using the standard admin tools available in most TAE scripts.  

This technique shows how to use a couple simple SQL commands through your CPANEL account to quickly do that cleanup.

First, hover over each bad button image and write down the ID number – if status is turned on in your Chrome browser, you will see the URL displayed at the lower left corner of the browser screen.

Login to your CPANEL and launch phpMyAdmin.  Open the database (most likely, xxxxx_site)

Click on the Buttons table.  You will be browsing the banners table.

Click INLINE.

Here is the SQL to change these ads from active and approved, and return them to the member’s account so they can re-submit them.

UPDATE `buttons` SET added =0,
STATUS =0 WHERE id IN ( 709, 536, 628, 477, 65, 310, 298, 757, 226, 476, 474, 534, 266, 76, 385, 311, 472 )

Note that the standard admin tools typically only provide the option to DELETE the ad.  My opinion: since the member either purchased those ads, or traded points or commissions to acquire those ads, I hesitate to delete them from their account.  A better option is to position them as inactive so the member can re-submit them.

 Rich Moyer

I’ve always thought there had to be a better way to implement the entry of  600×300 login ads than what we see with the limited function html editors in most of the TAE and safelist scripts.  It doesn’t matter how explicit the instructions, there are so many people who simply get it wrong  or deliberately submit anything BUT 600×300 graphics in their login ads.

I found the solution.  CKEditor.

This HTML editor is feature rich, fully customizable, and IT WORKS.  It has an INSERT IMAGE button that pops up a modal window with input lines for the IMAGE URL, Alt, and a previewer. Full control for sizing, padding, and border.

On the LINK tab, there is an input line for the TARGETURL, and a pull-down for the target options that include _blank.  (I’m going to research how to  customize the code so there is no option other than _blank.)

Here is a screenshot of the image input panel.

No more 728×90 images – give instructions to make sure the “lock” is locked, and set the width to 600

And the screenshot of the LINK tab

Be sure the pull-down is “_blank”

Here is a screenshot of the finished product.

Check out the video

https://youtu.be/AHAXQgA–uk

And here is the code to make it happen.  You will have to edit the /members/addlogin.php file.  Be sure to make a backup before making any changes.

Download the attachment HERE for the full code set.

At the top of the file, the next line right after the “<?php, insert these lines of code

?>

1. autoresponder.php – change the query to:

   select * from autoresponders order by days
   
   

2. memberadsremaining.php – almost the same code as adstatus.php
   
   See the post – customized for each system.
   
   Gives a count of all ads of each type that have been used, and the number available.  Also shows promo codes used. 
3. fixed Custom Pages
   
   The problem was not that the program was broken, but I was unaware that the custom pages were inserted into the NAV menu starting with sequence #10.  Numbering caused the problem.The fix:  number your Start button, and critical items like upgrade, at a sequence number LOWER than 10.  I recommend creating a blank entry with sequence number 10 and turn it OFF.  Leave a big gap between 10 and the next sequence number (suggestion: 50).  All of your custom pages will be inserted after 10.

    

4. rjmsendarmsgs.php Utility to dump autoresponders
   
   I never realized that the autoresponder messages were a “blob”.  I created a program to send each autoresponder output to email.  I then created a label in gmail and now have a listing I can forward or capture. 
5. update.php. HTML strip of autoresponder message ad body
   
   Small change in update.php now formats autoresponder emails 
6. edit.php – add preview to each page

    Preview code:
   
   <SCRIPT LANGUAGE="JavaScript">
     function previewad(htmlcode)
     {
     var win
     win = window.open("", "win", "height=700,width=700,toolbar=no,directories=no,menubar=no,scrollbars=yes,resizable=yes,dependent=yes'");
     win.document.clear();
     win.document.write(htmlcode);
     win.focus();
     win.document.close();
     }
     </SCRIPT>Then add this line in the <form>
     <INPUT TYPE="button" value="Preview " onClick="previewad(htmlcode.value)"> 

While most functions you need to perform are via your TAE Admin panel, there are some things you just need to do some raw database queries to get the information.  Your TAE database will not function without the database – gone are the old days of flat files.

In CPANEL for your TAE hosting site, there is a program called phpMyAdmin.  This is the “window to the world” of your site’s database.  

A “database” is a collection of tables

A “table” is made up of “rows” (or records) and “columns” (or fields)

Each field in the table has a specific name, and format

Key tables

settings

members

transactions

*_ipn

oto, offer, etc.

How to find duplicate IP addresses in “members” table

Many TAE owners are not techies like me.  I’m a little rusty since I retired 13 years ago, but I still have some skills.  I spent 24 years in data center operations and distributed systems operations,  network engineering, systems engineering, and IT management, but the difference is that I had surrounded myself with smart people who told me, “we got it boss, don’t touch the keyboard”.

The trouble is that all those smart people retired the same day I did, and scattered to the wind, so I had to learn all over about building and supporting systems.

I do like to pass on some nuggets, tips, and techniques.

My sites were recently, ACTIVELY hacked, while I was IN THE SITE trying to cut him off.  He was good.

I want to pass on some tips

Use STRONG PASSWORDS on your User account, Admin account, CPanel account, and Database.  Here are 3 really good password generators:

>>>> http://strongpasswordgenerator.com/

>>>> http://www.random.org/passwords/

>>>> http://passwordsgenerator.net/

TAE Admin Control Panel

• Just about everything is fed with a database, but you should be in your sites not only as admin, but as a user.  Any hint of a problem, get help.  
  
  
• Look at your Edit Pages.  Most admins don’t have Preview on those pages, so every day, check your INDEX pages, Login Pages, and OTOs.  Copy and paste the HTML code into an HTML editor (in html or source mode).  DO NOT use a WordPress blog – WP does some screwy things with your code.  Use this free HTML editor.  
  

>> http://html-color-codes.info/html-editor/

Colors.  Even the best cannot match colors.  Don’t guess, generate the hex codes for the colors you want with these tools.

>>>> http://html-color-codes.info/

>>>> http://www.w3schools.com/colors/colors_picker.asp

Coding Changes

• If you are making any code changes, ALWAYS make a copy of the php module before you make any changes.  I usually just copy to the same filename and tack on today’s date.  For instance:  advertise.php copy is named advertise.php20170129 (use this format so it collates properly in file manager)
  
  
• Use a Code Checker.  Copy and paste your php or html code into this PHP Code Analyzer BEFORE saving it.
  

>>>> http://phpcodechecker.com/

• Enlist the Pros.   Roger Hoover.  Reasonably priced, extremely responsive, super tech. 
  

>>>> http://hoover-usa.com/

Get to Know Your Database

• I have to admit that I am not a supertech with SQL but I can get things done.  I will tell you some basics, but this will not qualify to put on your resume’ as a DBA.  This is a whole article and video itself. You will be using Cpanel with the graphical database management tool phpMyAdmin.
  
  

Key Tables – a hacker can do the most damage without much effort by getting into these pages

• settings – CHECK THIS FIRST THING!  Your admin ID and password, pricing, Paypal and Payza ID’s are in this table.  
  
  
• members – all the information about your members, their id’s, passwords, Paypal, contacts, points, EVERYTHING ABOUT EACH MEMBER.
  
  
• pages – this is where all of your OTO, offers, index pages, advertising area, login pages, member area pages , headers, footers, TOS, Privacy page, earnings disclosure, etc. are stored
  
  
• navigation – controls what your members see on the NAV menu, how it looks, and what happens when they click a button.
  
  
• transactions (there may be more than one).  Commissions may be based on this data
  
  
• paypal_ipn, alertpay_ipn – anything with *_ipn these are transactions to each payment processor – could affect commissions.
  
  
• promo_codes and promo_used – not only the promo codes you offer, but who has redeemed them – could mean lots of lost revenue if they are able to redeem codes more than once.
  
  

Doing Backups – use CPANEL Backup (not the wizard) – same cpanel folder as File Manager

• NEVER do a whole site backup. Always do separate backups for Home directory, each database, and any forwarders
  
  
• You’re not done!  Those key tables above, I want you to access each using phpMyAdmin and Export to SQL.  Move these files to secure storage – accessible. OneDrive, GoogleDrive, or DropBox will allow you to share links to those SQL files with Roger or other tech.