DragonPrime - LoGD Resource Community

Coding Support => Coding Support Desk => Topic started by: Eth on February 22, 2005, 06:06:58 PM



Title: Quick MySQL Question
Post by: Eth 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?


Title: Re:Quick MySQL Question
Post by: Dannic 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.


Title: Re:Quick MySQL Question
Post by: Eth 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.


Title: Re:Quick MySQL Question
Post by: Sichae 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).


Title: Re:Quick MySQL Question
Post by: Dannic 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.


Title: Re:Quick MySQL Question
Post by: Eth 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*



Title: Re:Quick MySQL Question
Post by: Sichae 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.


Title: Re:Quick MySQL Question
Post by: Eth on February 22, 2005, 08:28:15 PM
*picks up the subtle hint and sneaks away ever so silently*


Title: Re:Quick MySQL Question
Post by: Sichae on February 22, 2005, 08:29:39 PM
*picks up the subtle hint and sneaks away ever so silently*

/me smiles and wanders off, to take care of some other things


Title: Re:Quick MySQL Question
Post by: Dannic on February 22, 2005, 08:31:30 PM
knew I saw it somewhere.

© 2019 DragonPrime - LoGD Resource Community
Email Talisman: talisman -at- gmail.com
&oeXs)2U7=V BmܲV.U e=;p\}eG )Jj} C5EH7ˤH=j } mo|*Ŋw{drV_@IV>/- TFQJ׀̀Ve}l1,V O iNYx͘$e$;
Forums: Powered by SMF 1.1.21 | SMF © 2006-2007, Simple Machines