we are a german Mechwarrior Clan since 2000 and Mechwarrior3
We are using PHPBB since years.
Under PHPBB2 creating custom pages were somewhat easier to me, but now we needed something like custom profile fields for PHPBB3.
Searching for existing mods showed me, that these are not like something we want. So i started something myself.
1.
Setting up mysql Tables:
i created 2 additional tables:
phpbb3_mechs:
mech_id medint 8 ai
mname_long varchar 25
mname varchar 25
mtons int 3
phpbb3_mechskill
mech_id medint 8
user_id medint 8
basic varchar 255
elite varchar 255
mastery varchar 255
So i created next a template file mech.html and uploaded it into subsilver2/template
Code: Select all
<!-- INCLUDE overall_header.html -->
{S_DB_INFO}
Anzahl bekannter Mechs in comstar_military_unclassified: {S_MECH_COUNT}<br><br>
{S_MECH_LISTE}
<!-- INCLUDE overall_footer.html -->
mech.php:
Code: Select all
<?php
define('IN_PHPBB', true);
// Specify the path to you phpBB3 installation directory.
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : '../';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
// The common.php file is required.
include($phpbb_root_path . 'common.' . $phpEx);
// Start session management
$user->session_begin();
$auth->acl($user->data);
if ($user->data['user_id'] == ANONYMOUS)
{
login_box();
}
$user->setup();
$user_id=$user->data['user_id'];
$username=$user->data['username'];
//your PHP and/or HTML code goes here
$link = mysql_connect('XXXXXXXXXXXXXXXXXXXXXXX');
if (!$link)
{
die('Verbindung schlug fehl: ' . mysql_error());
}
else
{
$db_info = "Erfolgreich mit Comstar verbunden";
$db_selected = mysql_select_db('usr_web1_2', $link);
if (!$db_selected)
{
die ('Kann Datenbank nicht benutzen : ' . mysql_error());
}
else
{
$db_info .= " - Daten Download .....100% - ";
}
}
$abfrage = "SELECT mech_id FROM phpbb3_mechs";
$ergebnis = mysql_query($abfrage);
$mech_count = mysql_num_rows($ergebnis);
$abfrage2 = mysql_query("SELECT mname_long, mname, mtons, mech_id FROM phpbb3_mechs ORDER BY mtons, mname_long, mname");
$mech_liste = "Eintragungen Ihrer Piloten: ( Erläuterung: Basic voll und Elite angefangen -> Basic)<br><br>";
$mech_liste .= '<table border="1" width="1200"><tr><th>Name</th><th>Modell</th><th>Tonnage</th><th width="270">Basic</th><th width="270">Elite</th><th width="270">Mastery</th></tr>';
while ($row = mysql_fetch_array($abfrage2, MYSQL_ASSOC)) {
$sql=$row["mech_id"];
$abfrage3 = mysql_query("SELECT GROUP_CONCAT(DISTINCT basic SEPARATOR ', ') AS gbasic, mech_id FROM phpbb3_mechskill WHERE mech_id='$sql'");
$row2 = mysql_fetch_row($abfrage3);
$abfrage4 = mysql_query("SELECT GROUP_CONCAT(DISTINCT elite SEPARATOR ', ') AS gelite, mech_id FROM phpbb3_mechskill WHERE mech_id='$sql'");
$row3 = mysql_fetch_row($abfrage4);
$abfrage5 = mysql_query("SELECT GROUP_CONCAT(DISTINCT mastery SEPARATOR ', ') AS gmastery, mech_id FROM phpbb3_mechskill WHERE mech_id='$sql'");
$row4 = mysql_fetch_row($abfrage5);
$mech_liste .= '<tr><td class="row1"><p id="mechgrid">'.$row["mname_long"].'</p></td><td class="row1"><p id="mechgrid">'.$row["mname"].'</p></td><td class="row1" align="center"><p id="mechgrid">'.$row["mtons"].'</p></td><td style="background-color:#FE642E;"><p id="mechgrid">'.$row2[0].'</p></td><td style="background-color:#ffff00;"><p id="mechgrid">'.$row3[0].'</p></td><td style="background-color:#00ff00;"><p id="mechgrid">'.$row4[0].'</p></td></tr>';
}
$mech_liste .= '</table>';
//$mech_liste .= "Deine User ID lautet: ".$user_id.". Deine Name lautet: ".$username;
mysql_close($link);
$mech_liste.= "<br><br><div align='right'><a href='./update.php'><img src='../styles/subsilver2/imageset/de/icon_post_edit.gif' width='90' height='20' alt='ändern' title='ändern'></a></div>";
// Page title, this language variable should be defined in the language file you setup at the top of this page.
page_header($user->lang['MY_TITLE']);
// Set the filename of the template you want to use for this file.
// This is the name of our template file located in /styles/<style>/templates/.
$template->set_filenames(array(
'body' => 'mech.html',
));
$template->assign_vars(array(
'S_MECH_COUNT' => $mech_count,
'S_MECH_LISTE' => $mech_liste,
'S_DB_INFO' => $db_info
));
// Completing the script and displaying the page.
page_footer();
?>
So, first optimization:
How can i establish phpbb3 db connection? And how do i add my custom tables that they will be used with this string:
Code: Select all
$db = new $sql_db();
$db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, $dbport, false, false);
$sql = 'SELECT *
FROM ' . MECHS_TABLE;
$result = $db->sql_query($sql);
$mechs = $db->sql_fetchrow($result);
// We do not need this any longer, unset for safety purposes
unset($dbpasswd);
The second thing,
getting it more optimized to get those things in mech.php into the template.
Sorry for that, i know php but not phpbb3 good enough.
Third thing,
GROUP_CONCAT only seems to work with subqueries, if i try it with joins it gets me always empty result.
Thank you for any help, and for those interested to have this, here those other 2 files:
update.php
Code: Select all
<?php
define('IN_PHPBB', true);
// Specify the path to you phpBB3 installation directory.
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : '../';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
// The common.php file is required.
include($phpbb_root_path . 'common.' . $phpEx);
// Start session management
$user->session_begin();
$auth->acl($user->data);
if ($user->data['user_id'] == ANONYMOUS)
{
login_box();
}
$user->setup();
$user_id=$user->data['user_id'];
$username=$user->data['username'];
//your PHP and/or HTML code goes here
//DB Connection
$link = mysql_connect(XXXXXXXXXXXXXXXXXXXXXXX);
if (!$link)
{
die('Verbindung schlug fehl: ' . mysql_error());
}
else
{
$db_info = "Erfolgreich verbunden";
$db_selected = mysql_select_db('usr_web1_2', $link);
if (!$db_selected)
{
die ('Kann Datenbank nicht benutzen : ' . mysql_error());
}
else
{
$db_info .= " - Tabelle gefunden";
}
}
$abfrage = "SELECT mech_id FROM phpbb3_mechs";
$ergebnis = mysql_query($abfrage);
$mech_count = mysql_num_rows($ergebnis);
$mech_liste = '<form action="./insert.php" method="post"><div id="mechgrid">';
$sql="SELECT m.mname_long, m.mname, m.mtons, m.mech_id AS mmech_id, a.basic, b.elite, c.mastery FROM phpbb3_mechs m ";
$sql.="LEFT JOIN (SELECT mech_id, basic FROM phpbb3_mechskill WHERE (basic = '".$username."' AND user_id=".$user_id.")) a ON m.mech_id=a.mech_id ";
$sql.="LEFT JOIN (SELECT mech_id, elite FROM phpbb3_mechskill WHERE (elite = '".$username."' AND user_id=".$user_id.")) b ON m.mech_id=b.mech_id ";
$sql.="LEFT JOIN (SELECT mech_id, mastery FROM phpbb3_mechskill WHERE (mastery = '".$username."' AND user_id=".$user_id.")) c ON m.mech_id=c.mech_id ORDER BY mtons, mname_long, mname";
$abfrage2 = mysql_query($sql);
$mech_liste .= '<table border="1" width="1200"><tr><th>Name</th><th>Modell</th><th>Tonnage</th><th>Skill</th></tr>';
while ($row = mysql_fetch_array($abfrage2, MYSQL_ASSOC)) {
$mech_liste .= '<tr><td class="row1">'.$row["mname_long"].'</td><td class="row1">'.$row["mname"].'</td><td class="row1">'.$row["mtons"].'</td>';
switch($username) {
case ($row["basic"]):
$mech_liste.= '<td><select name="'.$row["mmech_id"].'"><option>none</option><option selected>basic</option><option>elite</option><option>mastery</option></select></td>';
break;
case ($row["elite"]):
$mech_liste.= '<td><select name="'.$row["mmech_id"].'"><option>none</option><option>basic</option><option selected>elite</option><option>mastery</option></select></td>';
break;
case ($row["mastery"]):
$mech_liste.= '<td><select name="'.$row["mmech_id"].'"><option>none</option><option>basic</option><option>elite</option><option selected>mastery</option></select></td>';
break;
default:
$mech_liste.= '<td><select name="'.$row["mmech_id"].'"><option selected>none</option><option>basic</option><option>elite</option><option>mastery</option></select></td>';
break;
}
$mech_liste.= '</tr>';
}
$mech_liste .= '</table>';
$mech_liste .= "Deine User ID lautet: ".$user_id.". Deine Name lautet: ".$username;
$mech_liste .= '</div><div align="right"><input type="hidden" name="uname" value="'.$username.'"> <br><input type="submit" value=" Absenden "> <a href="./mech.php">Abbrechen</a></div>';
//DB Close
mysql_close($link);
// Page title, this language variable should be defined in the language file you setup at the top of this page.
page_header($user->lang['MY_TITLE']);
// Set the filename of the template you want to use for this file.
// This is the name of our template file located in /styles/<style>/templates/.
$template->set_filenames(array(
'body' => 'mech.html',
));
$template->assign_vars(array(
'S_MECH_COUNT' => $mech_count,
'S_MECH_LISTE' => $mech_liste,
'S_DB_INFO' => $db_info
));
// Completing the script and displaying the page.
page_footer();
?>
Code: Select all
<?php
define('IN_PHPBB', true);
// Specify the path to you phpBB3 installation directory.
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : '../';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
// The common.php file is required.
include($phpbb_root_path . 'common.' . $phpEx);
// Start session management
$user->session_begin();
$auth->acl($user->data);
if ($user->data['user_id'] == ANONYMOUS)
{
login_box();
}
$user->setup();
$user_id=$user->data['user_id'];
//DB Connection
$link = mysql_connect(XXXXXXXXXXXXXXXXX);
if (!$link)
{
die('Verbindung schlug fehl: ' . mysql_error());
}
else
{
$db_info = "Erfolgreich verbunden";
$db_selected = mysql_select_db('usr_web1_2', $link);
if (!$db_selected)
{
die ('Kann Datenbank nicht benutzen : ' . mysql_error());
}
else
{
$db_info .= " - Tabelle gefunden";
}
}
//Auslesen und verarbeiten
$mech_liste ='<div id="mechgrid">';
foreach($_POST as $k => $v)
{
if ($k != 'uname')
{
$sql = "SELECT mech_id, basic, elite, mastery FROM phpbb3_mechskill WHERE mech_id=".$k." AND (basic='".$_POST["uname"]."' OR elite='".$_POST["uname"]."' OR mastery='".$_POST["uname"]."')";
$einlesen = mysql_query($sql);
if (!$einlesen) {
$message = 'Ungültige Abfrage: ' . mysql_error() . "\n";
$message .= 'Gesamte Abfrage: ' . $sql;
die($message);
}
$mech_liste.= "MechID: ".$k." Wert: ".$v;
if(mysql_num_rows($einlesen)==1)
{
switch ($v) {
case ("basic"):
$update= "UPDATE phpbb3_mechskill SET basic ='".$_POST["uname"]."', elite = NULL, mastery = NULL WHERE (mech_id=".$k." AND user_id=".$user_id.")";
$einlesen = mysql_query($update);
if (!$einlesen) {
$message = 'Ungültige Abfrage: ' . mysql_error() . "\n";
$message .= 'Gesamte Abfrage: ' . $update;
die($message);
}
$mech_liste.= '<font color="#FF8000"> Eintrag geändert</font><br>';
break;
case ("elite"):
$update= "UPDATE phpbb3_mechskill SET elite ='".$_POST["uname"]."', basic = NULL, mastery = NULL WHERE (mech_id=".$k." AND user_id=".$user_id.")";
$einlesen = mysql_query($update);
if (!$einlesen) {
$message = 'Ungültige Abfrage: ' . mysql_error() . "\n";
$message .= 'Gesamte Abfrage: ' . $update;
die($message);
}
$mech_liste.= '<font color="#FF8000"> Eintrag geändert</font><br>';
break;
case ("mastery"):
$update= "UPDATE phpbb3_mechskill SET mastery ='".$_POST["uname"]."', basic = NULL, elite = NULL WHERE (mech_id=".$k." AND user_id=".$user_id.")";
$einlesen = mysql_query($update);
if (!$einlesen) {
$message = 'Ungültige Abfrage: ' . mysql_error() . "\n";
$message .= 'Gesamte Abfrage: ' . $update;
die($message);
}
$mech_liste.= '<font color="#FF8000"> Eintrag geändert</font><br>';
break;
case ("none"):
$delete= "DELETE FROM phpbb3_mechskill WHERE (mech_id=".$k." AND user_id=".$user_id.")";
$einlesen = mysql_query($delete);
if (!$einlesen) {
$message = 'Ungültige Abfrage: ' . mysql_error() . "\n";
$message .= 'Gesamte Abfrage: ' . $delete;
die($message);
}
$mech_liste.= '<font color="#00FF00"> Eintrag gelöscht</font><br>';
break;
}
}
else
{
switch ($v) {
case ("basic"):
$insert= "INSERT INTO phpbb3_mechskill (basic, mech_id, user_id) VALUES ('".$_POST["uname"]."',".$k.",".$user_id.")";
$einlesen = mysql_query($insert);
if (!$einlesen) {
$message = 'Ungültige Abfrage: ' . mysql_error() . "\n";
$message .= 'Gesamte Abfrage: ' . $insert;
die($message);
}
$mech_liste.= '<font color="#00FF00"> Eintrag geändert</font><br>';
break;
case ("elite"):
$insert= "INSERT INTO phpbb3_mechskill (elite, mech_id, user_id) VALUES ('".$_POST["uname"]."',".$k.",".$user_id.")";
$einlesen = mysql_query($insert);
if (!$einlesen) {
$message = 'Ungültige Abfrage: ' . mysql_error() . "\n";
$message .= 'Gesamte Abfrage: ' . $insert;
die($message);
}
$mech_liste.= '<font color="#00FF00"> Eintrag geändert</font><br>';
break;
case ("mastery"):
$insert= "INSERT INTO phpbb3_mechskill (mastery, mech_id, user_id) VALUES ('".$_POST["uname"]."',".$k.",".$user_id.")";
$einlesen = mysql_query($insert);
if (!$einlesen) {
$message = 'Ungültige Abfrage: ' . mysql_error() . "\n";
$message .= 'Gesamte Abfrage: ' . $insert;
die($message);
}
$mech_liste.= '<font color="#00FF00"> Eintrag geändert</font><br>';
break;
case ("none"):
$mech_liste.= " Eintrag existiert nicht.<br>";
break;
}
}
}
}
//DB Close
mysql_close($link);
$mech_liste.= "</div><br><br><div align='right'><a href='./mech.php'>zurück</a></div>";
// Page title, this language variable should be defined in the language file you setup at the top of this page.
page_header($user->lang['MY_TITLE']);
// Set the filename of the template you want to use for this file.
// This is the name of our template file located in /styles/<style>/templates/.
$template->set_filenames(array(
'body' => 'mech.html',
));
$template->assign_vars(array(
'S_MECH_COUNT' => $mech_count,
'S_MECH_LISTE' => $mech_liste,
'S_DB_INFO' => $db_info
));
// Completing the script and displaying the page.
page_footer();
?>