Re: How to call the right db table? [Q and A] - XOOPS Web Application System

1

How to call the right db table?

2005/1/24 23:15
jstacy
Just popping in
Posts: 7
Since: 2002/12/16

Hey all I am working on a project and I am porting a guild interface into XOOPS but I have a small delema. Since XOOPS uses prefexes in it's database like xoops_table is there a way to call this in a query line. Here is a small snippit of code that I would like to convert.

$querystr = "INSERT INTO xoops_char SET " . $this->assignstr;

Now xoops_char is the table. but this will not always hold true. it is true in my case because XOOPS is the prefex that I used when I installed xoops. but others will probibly use something different. This being the case is there a way to call this call so that it would be dynamic based on the users prefix. the char part of the table name will always be the same its the XOOPS part that will change. Also do I have to include any other php files to make sure I can make the correct call

Thanks

oh and If i'm being confusing please let me know and I will try to explain better.

2

Re: How to call the right db table?

2005/1/24 23:23
ackbarr
Posts: 1449
Since: 2002/10/2

the $xoopsDB object (created as a global variable in /include/common.php) as a method called prefix, which will take a table name, and return the table name with the site's db prefix prepended.

 $char_table = $xoopsDB->prefix('char'); 
$querystr = "INSERT INTO $char_table SET " . $this->assignstr;

If your snippet is inside a function, don't forget that you will need to globalize the $xoopsDB variable before you can use it:

 [color=008000][b]global $xoopsDB;[/b][/color] 
$char_table = $xoopsDB->prefix('char'); 
$querystr = "INSERT INTO $char_table SET " . $this->assignstr;

3

Re: How to call the right db table?

2005/1/24 23:32
jstacy
Just popping in
Posts: 7
Since: 2002/12/16

will I need to include the common.php in each file that I need to make the call in?

Thanks

4

Re: How to call the right db table?

2005/1/25 0:18
Dave_L
XOOPS is my life!
Posts: 2277
Since: 2003/11/7

Is this for a script that doesn't use the XOOPS core classes? In that case, you could do the following:

 $xoopsOption['nocommon'] = true; 
require_once '/path/to/xoops/mainfile.php'; 
$char_table = XOOPS_DB_PREFIX . '_' . 'char'; 
$querystr = "INSERT INTO $char_table SET " . $this->assignstr;

5

Re: How to call the right db table?

2005/1/25 1:25
jstacy
Just popping in
Posts: 7
Since: 2002/12/16

ok I think I have it but it still is giving me an undefined var error message. Here is the code please take a look at it at tell me where I am going wrong

 $xoopsOption['nocommon'] = true; 
require_once '../../mainfile.php'; 
 
global $xoopsDB; 
global $char_table; 
global $item_table; 
global $skill_table; 
global $talents_table; 
global $talenttree_table; 
$char_table = XOOPS_DB_PREFIX . '_' . 'char'; 
$item_table = XOOPS_DB_PREFIX . '_' . 'item'; 
$skill_table = XOOPS_DB_PREFIX . '_' . 'skill'; 
$talents_table = XOOPS_DB_PREFIX . '_' . 'talents'; 
$talenttree_table = XOOPS_DB_PREFIX . '_' . 'talenttree'; 
 
class wowdb { 
  var $db; 
  var $assignstr; 
 
  function connect( $host, $user, $password, $name ) { 
    $db = mysql_connect($host, $user, $password) or die( "Could not connect to db" ); 
    mysql_select_db( $name ) or die( "could not select db" ); 
  } 
 
  function query( $querystr ) { 
    $result = mysql_query($querystr) or die(mysql_error()); 
    return $result; 
  } 
 
  function getrow( $result ) { 
    return mysql_fetch_assoc( $result ); 
  } 
 
  function escape( $string ) { 
    if( version_compare( phpversion(), "4.3.0", ">" ) ) { 
      return mysql_real_escape_string( $string ); 
    } else { 
      return mysql_escape_string( $string ); 
    } 
  } 
   
 
## 
# Updatng code 
## 
  function reset_values() { 
    $this->assignstr = ""; 
  } 
 
  function add_value( $row_name, $row_data ) { 
    if( $this->assignstr != "" ) { 
      $this->assignstr .= ","; 
    } 
    if( ! is_numeric($row_data) ) { 
      $row_data = "'" . mysql_real_escape_string( $row_data ) . "'"; 
    } 
    $this->assignstr .= " `$row_name` = $row_data"; 
  } 
 
  function insert_item( $item ) { 
    $this->reset_values(); 
    $this->add_value('name', $item['name'] ); 
    $this->add_value('parent', $item['parent'] ); 
    $this->add_value('owner', $item['owner'] ); 
    $this->add_value('server', $item['server'] ); 
    $this->add_value('slot', $item['slot'] ); 
    $this->add_value('color', $item['color'] ); 
    $this->add_value('item', $item['item'] ); 
    $this->add_value('texture', $item['texture'] ); 
    $this->add_value('tooltip', $item['tooltip'] ); 
    if( isset( $item['quantity'] ) ) { 
      $this->add_value('quantity', $item['quantity'] ); 
    } 
 
    $querystr = "INSERT INTO $item_table SET " . $this->assignstr; 
 
    mysql_query($querystr) or die(mysql_error()); 
  } 
 
  function tooltip( $tipdata ) { 
    $tooltip = ""; 
    if( !is_array( $tipdata ) ) { 
      $tipdata = explode( "
", $tipdata ); 
    } 
    foreach( $tipdata as $tip ) { 
      $tooltip .= $tip . "n"; 
    } 
    return $tooltip; 
  } 
 
  function make_item( $item_data, $owner, $server, $parent, $slot_name ) { 
   $item = array(); 
   $item['name'] = $item_data['Name']; 
   $item['parent'] = $parent; 
   $item['owner'] = $owner; 
   $item['server'] = $server; 
   $item['slot'] = $slot_name; 
   $item['color'] = $item_data['Color']; 
   $item['item'] = $item_data['Item']; 
   $item['texture'] = $item_data['Texture']; 
   if( isset( $item_data['Quantity'] ) ) { 
     $item['quantity'] = $item_data['Quantity']; 
   } 
   $item['tooltip'] = $this->tooltip( $item_data['Tooltip'] ); 
   return $item; 
 
  } 
 
  function do_items( $data ) { 
     
    #first delete the stale data 
    $name = $this->escape( $data['CharName'] ); 
    $server = $this->escape( $data['Server'] ); 
    $querystr = sprintf("DELETE FROM $item_table WHERE owner = '%s' " . 
            "and server ='%s'", 
            $name, $server); 
    mysql_query($querystr) or die(mysql_error()); 
 
     
    #then process equiped items 
    $equip = $data['Equipment']; 
    foreach( array_keys($equip) as $slot_name ) { 
      $slot = $equip[$slot_name]; 
       
      $item = $this->make_item( $slot, $data['CharName'], $data['Server'], 
                'equip', $slot_name ); 
 
      $this->insert_item( $item ); 
    } 
          
    # now process inventory 
    $inv = $data['Inventory']; 
    foreach( array_keys( $inv ) as $bag_name ) { 
      $bag = $inv[$bag_name]; 
       
      $item = $this->make_item( $bag, $data['CharName'], $data['Server'], 
                'bags', $bag_name ); 
      #quantity for a bag means number of slots it has 
      $item['quantity'] = $bag['Slots']; 
       
      $this->insert_item( $item ); 
 
      foreach( array_keys( $bag['Contents'] ) as $slot_name ) { 
    $slot = $bag['Contents'][$slot_name]; 
    $item = $this->make_item( $slot, $data['CharName'], $data['Server'], 
                  $bag_name, $slot_name ); 
    $this->insert_item( $item ); 
      } 
    } 
  } 
 
  function do_skills( $data ) { 
    #first delete the stale data 
    $name = $this->escape( $data['CharName'] ); 
    $server = $this->escape( $data['Server'] ); 
    $querystr = sprintf("DELETE FROM $skill_table WHERE `char` = '%s' " . 
            "and server ='%s'", 
            $name, $server); 
    mysql_query($querystr) or die(mysql_error()); 
 
    foreach( array_keys( $data['Skills'] ) as $skill_type ) { 
      $sub_skill = $data['Skills'][$skill_type]; 
      $order = $sub_skill['Order']; 
      foreach( array_keys( $sub_skill ) as $skill_name ) { 
    if( $skill_name != 'Order' ) { 
      $this->reset_values(); 
      $this->add_value('char', $data['CharName'] ); 
      $this->add_value('server', $data['Server'] ); 
      $this->add_value('type', $skill_type ); 
      $this->add_value('name', $skill_name ); 
      $this->add_value('order', $order ); 
      $this->add_value('level', $sub_skill[$skill_name] ); 
      $querystr = "INSERT INTO $skill_table SET " . $this->assignstr; 
      print " n"; 
      mysql_query($querystr) or die(mysql_error()); 
       
    } 
      } 
    } 
  } 
 
  function do_talents( $data ) { 
    #first delete the stale data 
    $name = $this->escape( $data['CharName'] ); 
    $server = $this->escape( $data['Server'] ); 
    $querystr = sprintf("DELETE FROM $talents_table WHERE `char` = '%s' " . 
            "and server ='%s'", 
            $name, $server); 
    mysql_query($querystr) or die(mysql_error()); 
 
    $querystr = sprintf("DELETE FROM $talenttree_table WHERE `char` = '%s' " . 
            "and server ='%s'", 
            $name, $server); 
    mysql_query($querystr) or die(mysql_error()); 
 
    if($data['Talents']) { 
    foreach( array_keys( $data['Talents'] ) as $talent_tree ) { 
      $data_talent_tree = $data['Talents'][$talent_tree]; 
      foreach( array_keys( $data_talent_tree ) as $talent_skill ) { 
        $data_talent_skill = $data_talent_tree[$talent_skill]; 
        if( $talent_skill == 'Order' ) { 
        $tree_order = $data_talent_skill; 
        } elseif ( $talent_skill == 'PointsSpent' ) { 
        $tree_pointsspent = $data_talent_skill; 
        } elseif ( $talent_skill == 'Background') { 
        $tree_background = $data_talent_skill; 
        } else { 
          $this->reset_values(); 
          $this->add_value('char', $data['CharName'] ); 
          $this->add_value('server', $data['Server'] ); 
          $this->add_value('name', $talent_skill ); 
          $this->add_value('tree', $talent_tree ); 
          $this->add_value('tooltip', $data_talent_skill['Tooltip'] ); 
          $this->add_value('texture', $data_talent_skill['Texture'] ); 
          $this->add_value('row', substr($data_talent_skill['Location'], 0, 1) ); 
          $this->add_value('column', substr($data_talent_skill['Location'], 2, 1) ); 
          $this->add_value('rank', substr($data_talent_skill['Rank'], 0, 1) ); 
          $this->add_value('maxrank', substr($data_talent_skill['Rank'], 2, 1) ); 
          $querystr = "INSERT INTO $talents_table SET " . $this->assignstr; 
          print " n"; 
          mysql_query($querystr) or die(mysql_error()); 
        }  
      } 
     
      $this->reset_values(); 
      $this->add_value('char', $data['CharName'] ); 
      $this->add_value('server', $data['Server'] ); 
      $this->add_value('tree', $talent_tree ); 
      $this->add_value('background', $tree_background ); 
      $this->add_value('pointsspent', $tree_pointsspent ); 
      $this->add_value('order', $tree_order ); 
      $querystr = "INSERT INTO $talenttree_table SET " . $this->assignstr; 
      print " n"; 
      mysql_query($querystr) or die(mysql_error()); 
    } 
    }     
  } 
 
  function update_char( $name, $data ) { 
    # expose this for later functions 
    $data['CharName'] = $name; 
    #print "

"; 
    #print_r( $data ); 
    $name_escape = mysql_real_escape_string( $name ); 
    $server_escape = mysql_real_escape_string( $data['Server'] ); 
 
    $querystr = "SELECT name, server FROM $char_table WHERE ". 
      "name = '$name_escape' and server = '$server_escape'"; 
 
    print ""; 
    $result = mysql_query($querystr) or die(mysql_error()); 
 
    $update = mysql_num_rows( $result ) == 1; 
     
 
    $this->reset_values(); 
 
    if( isset($data['ProfilerVersion']) ) { 
      list( $major, $minor )= explode(".", $data['ProfilerVersion']); 
      $this->add_value( 'version', $data['ProfilerVersion'] ); 
    } else { 
      $major = 0; 
      $minor = 0; 
    } 
 
 
 
    $this->add_value( 'name', $name ); 
 
    $this->add_value( 'stat_int', $data['Stats']['Intellect'] ); 
    $this->add_value( 'stat_agl', $data['Stats']['Agility'] ); 
    $this->add_value( 'stat_sta', $data['Stats']['Stamina'] ); 
    $this->add_value( 'stat_str', $data['Stats']['Strength'] ); 
    $this->add_value( 'stat_spr', $data['Stats']['Spirit'] ); 
     
    if( isset($data['Guild']) && isset($data['Guild']['GuildName']) ) { 
      $this->add_value( 'guild_name', $data['Guild']['GuildName'] ); 
      $this->add_value( 'guild_title', $data['Guild']['Title'] ); 
      $this->add_value( 'guild_rank', $data['Guild']['Rank'] ); 
    } 
 
    $this->add_value( 'race', $data['Race'] ); 
 
    $this->add_value( 'res_frost', $data['Resists']['Frost'] ); 
    $this->add_value( 'res_arcane', $data['Resists']['Arcane'] ); 
    $this->add_value( 'res_fire', $data['Resists']['Fire'] ); 
    $this->add_value( 'res_shadow', $data['Resists']['Shadow'] ); 
    $this->add_value( 'res_nature', $data['Resists']['Nature'] ); 
 
    $this->add_value( 'armor', $data['Armor'] ); 
    $this->add_value( 'level', $data['Level'] ); 
    $this->add_value( 'server', $data['Server'] ); 
    $this->add_value( 'defense', $data['Defense'] ); 
    $this->add_value( 'talent_points', $data['TalentPoints'] ); 
 
    $this->add_value( 'money_c', $data['Money']['Copper'] ); 
    $this->add_value( 'money_s', $data['Money']['Silver'] ); 
    $this->add_value( 'money_g', $data['Money']['Gold'] ); 
 
    $this->add_value( 'exp', $data['Experience'] ); 
    $this->add_value( 'class', $data['Class'] ); 
    $this->add_value( 'health', $data['Health'] ); 
 
    if( $major == 0 && $minor >= 94 ) { 
      if( isset( $data["Melee Attack"] ) ) { 
    $attack = $data["Melee Attack"]; 
    $this->add_value( 'melee_power', $attack['AttackPower'] );  
    $this->add_value( 'melee_rating', $attack['AttackRating'] );  
    $this->add_value( 'melee_range', $attack['DamageRange'] );  
    $this->add_value( 'melee_range_tooltip', 
              $this->tooltip( $attack['DamageRangeTooltip'] ) );  
    $this->add_value( 'melee_power_tooltip', 
              $this->tooltip( $attack['AttackPowerTooltip'] ) );  
      } 
      if( isset( $data["Ranged Attack"] ) ) { 
    $attack = $data["Ranged Attack"]; 
    $this->add_value( 'ranged_power', $attack['AttackPower'] );  
    $this->add_value( 'ranged_rating', $attack['AttackRating'] );  
    $this->add_value( 'ranged_range', $attack['DamageRange'] );  
    $this->add_value( 'ranged_range_tooltip', 
              $this->tooltip( $attack['DamageRangeTooltip'] ) );  
    $this->add_value( 'ranged_power_tooltip', 
              $this->tooltip( $attack['AttackPowerTooltip'] ) );  
      } 
       
    } 
 
    if( $update ) { 
      $querystr = "UPDATE $char_table SET " . $this->assignstr . 
    " WHERE name = '$name_escape' and server = '$server_escape'"; 
    } else { 
      $querystr = "INSERT INTO $char_table SET " . $this->assignstr; 
    } 
    print ""; 
 
    mysql_query($querystr) or die(mysql_error()); 
 
    if( $major == 0 && $minor >= 91 ) { 
      $this->do_items( $data ); 
    } 
    if( $major == 0 && $minor >= 94 ) { 
      $this->do_skills( $data ); 
      $this->do_talents( $data ); 
    } 
 
     
  } 
 
   
} 
 
$wowdb = new wowdb; 
 
?>

Thanks!

6

Re: How to call the right db table?

2005/1/25 1:42
Dave_L
XOOPS is my life!
Posts: 2277
Since: 2003/11/7

What's the exact error message?

By the way, it's a good idea to put error_reporting(E_ALL) at the start of a script for testing/debugging purposes.

7

Re: How to call the right db table?

2005/1/25 2:04
jstacy
Just popping in
Posts: 7
Since: 2002/12/16

actualy I figured that out. The aswere was that I needed to call this

$char_table = XOOPS_DB_PREFIX . '_' . 'char';

before I ran the query for each function.

but now when I run the script I get this error

Notice: Undefined index: Order in C:\Websites\testsite\html\modules\WoWProfiler\lib\wowdb.php on line 172

The code I posted earlyer is the wowdb.php code

8

Re: How to call the right db table?

2005/1/25 2:58
Dave_L
XOOPS is my life!
Posts: 2277
Since: 2003/11/7

Does the script work? That notice may not be important.

Also, I don't see any place in the code you posted where 'Order' is used as an index.

9

Re: How to call the right db table?

2005/1/25 3:08
jstacy
Just popping in
Posts: 7
Since: 2002/12/16

well here is the function that it is referanceing

 function do_skills( $data ) { 
    #first delete the stale data 
    $name = $this->escape( $data['CharName'] ); 
    $server = $this->escape( $data['Server'] ); 
    $querystr = sprintf("DELETE FROM $skill_table WHERE `char` = '%s' " . 
            "and server ='%s'", 
            $name, $server); 
    mysql_query($querystr) or die(mysql_error()); 
 
    foreach( array_keys( $data['Skills'] ) as $skill_type ) { 
      $sub_skill = $data['Skills'][$skill_type]; 
      $order = $sub_skill['Order']; 
      foreach( array_keys( $sub_skill ) as $skill_name ) { 
    if( $skill_name != 'Order' ) { 
      $this->reset_values(); 
      $this->add_value('char', $data['CharName'] ); 
      $this->add_value('server', $data['Server'] ); 
      $this->add_value('type', $skill_type ); 
      $this->add_value('name', $skill_name ); 
      $this->add_value('order', $order ); 
      $this->add_value('level', $sub_skill[$skill_name] ); 
      $querystr = "INSERT INTO $skill_table SET " . $this->assignstr; 
      print " n"; 
      mysql_query($querystr) or die(mysql_error()); 
       
    } 
      } 
    } 
  }

It does populate the database but not sure if it is populating everything

Stats
Goal:	$100.00
Due Date:	Nov 30
Gross Amount:	$0.00
Net Balance:	$0.00
Left to go:	$100.00

How to call the right db table?

Re: How to call the right db table?

Re: How to call the right db table?

Re: How to call the right db table?

Re: How to call the right db table?

Re: How to call the right db table?

Re: How to call the right db table?

Re: How to call the right db table?

Re: How to call the right db table?

Login

Search

Recent Posts

XOOPS MyMenus 1.54.0 Beta 10

Re: XOOPS MyMenus 1.54.0 Beta 7

Re: XOOPS MyMenus 1.54.0 Beta 7

Re: XOOPS MyMenus 1.54.0 Beta 7

Re: XOOPS MyMenus 1.54.0 Beta 7

Re: XOOPS MyMenus 1.54.0 Beta 7

Re: XOOPS MyMenus 1.54.0 Beta 7

Re: XOOPS MyMenus 1.54.0 Beta 7

Re: XOOPS MyMenus 1.54.0 Beta 7

Re: XOOPS MyMenus 1.54.0 Beta 7

Who's Online

Donat-O-Meter

Latest GitHub Commits

{{ record.sha.slice(0, 7) }} - {{ record.commit.message | truncate }}