Kevin Smith

If you are pulling information from a mysql database and want to insert either ads (like adsense) or just repeat the headers after every X amount of results, here's my way of doing it:

<?
//connect to the mysql database\
//I always like to define the value of $i as 0 just to keep things straight in my mind.  You can set this to whatever number you want to start counting from
$i=0;
 
//The query -- this is an example from one of my sites.
$result55 = mysql_query("SELECT * FROM table WHERE safe='y' AND language='english' ORDER BY timedate DESC LIMIT 10");
 
//Do the basic while statement
while($row = mysql_fetch_array($result55))
{
//Set the count of $i to plus 1
$i++;
//echo your results
echo $row[id];
//create an if statement and print out an ad on whatever number you like.  Since I limited my query to ten results, I'm going to print an ad after the 5th result (in the middle, obviously).  Note that adsense will only allow you to print three ads on any given page.
if ($i == "5"){ ?>
INSERT ADSENSE CODE HERE
<?
}
?>
 

See a working demo of this at www.SecretTweet.com -- the ad script is repeated after every 3rd ad when the query limits the results to 10. This way I can achieve the maximum number of ads allowed by google while spreading out the ads evenly within the results.

I started a sideproject called Voolia a few weeks ago so I could send multiple urls through Twitter. After getting it to a stable state (meaning it functions perfectly for what I want to do lol), I just left it alone. I checked the stats on it today to find that it has been getting some attention from random websites...pretty neat.

You might be surprised at how simple the base of the site is. It's under 50 line (counting empty lines and comments!) ...check it out:

 
<?
//define vars
$links = $_POST["links"];
$email = $_POST["email"];
$dateadded = date('Y/n/j');
 
//build callsign
// Notice that I don't user lowercase l and the number 1....too easily mistaken for eachother.
function createsign() { $chars = "abcdefghijkmnopqrstuvwxyz023456789ABCDEFGHIJKMNOPQRSTUVWXYZ";
srand((double)microtime()*1000000); $i = 0; $pass = '' ;
while ($i <= 2) { $num = rand() % 33; $tmp = substr($chars, $num, 1);
$pass = $pass . $tmp; $i++; } return $pass;}
$callsign = createsign();
 
//Check if callsign already exists...
 $q2 = mysql_query("SELECT * FROM links WHERE callsign='$callsign'");
   $q3 = mysql_fetch_object($q2);
    if($q3->callsign == $callsign) {
$callsign = createsign(); //do it again
}
 
//insert vars
$insertlinksquery = "INSERT INTO links (links, dateadded, email, hits, ip, callsign) VALUES ('$links', '$dateadded', '$email', '0', '".$_SERVER['REMOTE_ADDR']."', '$callsign')";
$runinsertlinksquery = mysql_query($insertlinksquery) or die(mysql_error());
 
//now do a foreach and insert each url within the murl as $callsign+1;
//first, we pull up what we just insertted ...i need to make this more efficient!
$result = mysql_query("SELECT * FROM links WHERE callsign='$callsign1'");
$row = mysql_fetch_array($result);
$linksrow = $row['links'];
 
$i = 0;
$chunks = spliti ("
", $links, 100);
//print_r($chunks);
foreach ($chunks as $value) {
if($value == "") { $newstring = "http://voolia.com"; $cliprow = "http://voolia.com"; } //get rid of empty links from bookmarklet and replace with voolia.com for kicks and giggles
$i++;
$individualcallsign = "$callsign$i";
$insertindividual = "INSERT INTO links (links, dateadded, email, hits, ip, callsign) VALUES ('$value', '$dateadded', '$email', '0', '".$_SERVER['REMOTE_ADDR']."', '$individualcallsign')";
$runinsertindividual = mysql_query($insertindividual) or die(mysql_error());
}
?>
 

Isn't that amazingly simple?...I think so. With just a tiny bit of .htaccess manipulation, you can have your own url redirection service....I find these handy.

While I'm most certainly no expert on the subject of mysql injection attacks, I do know that, unless you have a ton of valuable information stored in your database, the "hype" of attack prevention is just that: hype. Most scriptkiddies aren't going to waste their time pulling junk information from your database (and besides, you should have a cron job setup to do automatic backups of your databases on a regular basis.

This is all my personal opinion, of course. Any of my sites could suffer possible attack tonight...I would worry too much about it. Just simply restore the most recent backup and THEN work on prevention (I don't have anything that crucial stored in vulnerable databases)...

Despite the above, here's a very basic php function to possibly deter or prevent an sql injection attack:

 
<?
function prevent($input){
    if(is_array($input)){
        foreach($input as $k=&gt;$i){
            $output[$k]=prevent($i);
        }
    }
    else{
        if(get_magic_quotes_gpc()){
            $input=prevent($input);
        }
        $output=mysql_real_escape_string($input);
    }
 
    return $output;
}
>

Ever wonder which links are the most popular on your site? I'm always curious which links receive the most attention and who I'm giving my traffic to so I developed this "quick-n-dirty" outbound link tracking script that runs from a mysql database (if you're still running flat file scripts, time for an update...no one does that anymore and haven't for a long time...shame on you).

tl.php (for tracklink...can be named anything, obviously)

 
<?
//KevinSmithDesigns.com
//connect
mysql_connect("localhost", "un", "pw") or die(mysql_error());
mysql_select_db("") or die(mysql_error());$url = $_GET['ksdurl'];
 
$comment = $_GET['ksdcomment'];
$dateadded = date('Y/m/d'); // 4 dig year, 2 dig month, and 2 dig day -- makes for the easiest and cleanest sorting later on!
 
//Check if url already exists WITH the specific comment...
$exist = mysql_query("SELECT * FROM links WHERE url='$url' AND comment='$comment'");
$doesit = mysql_fetch_object($exist);
if($doesit->url == $url) {
mysql_query("UPDATE links SET clicks=clicks+1 WHERE url='$url' AND comment='$comment'");
header("Location: $url");
} else {
 
//insert vars
$insertlinksquery = "INSERT INTO links (url, created, clicks, comment) VALUES ('$url', '$dateadded', '1', '$comment')";
$runinsertlinksquery = mysql_query($insertlinksquery) or die(mysql_error());
 
header("Location: $url");
}
 
?>
 

Now how to activate this script so that clicks are recorded: If you link to google.com, your new url would be: http://domain.com/tl.php?ksdurl=http://google.com. If you link to google multiple times and want to track each link, add a comment: http://domain.com/tl.php?ksdurl=http://google.com&ksdcomment=from about page Like always, this can be done better and more efficient. However, this is something I've quickly thrown together to track a few links simply to satisfy my curiosity. I'd suggest adding in $_SERVER['HTTP_REFERER'] to automatically track the source AND it would also be a good idea to use htaccess to automatically log links in tl.php...I'm just not going to take the time to do that. ----- You could customize the display of the stats to your own (mine is fairly detailed and complicated compared to the above script). Here's an example:

 
<?
$sortby1 = $_GET['sort'];
if($sortby1 == ''){ $sortby = 'id'; } else { $sortby = $sortby1; }
//don't forget to connect to the db
?>
<html>
<head>
<title>Link Tracker</title>
</head>
<body>
<font size="6" face="Verdana">Link Tracker</font><br>
<font face="Verdana" size="2">Currently sorting by <? echo $sortby; ?>.&nbsp; Reorder by: Sort by: <a href="stats.php?sort=id">ID</A> | <a href="stats.php?sort=url">URL</A> |<a href="stats.php?sort=created">Created</A> |<a href="stats.php?sort=clicks">Clicks</A> |<a href="stats.php?sort=comment">Comment</A></font>
<table border="1" width="850" cellspacing="0" bordercolor="#000000" bordercolorlight="#000000" bordercolordark="#000000">
<tr>
<td width="51" align="center" valign="top" bgcolor="#C0C0C0">
<p align="center"><font face="Verdana" size="1">ID</font></td>
<td width="72" align="center" valign="top" bgcolor="#C0C0C0"><font face="Verdana" size="1">CLICKS</font></td>
<td width="99" align="center" valign="top" bgcolor="#C0C0C0"><font face="Verdana" size="1">ADDED</font></td>
<td width="385" align="center" valign="top" bgcolor="#C0C0C0"><font face="Verdana" size="1">URL</font></td>
<td width="209" align="center" valign="top" bgcolor="#C0C0C0"><font face="Verdana" size="1">COMMENT</font></td>
</tr>
 
<?
$getlinks = mysql_query("SELECT * FROM links ORDER BY $sortby");
while($getlinks1 = mysql_fetch_array($getlinks)){
$id = $getlinks1['id'];
$url = $getlinks1['url'];
$added = $getlinks1['created'];
$comment = $getlinks1['comment'];
$hits = $getlinks1['clicks'];
echo "
<tr>
<td width=51 align=center><font face=Verdana size=1>$id</font></td>
<td width=72 align=center><font face=Verdana size=1>$hits</font></td>
<td width=99 align=center><font face=Verdana size=1>$added</font></td>
<td width=385 align=left><font face=Verdana size=1><a href=\"$url\">$url</a></font></td>
<td width=209 align=left><font face=Verdana size=1>$comment</font></td>
</tr>
 
";
}
echo "</table>
 
";
?>
</body>
</html>
 

And, finally, sample database structure:

CREATE TABLE `links` ( `id` MEDIUMINT(9) NOT NULL AUTO_INCREMENT, `url` TEXT NOT NULL, `created` VARCHAR(20) NOT NULL DEFAULT '', `clicks` VARCHAR(10) NOT NULL DEFAULT '', `comment` VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 

Additions, comments, suggestions all welcome.