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;
Step 2: Run this code in phpMyAdmin -> db_name (this will add a new column where the data is saved)
Step 3: Create a file in htdocs/ name it spy.php and paste into it this code:
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,:
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]](http://img843.imageshack.us/img843/3365/capturevql.th.png)
![[Image: capture3sp.th.png]](http://img3.imageshack.us/img3/3242/capture3sp.th.png)
Credits:
- Me (idea and code)
- Google (for the mysql trigger documentation)
Best regards, Sinner.
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:
![[Image: capturevql.th.png]](http://img843.imageshack.us/img843/3365/capturevql.png)
![[Image: capture3sp.th.png]](http://img3.imageshack.us/img3/3242/capture3sp.png)
Credits:
- Me (idea and code)
- Google (for the mysql trigger documentation)
Best regards, Sinner.
