Good Bye Magic Cards | 3l3c7r1c pl4y6r0und

Goodbye Magic Cards – Part 2: PHP Script

After preparing the data last time I need to do the script. I use PHP because I like the language and I’m quite fit in PHP programming. Additionally it can be used on Windows and Linux equally. This time I use the Netbeans IDE, even if a simple text pad would be sufficient as well. For sure such a program could be done in any other programming language (such as Phyton, C#, ..) as well.

Similar last time I must have a look on the HTML code. I search for the tags and can find them easily. Now I have to search for some code snippets that enable me to identify the single columns of the table. While doing such work I always have an empty text file open in my Notepad++ and copy interesting info right into that file. I can copy the string out of this file whenever I need it later on.

Then I make the script. Is has to run through each line of my CSV file and call the target website with the parameters. The script then parse the website and writes the parameters together with the price in a new CSV file.

I use REGEX for parsing the website. Some programmers don’t really like regex because it is so mighty that it can be seen as separate programming language. Honestly it is a bit complicated at the beginning but it is ideal for such a job and there are some helpful resources. For e.g. I strongly suggest to use https://regex101.com/ for developing and testing the expressions.

To make the code run the following extension have to be enabled in the php.ini

extension=php_openssl.dll
extension=php_curl.dll

Additionally “allow_url_fopen = On” has to be set.

<?php
ini_set('auto_detect_line_endings',TRUE); // Necessary for reading the CSV
$input = fopen('C:\temp\magic karten.csv','r'); // Input CSV with cards and Extension IDs
$output = fopen('C:\temp\Kartenpreise.txt', 'wt'); // Output CSV

while ( ($data = fgetcsv($input,1000,',') ) != FALSE ) 
{
    $ch = curl_init();
    $useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)';
    $url= utf8_encode("https://www.???.de/?mainPage=advancedSearch&cardName=".str_replace(' ','+',$data[0])."&idExpansion=".$data[3]."&manaCost=&convertedManaCostCompare=%3D&convertedManaCostValue=&powerCompare=%3D&powerValue=&toughnessCompare=%3D&toughnessValue=");
    curl_setopt($ch,CURLOPT_URL,$url);
    curl_setopt($ch,CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch,CURLOPT_USERAGENT, $useragent);
    curl_setopt($ch,CURLOPT_RETURNTRANSFER, true);
    $filestring = curl_exec($ch);
    curl_close ($ch);
    usleep(500000); // To be polite and don't stress the target server to much

    $searchpattern = '/<[^>]*>(?.*)<\/a><\/td>\s*<[^>]*>(?.*)<\/a><\/td>\s*(?\S*)<\/td>\s*(?\S*)/';
    if (!preg_match_all($searchpattern, $filestring, $treffer))
    {
        echo $data[0]."\t".$data[2]."\t not found !!!!!!!!!!\r\n";
        continue;
    }
    echo $treffer['name_deu'][0]."\t".$treffer['name_eng'][0]."\t".$data[2]."\t".$treffer['anz'][0]."\t".$treffer['preis'][0]."\r\n";
    fputs($output, $treffer['name_deu'][0]."\t".$treffer['name_eng'][0]."\t".$data[2]."\t".$treffer['anz'][0]."\t".$treffer['preis'][0]."\r\n");
}
ini_set('auto_detect_line_endings',FALSE);
fclose($input);
fclose($output);
?>

<?php

ini_set('auto_detect_line_endings',TRUE); // Necessary for reading the CSV

$input = fopen('C:\temp\magic karten.csv','r'); // Input CSV with cards and Extension IDs

$output = fopen('C:\temp\Kartenpreise.txt', 'wt'); // Output CSV

while ( ($data = fgetcsv($input,1000,',') ) != FALSE )

{

$ch = curl_init();

$useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)';

$url= utf8_encode("https://www.???.de/?mainPage=advancedSearch&cardName=".str_replace(' ','+',$data[0])."&idExpansion=".$data[3]."&manaCost=&convertedManaCostCompare=%3D&convertedManaCostValue=&powerCompare=%3D&powerValue=&toughnessCompare=%3D&toughnessValue=");

curl_setopt($ch,CURLOPT_URL,$url);

curl_setopt($ch,CURLOPT_SSL_VERIFYPEER, false);

curl_setopt($ch,CURLOPT_USERAGENT, $useragent);

curl_setopt($ch,CURLOPT_RETURNTRANSFER, true);

$filestring = curl_exec($ch);

curl_close ($ch);

usleep(500000); // To be polite and don't stress the target server to much

$searchpattern = '/<[^>]*>(?.*)<\/a><\/td>\s*<[^>]*>(?.*)<\/a><\/td>\s*(?\S*)<\/td>\s*(?\S*)/';

if (!preg_match_all($searchpattern, $filestring, $treffer))

{

echo $data[0]."\t".$data[2]."\t not found !!!!!!!!!!\r\n";

continue;

}

echo $treffer['name_deu'][0]."\t".$treffer['name_eng'][0]."\t".$data[2]."\t".$treffer['anz'][0]."\t".$treffer['preis'][0]."\r\n";

fputs($output, $treffer['name_deu'][0]."\t".$treffer['name_eng'][0]."\t".$data[2]."\t".$treffer['anz'][0]."\t".$treffer['preis'][0]."\r\n");

}

ini_set('auto_detect_line_endings',FALSE);

fclose($input);

fclose($output);

The result is a nice CSV file with all the information I need. I open it in my spreadsheet program and can easily build the sum.

Goodbye Magic Cards – Part 1: Reverse Engineering, Notepad Action and Spreadsheet Magic

I want to make a small program to get the value of my Magic Card collection. The script will automatically calls the information from a 3rd party website. Therefore I just had to sit down for approx. 2 hours and enter the card names into a spreadsheet application. As a miser I use OpenOffice.Calc for private. For my personal needs that’s more than enough even if its scope cannot compete with Microsoft Excel.

I enter all the card names and their edition in my table. The edition is necessary because it might have an impact on the card’s value.

OpenOffice Calc Tabellenblatt — OpenOffice Calc Table

Now I can start to search for a suitable website to get the price information. To keep it simple I search for a site that hands over the parameters in the site URL. Therefore I use the site’s search function so see how it is working.

In my case I just search for the first of my cards with the flowery name “Woodripper” out of “Nemesis” series. That’s the sites response

Looking a the parameters after the “/?” I understand that the card’s name is handed over in clear text: “cardName=Woodripper”. The variable names are easy to understand so I see that idExpansion must be the series. Unfortunately there is no clear text, it’s coded in an indentifier.The other variables are search options that are not useful for me at the moment. I also see that the result is displayed in a nice HTML table and that will simplify parsing later on.

My next job is to find out how the idExpansion relates to the name of the series. The series names are shown in the website, therefore this information has to somewhere be in the HTML code. I have a look on the source code of the search page. This is pretty simple and works in all browsers similar. You have to right-click somewhere on the website and select the item called like “display source code”. Unfortunately I get the result almost unformatted. That’s not unusual but at the first look I cannot see anything interested. It would be possible to use an advanced HTML editor to beautify my code but I use my beloved Notepad++ instead.

I copy the complete code into a new file and search for the string “Nemesis”. I find the search string embedded intags. Understanding the syntax of option tags I know that they will tell me what I have searched for: the ID and the clear text.

In my example the option tags look like this:

<option value=”32″>Nemesis</option>

It is <option value=”idExpansion”>clear text</option>

I change the formatting of the code using the search and replace function of Notepad++. I add a line break (\r\n) after each </option> tag.

When I scroll down the document now I can easily find the data I’m searching for. Now I just delete everything before the firstand after the lasttag. Now the document contains the payload only. I now also delete the first row because the option ALL has no use for me.

Anfang meiner Nutzdaten — Beginning of the payload

Now I have to put it into a format that my spreadsheet application can work with. I use again the search and replace function and replace <option value =” with an empty string, “>with a tabulator (\t) and with an empty string. Now I have a nice tabstop separated text file.

Tabstopp getrennter Text — tabstop separated text file

Now it’s time for a bit of Excel-Magic (I know politically correct it should be called “Openoffice.Calc-Magic or even better “Spreadsheet-Application-Magic” but that sounds to weird to me). I import the text file into a separate tab and add the series ID in my card list using VLOOKUP.

Importierte Zuordnungsliste — Imported series id lookup table

Now I can save my file in ODT format first and then I save it additionally in CSV format. Now I the date is prepared for the next step. Next time I will continue with the script.

Goodbye Magic Cards – The Script

Goodbye Magic Cards

Now it’s time to part with my Magic – The Gathering decks. After gathering dust for years I have decided to finally get rid of my former hobby. My wife recommended to just throw it away but after successfully selling my old stereo I prefer to earn some money instead. I mean, it really doesn’t make sense to throw away something if you can get some Euros for. There is only one question: what is a reasonable price?

What I will do now is writing a small script for determining the value of my cards. That’s a good life hack and a programming exercise.

Share this:

Share this:

Share this: