Mechwarrior Mechhangar Mod - quick & dirty - how to optimize

Discussion forum for MOD Writers regarding MOD Development.
Locked
Slechtvalk
Registered User
Posts: 34
Joined: Tue Jan 15, 2008 12:56 pm

Mechwarrior Mechhangar Mod - quick & dirty - how to optimize

Post by Slechtvalk » Sat Jul 05, 2014 9:32 am

Good Morning,

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 -->
Well i tried to add a IF S_IN_GROUP_X but it shows everone that this page is not allowed, so what i miss is a code snippet (template-> assign vars?) in those 3 php files: (?)

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&auml;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='&auml;ndern' title='&auml;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();
?>
As you now can see, its a dirty mod, i lack the energy to get through optimization, i will do it, but searching via google and reading many tutorials dont give me the lacking answer...

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);
MECHS_TABLE means where do i have to add something? I would be excited even with some links where i can read about that, but google it looks like i am searching for the wrong words.

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.'">&nbsp;<br><input type="submit" value=" Absenden ">&nbsp;<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();
?>
insert.php (well for testing the output is full at the moment, will reduce it later only to changed things

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&auml;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&auml;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&auml;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&ouml;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&auml;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&auml;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&auml;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&uuml;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();
?>
ad last i added a class into stylessheet.css, i guess almost everyone can define that in their taste, and added a language file, but did not use it.

Slechtvalk
Registered User
Posts: 34
Joined: Tue Jan 15, 2008 12:56 pm

Re: Mechwarrior Mechhangar Mod - quick & dirty - how to opti

Post by Slechtvalk » Sat Jul 05, 2014 9:50 am

Well done this for template restriction

https://www.phpbb.com/kb/article/creati ... e-switches

Works.

Locked

Return to “[3.0.x] MOD Writers Discussion”