[v1.4] Spy buildings on attack

[sinner]

INFO: This script will help you determine the building levels of a spied village and automatically save it into another mysql column.

Attention, this IS NOT a php snippet, though, I kindly ask you to use this as it follows:

Step 1: Run this code in phpMyAdmin -> db_name
- this code will create a function which selects the number of units from an string like 1;0;0;0;20;10;100;29;

PHP Code:

CREATE FUNCTION twexplode(
    x VARCHAR(255),
    delim VARCHAR(12),
    pos INT
    )
    RETURNS VARCHAR(255)
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, ''); 


Step 2: Run this code in phpMyAdmin -> db_name (this will add a new column where the data is saved)

PHP Code:

ALTER TABLE  reports ADD  s_buildings VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL AFTER  receiver_userid 


Step 3: Create a file in htdocs/ name it spy.php and paste into it this code:

PHP Code:

<?php
$con = mysql_connect('localhost','root','pass') or die(mysql_error());
//replace pass with mysql pass
mysql_select_db('database',$con) or die(mysql_error());
//replace database with the db name

mysql_query("CREATE TRIGGER `spy` BEFORE INSERT ON `reports`
FOR EACH ROW
BEGIN

IF NEW.type = 'attack' AND (twexplode(NEW.a_units, ';',4) - twexplode(NEW.b_units, ';',4)) > 0 AND NEW.in_group = 'attack' THEN

    SET @buildings = NULL;
    SELECT
        CONCAT(
            `main`, ';',
            `barracks`, ';',
            `stable`, ';',
            `garage`, ';',
            `snob`, ';',
            `smith`, ';',
            `place`, ';',
            `market`, ';',
            `wood`, ';',
            `stone`, ';',
            `iron`, ';',
            `farm`, ';',
            `storage`, ';',
            `hide`, ';',
            `wall`)
        INTO
            @buildings
        FROM
            `villages`
        WHERE
            `id` = NEW.to_village;

    SET NEW.s_buildings = @buildings;

  END IF;
END") or die(mysql_error());
mysql_close($con);
?>

Step 4: Go to http://localhost/spy.php
- If page is blank (empty / white), or it returns something like duplicate trigger name, everything is ok, DELETE spy.php
- If there are shown any errors, please post them here !

Step 5: All thats left to do now is the modification for game_report_view_attack.tpl so it can parse and show the data from s_buildings (Scouted buildings column).
It will be something like this,:

PHP Code:

{php}
    $info = mysql_fetch_assoc(mysql_query("SELECT s_buildings FROM reports WHERE id = ".(int)$_GET['view']));
    if ($info['s_buildings']) {
            $level = explode(';',$info['s_buildings']);
            $_builds = array(
                0 => 'Village headquarters',
                1 => 'Barracks',
                2 => 'Stable',
                3 => 'Workshop',
                4 => 'Academy',
                5 => 'Smithy',
                6 => 'Rally point',
                7 => 'Market',
                8 => 'Timber camp',
                9 => 'Clay pit',
                10 => 'Iron mine',
                11 => 'Farm',
                12 => 'Warehouse',
                13 => 'Hiding place',
                14 => 'Wall'
            );
            
            echo '<table style="border: 1px solid #DED3B9;">
                <tr>
                    <th>Scouted buildings:</th>
                    <td>';
                    for ($i = 0; $i < count($_builds); $i++) //++$i wont work in php4
                        if ($level[$i])
                            echo $_builds[$i].' <b>(Level '.$level[$i].')</b><br />';
                echo '</td></tr></table><br/>';
    }
{/php} 


FAQ:
Q: Why do you need to run the SQL code from spy.php in a php file when you can run it in phpMyAdmin?
A: Because phpMyAdmin 2.x has trouble executing triggers with delimiters ! So it's best and secure to run it in PHP.

Printscreen of the final result:




Credits:
- Me (idea and code)
- Google (for the mysql trigger documentation)

Best regards, Sinner.