Professional Web Applications Themes

insert problem.... - MySQL

CREATE TABLE `php_mysql`.`contacts` ( `id` int(6) NOT NULL auto_increment, `first` varchar(15) NOT NULL, `last` varchar(15) NOT NULL, `phone` varchar(20) default NULL, `mobile` varchar(20) default NULL, `fax` varchar(20) default NULL, `email` varchar(30) default NULL, `web` varchar(30) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE KEY `last_name` (`last`), KEY `id_2` (`id`) ) ENGINE=InnoDB DEFAULT CHT=latin1; insert into table contacts values '','steve','cristaldi','','','','',''); 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 'table contacts values ('','steve','kurowski','','','','','')' at line 1...

  1. #1

    Default insert problem....

    CREATE TABLE `php_mysql`.`contacts` (
    `id` int(6) NOT NULL auto_increment,
    `first` varchar(15) NOT NULL,
    `last` varchar(15) NOT NULL,
    `phone` varchar(20) default NULL,
    `mobile` varchar(20) default NULL,
    `fax` varchar(20) default NULL,
    `email` varchar(30) default NULL,
    `web` varchar(30) default NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id` (`id`),
    UNIQUE KEY `last_name` (`last`),
    KEY `id_2` (`id`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    insert into table contacts values '','steve','cristaldi','','','','','');


    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 'table
    contacts values ('','steve','kurowski','','','','','')' at line 1
    someone Guest

  2. #2

    Default Re: insert problem....

    removed keyword table from insert, got error as follows

    Out of range value adjusted for column 'id' at row 1


    someone wrote:
     
    someone Guest

  3. #3

    Default Re: insert problem....


    someone wrote: 

    If you copied and pasted the above from some database
    client, then it is possible the error is due to the back-tick
    characters shown in your create table statement - They
    should be single quotes (') instead.

    I would start with those and see what you get from there.
    You might even try to remove the 'ENGINE.....' part.

    Cheers,
    Chris Val

    Chris Guest

  4. #4

    Default Re: insert problem....

    On Fri, 22 Sep 2006 02:12:10 GMT, someone wrote: [/ref]
    .. ^^^^^^ [/ref]

    A) you'll not be inserting '' into an int(6) column any time soon.
    B) which means you'll probably want to enumerate the columns you ARE
    filling:

    insert into contacts ( first, last, phone, mobile fax, web )
    values ('steve', 'cristaldi', '', '', '', '' );

    C) you're allowing the other columns to be null... why not let them be
    null instead of blank? As in:

    insert into contacts ( first, last, )
    values ( 'steve', 'cristaldi' );

    D) I am not sure about the UNIQUE on the last name. What happens when
    you have Joe Smith and Bill Smith? (I'm honestly unsure about this one;
    I haven't played with this option by hand.)

    --
    This was, apparently, beyond her ken. So far beyond her ken that she was
    well into barbie territory.
    -- J. D. Baldwin
    Peter Guest

  5. #5

    Default Re: insert problem....

    Chris ( Val ) wrote: 
    >
    >
    > If you copied and pasted the above from some database
    > client, then it is possible the error is due to the back-tick
    > characters shown in your create table statement - They
    > should be single quotes (') instead.
    >
    > I would start with those and see what you get from there.
    > You might even try to remove the 'ENGINE.....' part.
    >
    > Cheers,
    > Chris Val
    >[/ref]

    Chris,

    No, the back-ticks are correct syntax. These are column names, not values.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  6. #6

    Default Re: insert problem....

    someone wrote: [/ref]

    It's because you're trying to insert an empty string in the id column.
    This is not a valid int value.

    You should always specify the columns when you do an INSERT - that way
    if later you need to add a column to the table all of your INSERTs will
    not break (as long as you have a default value for the column). As it
    is, if you add a column your INSERT will fail.

    insert into contacts(first, last, phone, mobile, fax, email, web)
    values ('steve','cristaldi','','','','','');

    Otherwise, if you really insist on doing it without the column names,

    insert into contacts values (NULL, 'steve','cristaldi','','','','','');

    The NULL value tells an auto_increment column to increment to the next
    value. 0 should work also, but I haven't tried it.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. a problem about cfinsert&sql.insert
    By justay in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 14th, 03:12 PM
  2. Problem with insert dates
    By CFcippa in forum Coldfusion - Getting Started
    Replies: 0
    Last Post: June 22nd, 04:07 PM
  3. ' & insert problem
    By Dicky in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 25th, 12:17 PM
  4. INSERT MAX(ID) Syntax problem
    By Targa in forum ASP Database
    Replies: 2
    Last Post: May 1st, 08:01 PM
  5. Problem with insert
    By ASP MS Access insert issue in forum ASP Components
    Replies: 2
    Last Post: April 12th, 02:46 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