Professional Web Applications Themes

Alter table query problem - MySQL

Need a little newbie help with altering a table in mysql. I created a varchar(255) column that is no longer suitable for my needs and I wan to change it to a memo field. I have constructed the following query to change the column type but it doesn't work. Any help greatly appreciated. <?php $query1="ALTER TABLE Format CHANGE ptext memo"; // execute query $result = mysql_query($query1) or die ("Error in query $query1" . mysql_error()); // close connection mysql_close($connection); ?> This is the error message I get Error in query ALTER TABLE Format CHANGE ptext memoYou have an error in your ...

  1. #1

    Default Alter table query problem

    Need a little newbie help with altering a table in mysql. I created a
    varchar(255) column that is no longer suitable for my needs and I wan to change
    it to a memo field. I have constructed the following query to change the column
    type but it doesn't work. Any help greatly appreciated.

    <?php
    $query1="ALTER TABLE Format CHANGE ptext memo";
    // execute query
    $result = mysql_query($query1) or die ("Error in query $query1" .
    mysql_error());
    // close connection
    mysql_close($connection);
    ?>

    This is the error message I get

    Error in query ALTER TABLE Format CHANGE ptext memoYou have an error in your SQL
    syntax; check the manual that corresponds to your MySQL server version for the
    right syntax to use near '' at line 1

    TIA
    Jenny

    Jenny Rice Guest

  2. #2

    Default Re: Alter table query problem

    Jenny Rice wrote:
    > Need a little newbie help with altering a table in mysql. I created a
    > varchar(255) column that is no longer suitable for my needs and I wan to change
    > it to a memo field. I have constructed the following query to change the column
    > type but it doesn't work. Any help greatly appreciated.
    [ALTER TABLE Format CHANGE ptext memo]

    Please do not post php if it has no use.
    > This is the error message I get
    >
    > 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 '' at line 1
    Did you check the MySQL manual for your server version? There is a
    subsection about the syntax for ALTER TABLE.

    [url]http://dev.mysql.com/doc/refman/5.0/en/alter-table.html[/url]

    There it says that you have to repeat the column definition if you want
    to rename the column

    CHANGE old_col_name column_definition

    In your case old_col_name is `ptext` and column_definition is "memo
    VARCHAR(255)"

    HTH
    Kai

    --
    This signature is left as an exercise for the reader.
    Kai Ruhnau Guest

  3. #3

    Default Re: Alter table query problem

    Apologies for using php but I thought perhaps the error may lie within the php
    code rather than the mysql statement. I have looked at the page you refer to and
    I see what you mean. Have changed statement to
    [ALTER TABLE Format CHANGE ptext ptext text]
    and everything works fine.

    Many thanks
    Jenny

    In article <e66ugd$1cf$02$1news.t-online.com>, Kai Ruhnau says...
    >
    >[ALTER TABLE Format CHANGE ptext memo]
    >
    >Please do not post php if it has no use.
    >
    >
    >Did you check the MySQL manual for your server version? There is a
    >subsection about the syntax for ALTER TABLE.
    >
    >[url]http://dev.mysql.com/doc/refman/5.0/en/alter-table.html[/url]
    >
    >There it says that you have to repeat the column definition if you want
    >to rename the column
    >
    >CHANGE old_col_name column_definition
    >
    >In your case old_col_name is `ptext` and column_definition is "memo
    >VARCHAR(255)"
    >
    >HTH
    >Kai
    >
    Jenny Rice Guest

  4. #4

    Default Re: Alter table query problem

    I had difficulty in finding memo field too. My db tools trial software expired a
    couple of days ago and I was convinced that when I was constructing tables
    within db tools that it gave the option of a memo field. However I have used
    text as you suggested and it has done the trick. Is there a definitive table of
    the available field types and what they mean (e.g. what is the largest number
    that can be used in an INT type or BIGINT, or what is the max number of
    characters that can be used in a TEXT type etc etc)

    Many thanks for your help

    Jenny

    In article <slrne8dvj7.2sl.hellsopothin.ninehells.com>, Peter H. Coffin says...
    >I find no "memo" column type in the MySQL manual, and the only reference
    >to it is
    >[url]http://dev.mysql.com/doc/refman/5.1/en/programs-known-to-work-with-myodbc.html[/url]
    >which talks about making column definitions label as such in the NT
    >version of Access by changing the MySQL column definition to type TEXT.
    >Perhaps that is the column type you are looking for.
    >
    Jenny Rice Guest

Similar Threads

  1. Alter Table / Changing field types in SQL
    By Fordian-Slip in forum Coldfusion Database Access
    Replies: 2
    Last Post: January 6th, 11:03 PM
  2. 2 table query problem
    By glossen in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 24th, 12:01 PM
  3. alter a DMS table space
    By xixi in forum IBM DB2
    Replies: 0
    Last Post: August 4th, 09:18 PM
  4. alter table/tables
    By Jacco Schalkwijk in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 11th, 08:41 AM
  5. alter table move into temporary tablespace.
    By Charles J. Fisher in forum Oracle Server
    Replies: 3
    Last Post: December 16th, 04:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139