Can someone tell me what is wrong with the SQL in this query?

$DB->query("ALTER TABLE " . TABLE_PREFIX . "users ADD COLUMN p71_name TINYTEXT(33) DEFAULT '' AFTER lastactivity");

I had a bunch of other SQL commands in there too but I took them out thinking they were the problem. This, even without default, doesn't work.

$DB->query("ALTER TABLE " . TABLE_PREFIX . "users ADD COLUMN p71_name TINYTEXT(33) AFTER lastactivity");

    Try

    $DB->query("ALTER TABLE " . TABLE_PREFIX . "users ADD COLUMN p71_name TINYTEXT DEFAULT '' AFTER lastactivity");

      No, that didn't work. All it did was automatically add "(1)" after TINYTEXT.

        I did try and was able to get it working, hopefully someone else will be able to help.

        Sorry I wasn't able to get it working for you.

          What error message(s) do you get? Have you tried simply echoing out the query string from your script, then copying-and-pasting it into a command line session of your DBMS to see if it works? Are you connecting to the database using a user who has the necessary privileges to alter tables?

            I am creating a plugin for a CMS, Subdreamer. It gives no specific message, just that there is an error when it tries to run that query.

            Yes, the user has sufficient privlages to alter tables.

            And I'm sure if I copy/pasted it right into phpMyAdmin, it would work, like Joe has shown us. But I need the script to be able to do it.

              I'd suggest doing the following to help debug. Since I don't know what class your $DB object is derived from, I'm not sure how to get error information out of it, so you'll need to adjust as applicable.

              <?php
// before any of your code executes:
ini_set('display_errors', 1);
error_reporting(E_ALL | E_STRICT);

// then add some debugging to your query code:
$query = "ALTER TABLE " . TABLE_PREFIX . "users ADD COLUMN p71_name TINYTEXT(33) DEFAULT '' AFTER lastactivity";
$result = $DB->query($query);
if($result == false)
{
   // hypothetical method "last_error()", modify as appropriate
   die('Query failed -- ' . $DB->last_error() . '<br />' . $query);  
              
}

              If there is no error method in the $DB object and you are using MySQL, you could try mysql_error() instead.

                Okay. I tried what you said, and put that code in in the proper places. It went through [meaning it didn't hit the if result = false], but then gave me the error it's been giving me. I went into phpMyAdmin, and into the database, and into the SQL tab, and pasted the SQL code that I've been trying to get to work. Here is the error I got:

                ALTER TABLE sd_users ADD COLUMN p71_name TINYTEXT( 33 ) DEFAULT '' AFTER lastactivity

MySQL said: Documentation
#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 '(33) DEFAULT '' AFTER lastactivity' at line 1

                So I played around with it a bit, and found out that this code works fine, when inserted via phpMyAdmin:

                ALTER TABLE sd_users ADD COLUMN p71_name TINYTEXT NOT NULL DEFAULT '' AFTER lastactivity

                So now my question is.. how do I make a seperate SQL code that adds the limits like I need? [ex. TINYTEXT(33)]

                  Write a Reply...