In previous parts, I covered DAC Connections, then Part 1: What You Need To Build A Drop List, Part 2: Building A Drop List, Part 3: Maintaining A Drop List and now Part 4: Accessing Your Drop Lists.
There really is no point to having a populated zone file, if you are unable to access it for any reason or produce your own drop lists. This article will cover those who have populated the zone file, but also for those who make lots of searches to see what exists. This latter point, is more about research and mark/rights protection, as well as finding potential buyers, and part of this article will suit your needs too.
Downloading Todays List
This is the basic, downloading todays drop list, you can of course modify this to download any day you choose.
$today = date("Y-m-d");
$query = "SELECT domain FROM `zonefile` WHERE `dropdate` = '$today';";
$result = mysql_query($query);
$fp = fopen('php://output', 'w');
if ($fp && $result) {
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="'.$today.'.txt"');
header('Pragma: no-cache');
header('Expires: 0');
while ($row = mysql_fetch_row($result)) {
fputcsv($fp, array_values($row));
}
}
The above will simply download todays file, I would suggest including a switch to change the date, but its up to you, how you do it.
Searching The Database
Suppose you want to search the database and browse online, I have gone with absolute basics which is Domain and Drop Date only. A Very simple form is all that is needed.
<form id="search" method="post" action="./droplist.php">
<p>
<label for="drop" id="datelab">Search by Drop Date:</label>
<input id="drop" name="drop" onClick="this.form.reset()"> (note todays renewal required is: <?php echo date('Y-m-d', strtotime('+ 92 days')); ?>, Suspended Date is <?php echo date('Y-m-d', strtotime('+ 60 days')); ?>)</p>
<p><label for="domain" id="domainlab">Search by Domain:</label>
<input id="domain" name="domain" onClick="this.form.reset()"></p>
<p>
<input type="submit" name="submit" value=" SUBMIT " /> <input type="reset" value=" RESET ">
</p>
</form>
I have added some embellishments such as showing some useful dates to use for reference but these aren’t needed. I have also set it to clear the boxes when clicked to avoid crossed data on submit.
Displaying Drop Lists and Data
Once you have submitted the search query using a form, or converted it to a GET[] rather than POST[] you need to display the results.
Firstly, you need to connect to the myql database…
$con = mysql_connect ('localhost', 'zone_zone', '9@55\/\/012D' or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ('zone_nzf) or die ('I cannot select the database because: ' . mysql_error());
I keep this in a function, with success or fail returned, but you an just bung it at the top of your file, its down to you.
You will need to know if the form have been submitted and extract the Drop Date or Domain string.
if($_POST['submit']){
if($_POST['drop']){
$date = $_POST['drop'];
//
// drop date search code here.
//
} elseif($_POST['domain']) {
$doms = $_POST['domain'];
//
// domain search code here
//
} // close if drop
} //close submit
You don’t need to do this, but I always do it with any database query. Sometimes it only shows in debug mode, sometimes in the main view but I always have it in there. You’ll need to alter the Query a little for Drop Dates and Domain searches, but I’ll go with Domain Search code here.
$query=mysql_query("SELECT COUNT(*) AS `rows` FROM `zonefile` WHERE `domain` LIKE '%$domain%';");
$rows=mysql_num_rows($query);
echo "<p>This date ($date) has " . number_format($rows) . " records returns.</p>";
This is useful if you want to know how many records are returned on the given date or within the search query. You may deem it not worth while but thee codes there anyway. You should build this action in to the below code, rather than run it twice, but since you may not care how many lines I’ve omitted it below. I have also switched to dropdate search since I posted a search query above.
<table cellpadding="0" cellspacing="0" border="0">
<thead>
<th width="300">Domain</th>
<th width="150">Drop Date</th>
</thead>
<?php
$query = mysql_query("select * from zonefile where dropdate='$date' order by domain ASC;");
if(mysql_num_rows($query)>0){
while($data=mysql_fetch_array($query,1)){
?>
<tr>
<td><strong><?php echo $data['domain'];?></strong> (<a href="http://webwhois.nic.uk/cgi-bin/webwhois.cgi?wvw7yesk=3hryr4hby3&wquery=<?php echo $data['domain'];?>">whois</a>)</td>
<td><?php echo $data['dropdate'];?></td>
</tr>
<?php } // while sql fetch
} //if rows ?>
</table>
You probably should add a switch to adjust the background of each line to make it less headache inducing to read. I’m sure there are a dozen different ways to do this, but I went quick and easy to read code.
if($lalt==1){
$row ='#888888';
$fon='#FFFFFF';
$lalt="1";
}else {
$row ='#EEEEEE';
$fon='#000000';
$lalt="0";
}
Insert the above “if…then” statement into the “do…while” code block. Putting it there means on each iteration lalt (line alt) will change from 0 to 1 and the colour panels will change each row. Something like this would work…
<tr bgcolor="<?php echo $row; ?>"> <td><strong><font face="Courier New, Courier, monospace" color="<?php echo $fon; ?>"><?php echo $data['domain'];?></font></strong> (<a href="http://webwhois.nic.uk/cgi-bin/webwhois.cgi?wvw7yesk=3hryr4hby3&wquery=<?php echo $data['domain'];?>">whois</a>)</td> <td><?php echo $data['dropdate'];?></td> </tr>
So thats a simple displaying a given date. How you get the date to the above page is down to you. You can either use a GET[] or a POST[] function to get the date and pass it to the code. I would use a form to feed a POST[] array and then act, but a GET[] can be useful to quickly change date…
$date = $_GET[dd];
Then call the page with “dropdate.php?dd=2016-11-24” or similar.
Other Applications
These are just some ideas I toyed with, Domain Watchlists, you could set up a Crontab for certain keyterms, and when the application detects the keyword in any given days list, it sends an email with the domain list.
The download script I posted at the top of this could be very easily modified for this job.
If you have your own tag, you could create a watchlist to watch your tag and track renewals and modifications or any other tag if you stored tag data.
How much data you store, is subject to Nominets allowance and your own personal choice.
I have a few more things to write about this, but this concludes the basics. I’ll do a summery post and perhaps put the code together in to a workable solution but 95% of all the code you need in in these files and just needs sticking together.
You should now have a 2-3gb database, populated with around 11m records, and able to produce your own drop lists.
Important Notice
Before using this or any of the code I have posted you should sanitise it and enhance security, this code is NOT meant for public use. In order to keep the code simply, it has been aimed at private access so security hasn’t been a huge concern.
DO NOT DEPLOY THIS CODE.
A Manchester Based Photographer and Website Developer with interests in Strongman, Fitness and Geekery.