Having troubles with tables

This forum is now closed as part of retiring phpBB2.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

This forum is now closed due to phpBB2.0 being retired.
User avatar
Jadestone
Registered User
Posts: 630
Joined: Tue May 24, 2005 12:54 am
Location: Juno Beach, FL
Contact:

Having troubles with tables

Post by Jadestone »

Im working on a mod for my forum, and i can get it to work just fine but when i tested it on my beta test site it doesnt pull the tables, i get NAME.phpbb_characters doesnt exists, then i have to go thru the code and turn the table to what my forums db is (ie: phpbb_ , alpha_ )

for this test i did i used versato_ as my table, and then when i click on the link to look at the characters i get the table doesnt exists error.

now ive modded someone elses mod and im just using this just in my fourms, but he hasnt responded to how to fix these errors, if you could please take a look for me and tell me what i need to change.

Im not a real coder at all but im not a full noob when it comes to this.

between my roommate and i working on these i think its safe to say we`re retarded when it comes to mySQL.

thanks in advance^^

the files are located here:

Code: Select all

removed
also if anyone wouldnt mind after i get this fixed could someone try to explain to me how to add extra files into this mod? i need to add a subjob and base that 1/2 of the main job.
Last edited by Jadestone on Sat Jan 21, 2006 12:14 am, edited 1 time in total.
User avatar
poyntesm
Registered User
Posts: 1671
Joined: Tue Jan 18, 2005 11:19 am
Location: Dublin, Ireland
Contact:

Post by poyntesm »

You can solve this in the php side before it gets passed to the DB. Just use $table_prefix.

So phpbb_users will become $table_prefix.'users'

Hope that helps.
User avatar
Jadestone
Registered User
Posts: 630
Joined: Tue May 24, 2005 12:54 am
Location: Juno Beach, FL
Contact:

Post by Jadestone »

thanks ill try that out^^

another question then...
for the MySQL table creation..

Code: Select all

CREATE TABLE `phpbb_characters` (
  `ID` int(11) NOT NULL auto_increment,
  `user_ID` int(11) NOT NULL default '0',
  `main` tinyint(2) NOT NULL default '0',
  `name` varchar(12) NOT NULL default '',
  `rank` varchar(25) NOT NULL default '',
  `lvl` tinyint(4) NOT NULL default '0',
  `class` varchar(14) NOT NULL default '',
  `race` varchar(10) NOT NULL default '',
  `prof1` varchar(25) NOT NULL default '',
  `prof1lvl` int(11) NOT NULL default '0',
  `prof2` varchar(25) NOT NULL default '',
  `prof2lvl` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM;
do i do the same thing? or do i just leave it phpbb_characters?
User avatar
3Di
Former Team Member
Posts: 16053
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano 🇮🇹 - Frankfurt 🇩🇪
Name: Marco
Contact:

Post by 3Di »

you should use this before the query (in php)

Code: Select all

	if($table_prefix !== 'phpbb_') $sql = preg_replace('/phpbb_/', $table_prefix, $sql);
all those backtiks should have to be removed from your query if you want it to be DBAL compliant..though..
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
User avatar
Jadestone
Registered User
Posts: 630
Joined: Tue May 24, 2005 12:54 am
Location: Juno Beach, FL
Contact:

Post by Jadestone »

ok, im having a little prob still...

sry but im trying to understand this as best i can...

when i try this

Code: Select all

FROM $table_prefix.'characters'
i get an error, so i tried this

Code: Select all

FROM $table_prefixcharacters
which seems to work... im just makng sure that this is right...

or should i keep the 'characters' ?
ie:

Code: Select all

FROM $table_prefix'characters'
as for the

Code: Select all

   if($table_prefix !== 'phpbb_') $sql = preg_replace('/phpbb_/', $table_prefix, $sql);
should i put that in both of the php files before the

Code: Select all

define('IN_PHPBB', true);
$phpbb_root_path = './';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);
sry if im getting annoying i really want to learn this
User avatar
3Di
Former Team Member
Posts: 16053
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano 🇮🇹 - Frankfurt 🇩🇪
Name: Marco
Contact:

Post by 3Di »

first solution..

Code: Select all

#
#-----[ OPEN ]------------------------------------------------
#
includes/constants.php
#
#-----[ FIND ]------------------------------------------------
#
?>
#
#-----[ BEFORE, ADD ]-----------------------------------------
#
define ('CHARACTERS_TABLE', $table_prefix.'characters');
#
#-----[ SAVE/CLOSE ALL FILES ]--------------------------------
#
# EoM
then you can use
example

Code: Select all

				$sql = "SELECT *
               			FROM " . CHARACTERS_TABLE ."
               			WHERE blah = $blah";
					if(!$result = $db->sql_query($sql))
					{
						 message_die(GENERAL_ERROR, "blahbòahbòah", __LINE__, __FILE__);
        				}
___________________________________________________________

second solution

in case you want to use this
if($table_prefix !== 'phpbb_') $sql = preg_replace('/phpbb_/', $table_prefix, $sql);
then

Code: Select all

				$sql = "SELECT *
               			FROM phpb_characters
               			WHERE blah = $blah";
					if(!$result = $db->sql_query($sql))
					{
						 message_die(GENERAL_ERROR, "blahblahblah", __LINE__, __FILE__);
        				}
	if($table_prefix !== 'phpbb_') $sql = preg_replace('/phpbb_/', $table_prefix, $sql);

on the fly.. not tested..
Last edited by 3Di on Mon Nov 07, 2005 10:14 pm, edited 1 time in total.
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
User avatar
Jadestone
Registered User
Posts: 630
Joined: Tue May 24, 2005 12:54 am
Location: Juno Beach, FL
Contact:

Post by Jadestone »

sry for the double post... didnt realize i did that... im going to test that now^^ thanks^^
Last edited by Jadestone on Tue Nov 08, 2005 2:33 am, edited 2 times in total.
User avatar
3Di
Former Team Member
Posts: 16053
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano 🇮🇹 - Frankfurt 🇩🇪
Name: Marco
Contact:

Post by 3Di »

read my above post...
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
User avatar
Jadestone
Registered User
Posts: 630
Joined: Tue May 24, 2005 12:54 am
Location: Juno Beach, FL
Contact:

Post by Jadestone »

so far it seems to work fine^^ im going to try to install it on a different database and see it it still works.


thanks for your help^^
User avatar
3Di
Former Team Member
Posts: 16053
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano 🇮🇹 - Frankfurt 🇩🇪
Name: Marco
Contact:

Post by 3Di »

Jadestone wrote: so far it seems to work fine^^ im going to try to install it on a different database and see it it still works.


thanks for your help^^


glad to help :)
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
User avatar
Jadestone
Registered User
Posts: 630
Joined: Tue May 24, 2005 12:54 am
Location: Juno Beach, FL
Contact:

Post by Jadestone »

argh!

SQL Error : 1146 Table 'name.phpbb_characters' doesn't exist

its still calling the phpbb_

im wondering if itll just be easier to name my tables phpbb_

itll save me the agravation ><

i dont even know why it worked on the other site....

fyi, ive been using this option

if($table_prefix !== 'phpbb_') $sql = preg_replace('/phpbb_/', $table_prefix, $sql);
User avatar
3Di
Former Team Member
Posts: 16053
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano 🇮🇹 - Frankfurt 🇩🇪
Name: Marco
Contact:

Post by 3Di »

post here your complete query... ;)
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
User avatar
Jadestone
Registered User
Posts: 630
Joined: Tue May 24, 2005 12:54 am
Location: Juno Beach, FL
Contact:

Post by Jadestone »

this might be easier.... this is one of the files im having this problem in.
i think i am getting the tables to pull right, but then ill get an error like this

Code: Select all

Could not query users

DEBUG MODE

SQL Error : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY name ASC' at line 3

SELECT ID, user_ID, name, rank, lvl, class, race, prof1, prof1lvl, prof2, prof2lvl FROM ORDER BY name ASC

Line : 126
File : characters.php
so im wondering if i have another error? and how come it never came up till i was editing the tabel prefixes?

this is character.php this is the file you`ve been helping me attempt to fix :?

Code: Select all

<?php

define('IN_PHPBB', true);
$phpbb_root_path = './';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);

//
// Start session management
//
$userdata = session_pagestart($user_ip, PAGE_VIEWMEMBERS);
init_userprefs($userdata);
//
// End session management
//

$start = ( isset($HTTP_GET_VARS['start']) ) ? intval($HTTP_GET_VARS['start']) : 0;

if ( isset($HTTP_GET_VARS['mode']) || isset($HTTP_POST_VARS['mode']) )
{
	$mode = ( isset($HTTP_POST_VARS['mode']) ) ? htmlspecialchars($HTTP_POST_VARS['mode']) : htmlspecialchars($HTTP_GET_VARS['mode']);
}
else
{
	$mode = 'default';
}

if(isset($HTTP_POST_VARS['order']))
{
	$sort_order = ($HTTP_POST_VARS['order'] == 'ASC') ? 'ASC' : 'DESC';
}
else if(isset($HTTP_GET_VARS['order']))
{
	$sort_order = ($HTTP_GET_VARS['order'] == 'ASC') ? 'ASC' : 'DESC';
}
else
{
	$sort_order = 'ASC';
}

//
// Memberlist sorting
//
$mode_types_text = array("Character Name", "Character Rank", "Character Level", "Character Job", "Character's Main Crafting Level",  "Character's Secondary Crafting Level");
$mode_types = array('name', 'rank', 'level', 'class', 'prof1lvl', 'prof2lvl');

$select_sort_mode = '<select name="mode">';
for($i = 0; $i < count($mode_types_text); $i++)
{
	$selected = ( $mode == $mode_types[$i] ) ? ' selected="selected"' : '';
	$select_sort_mode .= '<option value="' . $mode_types[$i] . '"' . $selected . '>' . $mode_types_text[$i] . '</option>';
}
$select_sort_mode .= '</select>';

$select_sort_order = '<select name="order">';
if($sort_order == 'ASC')
{
	$select_sort_order .= '<option value="ASC" selected="selected">' . $lang['Sort_Ascending'] . '</option><option value="DESC">' . $lang['Sort_Descending'] . '</option>';
}
else
{
	$select_sort_order .= '<option value="ASC">' . $lang['Sort_Ascending'] . '</option><option value="DESC" selected="selected">' . $lang['Sort_Descending'] . '</option>';
}
$select_sort_order .= '</select>';

//
// Generate page
//
$page_title = "FFXI Character Roster";
include($phpbb_root_path . 'includes/page_header.'.$phpEx);

$template->set_filenames(array(
	'body' => 'characters_body.tpl')
);
make_jumpbox('viewforum.'.$phpEx);

$button = ($userdata['username'] != "Anonymous") ? "<form method=\"POST\" action=\"char_edit.php\"><input type=\"submit\" value=\"Manage your characters\" class=\"liteoption\"></form>" : "";

$template->assign_vars(array(
	'L_SELECT_SORT_METHOD' => $lang['Select_sort_method'],
	'L_UNAME' => "Forum Username",
	'L_CNAME' => "Character Name",
	'L_RANK' => "Rank",
	'L_LEVEL' => "Level",
	'L_CLASS' => "Job",
	'L_RACE' => "Race",
	'L_PROF' => "Synthing Levels",
	
	'BUTTON' => $button,

	'S_MODE_SELECT' => $select_sort_mode,
	'S_ORDER_SELECT' => $select_sort_order,
	'S_MODE_ACTION' => append_sid("characters.$phpEx"))
);

switch( $mode )
{
	case 'name':
		$order_by = "name $sort_order";
		break;
	case 'rank':
		$order_by = "rank $sort_order";
		break;
	case 'level':
		$order_by = "lvl $sort_order";
		break;
	case 'class':
		$order_by = "class $sort_order";
		break;
	case 'prof1lvl':
		$order_by = "prof1lvl $sort_order";
		break;
	case 'prof2lvl':
		$order_by = "prof2lvl $sort_order";
		break;
	default:
		$order_by = "name $sort_order";
		break;
}

$sql = "SELECT ID, user_ID, name, rank, lvl, class, race, prof1, prof1lvl, prof2, prof2lvl 
	FROM phpbb_characters
	ORDER BY $order_by";
if( !($result = $db->sql_query($sql)) )
{
	message_die(GENERAL_ERROR, 'Could not query users', '', __LINE__, __FILE__, $sql);
}
 if($table_prefix !== 'phpbb_') $sql = preg_replace('/phpbb_/', $table_prefix, $sql);
if ( $row = $db->sql_fetchrow($result) )
{
	$i = 0;
	do
	{
		// Start retreiving information
		$user_ID_ = $row['user_ID'];
		$roflcopter = $db->sql_fetchrow($db->sql_query("SELECT username FROM " . USERS_TABLE . " WHERE user_id = $user_ID_ LIMIT 1"));
		$username = $roflcopter['username'];
		
		$charname = $row['name'];
		$rank = $row['rank'];
		$level = $row['lvl'];
		$class = $row['class'];
		$race = $row['race'];
		$prof1 = $row['prof1'];
		$prof1lvl = $row['prof1lvl'];
		$prof2 = $row['prof2'];
		$prof2lvl = $row['prof2lvl'];
		

		$row_color = ( !($i % 2) ) ? $theme['td_color1'] : $theme['td_color2'];
		$row_class = ( !($i % 2) ) ? $theme['td_class1'] : $theme['td_class2'];

		$template->assign_block_vars('memberrow', array(
			'ROW_NUMBER' => $i + ( $start + 1 ),
			'ROW_COLOR' => '#' . $row_color,
			'ROW_CLASS' => $row_class,
			
			'USERNAME' => $username,
			'USERNAME_LINK' => "profile.php?mode=viewprofile&u=" . $user_ID_,
			'CHARACTER' => $charname,
			'RANK' => $rank,
			'LVL' => $level,
			'CLASS' => $class,
			'RACE' => $race, 
			'PROFS' => $prof1 . ' ' . $prof1lvl . ', ' . $prof2 . ' ' . $prof2lvl)
		);

		$i++;
	}
	while ( $row = $db->sql_fetchrow($result) );
	$db->sql_freeresult($result);
}

$template->pparse('body');

include($phpbb_root_path . 'includes/page_tail.'.$phpEx);

?>
User avatar
3Di
Former Team Member
Posts: 16053
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano 🇮🇹 - Frankfurt 🇩🇪
Name: Marco
Contact:

Post by 3Di »

at a first glance the WHERE clause it's missing..
like..

Code: Select all

FROM phpbb_characters
WHERE user_id <> " . ANONYMOUS . "
ORDER BY $order_by"; 
or the WHERE you want, indeed..

i saved that file and i'll try to find the time to help you asap...

are you attempting to made such a memberlist.php external to use like the forum jumpbox?
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
User avatar
Jadestone
Registered User
Posts: 630
Joined: Tue May 24, 2005 12:54 am
Location: Juno Beach, FL
Contact:

Post by Jadestone »

its a little hard to explain...
you can see some of it here, but the whole site isnt working right (its just 1 of my test sites)

http://test.thebladepact.com/characters.php

basically my members can fill out a form of their characters and then itll make up this template, then itll also put it into the viewforum.tpl, and the view profile also.

if you look at a post it puts the fields uder the user info, but for some reason the topic calendar mod messed up my viewforum so now when i look at a post i get a blank page... im not too worried tho, im redoing my main forums and starting from scratch.

if you download the .rar file i have in my first post you can see what im talking about... the only 2 files that are causing errors are the characters.php and the char_edit.php... both are having the same problems
Post Reply

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