#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    25
    Rep Power
    0

    MYSQL Table Help


    I haven't had problems learning to create tables and doing queries until now. Here's the show create table tag:

    | job_listings | CREATE TABLE `job_listings`
    `job_id` int(11) NOT NULL AUTO_INCREMENT,
    `title` int(30) DEFAULT NULL,
    `salary` int(30) DEFAULT NULL,
    `zip` int(5) DEFAULT NULL,
    `description` int(30) DEFAULT NULL,
    PRIMARY KEY (`job_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |



    And here is me inserting values:
    INSERT INTO job_listings VALUES ('Cook', NULL, NULL, NULL), ('Waitress', NULL, NULL, NULL), ('Hairdresser', NULL, NULL, NULL), ('Web Designer', NULL
    , NULL, NULL), ('Web Developer', NULL, NULL, NULL);

    And here is my error:
    ERROR 1136 (21S01): Column count doesn't match value count at row 1

    I'm either having a brainfart and I'm forgetting to input a "job id" at the beginning of each value, but for some reason I remember that it's auto increment so it would do it itself when I do a query. Anytime I DO put numbers before Cook, Waitress, Hairdresser, etc, here is the error:

    ERROR 1366 (HY000): Incorrect integer value: 'Cook' for column 'title' at row 1
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    Have you tried setting it up this way?

    PHP Code:
    INSERT INTO table_name (column1column2column3,...) VALUES (value1value2value3,...) 
  4. #3
  5. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,959
    Rep Power
    9397
    It will do it automatically as long as you tell MySQL that you aren't trying to provide a value for it. With the syntax you're using, as opposed to the one demonstrated by fayt84, you're saying that you will provide a value for every single column in the table. NULL will work for auto_increment values but being explicit about the columns you're filling in is better.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    25
    Rep Power
    0
    mysql> INSERT INTO job_listings ('title', 'salary', 'zip', 'description') VALUES ('Cook', NULL, NULL, NULL), ('Hairdresser', NULL, NULL, NULL), ('Waitress'
    , NULL, NULL, NULL), ('Web Designer', NULL, NULL, NULL), ('Web Developer', NULL, NULL, NULL);
    ERROR 1064 (42000): 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 '
    'title', 'salary', 'zip', 'description') VALUES ('Cook', NULL, NULL, NULL), ('Ha' at line 1
    mysql>
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    25
    Rep Power
    0
    Anyone? I can't believe I forgot the original columns but I'm still getting that error at the top.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by powersoftllc
    mysql> INSERT INTO job_listings ('title', 'salary', 'zip', 'description') ...
    get rid of those single quotes, they turn your column names into strings

    the insidious mysql backticks f*ck up a lot of people, basically you don't need to use them at all

    (unless your table/column name contains a special character like a space, or is the same as a reserved word, but neither of those is a good idea in the first place...)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    25
    Rep Power
    0
    Just tried that, still getting this:

    ERROR 1366 (HY000): Incorrect integer value: 'Cook' for column 'title' at row 1


    Really irritating for me, sorry if I'm not getting it.
  14. #8
  15. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,575
    Rep Power
    1906
    Title is defined as INT, if you want to have text you should define the it as VARCHAR.

    FYI, INT(size) does not have the same meaning as VARCHAR(size).
    You can get more information about it here
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    25
    Rep Power
    0
    Oh dear god I've really lost my mind today. I didn't even notice everything was INT because I was so used to putting everything as VARCHAR. Thank you sir, I am an idiot.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by MrFujin
    Title is defined as INT, if you want to have text you should define the it as VARCHAR.

    FYI, INT(size) does not have the same meaning as VARCHAR(size).
    You can get more information about it
    Good call, I didn't catch that one
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    25
    Rep Power
    0
    CREATE TABLE job_listings (job_id NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(30), salary INT(6), zip INT(5), description VARCHAR(30));

    ERROR 1064 (42000): 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 '
    NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(30), salary INT(6), zip INT(5' at line 1
  22. #12
  23. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,575
    Rep Power
    1906
    job_id have no type assigned. Should be defined as int like it is shown in your first post.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    25
    Rep Power
    0
    Man you guys are great

IMN logo majestic logo threadwatch logo seochat tools logo