DragonPrime - LoGD Resource Community
Welcome Guest
  • Good evening, Guest.
    Please log in, or register.
  • November 18, 2018, 07:45:38 PM
Home Forums News Downloads Login Register Advanced Search
* * *
DragonPrime Menu
Login
 
 
Resource Pages
Search

Pages: [1]   Go Down
  Print  
Author Topic: Quick MySQL Question  (Read 3339 times)
0 Members and 1 Guest are viewing this topic.
Eth
Member
Codemeister
****
Offline Offline

Posts: 302


Shameless Drunk


View Profile
« on: February 22, 2005, 06:06:58 PM »

So I was sitting here and wondering....

Is there a way to check to see if a field exists within a table, and if not, install the needed field?

More specifically:
I released a new version of my petshop mod last night, and within the peteditor was a new field for the pets database. So I was essentially wondering is there a way to check for this field in the install routine, and then have the module install it?
Logged

Purveyor of fine cloaks, gloves, boots, gerbils, and Siamese cats.
Dannic
Guest
« Reply #1 on: February 22, 2005, 06:45:09 PM »

Code:
$fieldname = 'pets';
$table = 'pets'
$fieldexists = false;
$sql ="SHOW FIELDS FROM `".db_prefix($table)."`";
$result = db_query($sql);
for ($i=0;$i<db_num_rows($result);$i++){
      $row = db_fetch_assoc($result);
                if (strtolower($row['field']) == $fieldname) {
                       $fieldexists = true;
                 }else{
                       $fieldexists = false;
                 }
}

That might do some of what you are looking for.

EDIT:  That should be better.
« Last Edit: February 22, 2005, 07:44:05 PM by Dannic » Logged
Eth
Member
Codemeister
****
Offline Offline

Posts: 302


Shameless Drunk


View Profile
« Reply #2 on: February 22, 2005, 07:29:07 PM »

Ah, thanks Dannic. Will have to give this a shot in a moment. I just thought something like this would be far more convienent than having the admin perform extra steps just to update said module. I think I shall be able to construct the needed code from the snippet you have provided. Thanks again.

Now, off to do some testing.
Logged

Purveyor of fine cloaks, gloves, boots, gerbils, and Siamese cats.
Sichae
iMod God
SVN Users
Mod God
*
Offline Offline

Posts: 3458


If ya didn't get it by now... you're hopeless...


View Profile WWW
« Reply #3 on: February 22, 2005, 07:31:43 PM »

You might also wish to make that db_prefix Compatable. ^_^

Code:
$sql ="SHOW FIELDS FROM ".db_prefix($table")."";
As well, Dannic's way doesn't actually show the $result. You need to change $row = db_query($sql) to $result = db_query($sql).
« Last Edit: February 22, 2005, 07:34:41 PM by Sichae » Logged

If you didn't understand anything in the above post, don't try to attempt anything suggested.

Dannic
Guest
« Reply #4 on: February 22, 2005, 07:39:56 PM »

yep.. missed that.

If that doesn't do it let me know.  I may have converted it wrong.
« Last Edit: February 22, 2005, 07:44:37 PM by Dannic » Logged
Eth
Member
Codemeister
****
Offline Offline

Posts: 302


Shameless Drunk


View Profile
« Reply #5 on: February 22, 2005, 08:15:48 PM »

Doesn't appear to work, keeps returning a value of false whether the field exists or not.

*Goes about finding a way to make this work on his own as well*

Logged

Purveyor of fine cloaks, gloves, boots, gerbils, and Siamese cats.
Sichae
iMod God
SVN Users
Mod God
*
Offline Offline

Posts: 3458


If ya didn't get it by now... you're hopeless...


View Profile WWW
« Reply #6 on: February 22, 2005, 08:25:51 PM »

Hmm... isn't there a mod, that checks for fields in the accounts table?

*subtle hint*

Code:
$sql = "DESCRIBE " . db_prefix("accounts");
   $result = db_query($sql);
   $specialty="DA";
   while($row = db_fetch_assoc($result)) {
      // Convert the user over
      if ($row['Field'] == "darkarts") {
         debug("Migrating darkarts field");
         $sql = "INSERT INTO " . db_prefix("module_userprefs") . " (modulename,setting,userid,value) SELECT 'specialtydarkarts', 'skill', acctid, darkarts FROM " . db_prefix("accounts");
         db_query($sql);
         debug("Dropping darkarts field from accounts table");
         $sql = "ALTER TABLE " . db_prefix("accounts") . " DROP darkarts";
         db_query($sql);
      } elseif ($row['Field']=="darkartuses") {
         debug("Migrating darkarts uses field");
         $sql = "INSERT INTO " . db_prefix("module_userprefs") . " (modulename,setting,userid,value) SELECT 'specialtydarkarts', 'uses', acctid, darkartuses FROM " . db_prefix("accounts");
         db_query($sql);
         debug("Dropping darkartuses field from accounts table");
         $sql = "ALTER TABLE " . db_prefix("accounts") . " DROP darkartuses";
         db_query($sql);
      }
   }
   debug("Migrating Darkarts Specialty");
   $sql = "UPDATE " . db_prefix("accounts") . " SET specialty='$specialty' WHERE specialty='1'";
   db_query($sql);

So, jsut Describe your table, check the fields... if the field is in there, migrate values by Altering the row.
« Last Edit: February 22, 2005, 08:27:39 PM by Sichae » Logged

If you didn't understand anything in the above post, don't try to attempt anything suggested.

Eth
Member
Codemeister
****
Offline Offline

Posts: 302


Shameless Drunk


View Profile
« Reply #7 on: February 22, 2005, 08:28:15 PM »

*picks up the subtle hint and sneaks away ever so silently*
Logged

Purveyor of fine cloaks, gloves, boots, gerbils, and Siamese cats.
Sichae
iMod God
SVN Users
Mod God
*
Offline Offline

Posts: 3458


If ya didn't get it by now... you're hopeless...


View Profile WWW
« Reply #8 on: February 22, 2005, 08:29:39 PM »

*picks up the subtle hint and sneaks away ever so silently*

* Sichae smiles and wanders off, to take care of some other things
Logged

If you didn't understand anything in the above post, don't try to attempt anything suggested.

Dannic
Guest
« Reply #9 on: February 22, 2005, 08:31:30 PM »

knew I saw it somewhere.
Logged
Pages: [1]   Go Up
  Print  
 
Jump to:  


*
DragonPrime Notices
Version 1.1.2 is the current supported version and is available for download.

Support Us
No funds raised yet this year
Your help is greatly appreciated!
Recent Topics
DragonPrime LoGD
Who's Online
44 Guests, 0 Users
Home Forums News Downloads Login Register Advanced Search