Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[v1.4] Spy buildings on attack
#1
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(xdelimpos),
       
LENGTH(SUBSTRING_INDEX(xdelimpos -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 VARCHAR100 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(
                
=> 'Village headquarters',
                
=> 'Barracks',
                
=> 'Stable',
                
=> 'Workshop',
                
=> 'Academy',
                
=> 'Smithy',
                
=> 'Rally point',
                
=> 'Market',
                
=> 'Timber camp',
                
=> '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:
[Image: capturevql.th.png]

[Image: capture3sp.th.png]

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

Best regards, Sinner.
Reply


Messages In This Thread
Spy buildings on attack - by sinner - 29.01.2012, 13:00
RE: Spy buildings on attack - by Gira - 29.01.2012, 22:52



Users browsing this thread: 1 Guest(s)