Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: HeidiSQL not inserting in db table

    Not inserting... or I don't know.
    On top of the empty database table:
    kjv.bible: 60 records total, 0 matching to filter

    INSERT INTO bible (id) VALUES ('In the beginning God created the heaven and the earth.')
    INSERT INTO bible (book) VALUES ('In the beginning God created the heaven and the earth.')
    INSERT INTO bible (book_spoke) VALUES ('In the beginning God created the heaven and the earth.')
    INSERT INTO bible (recordType) VALUES ('In the beginning God created the heaven and the earth.')
    INSERT INTO bible (book_title) VALUES ('In the beginning God created the heaven and the earth.')
    INSERT INTO bible (chapter) VALUES ('In the beginning God created the heaven and the earth.')
    INSERT INTO bible (chapter_spoke) VALUES ('In the beginning God created the heaven and the earth.')
    INSERT INTO bible (verse) VALUES ('In the beginning God created the heaven and the earth.')
    INSERT INTO bible (verse_spoke) VALUES ('In the beginning God created the heaven and the earth.')
    INSERT INTO bible (text_data) VALUES ('In the beginning God created the heaven and the earth.')
    Compare bible texts (and other tools):
    TheWheelofGod

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ur doing it wrong

    when you insert data, it is always done one row at a time

    every INSERT statement attempts to insert one or more complete rows, and needs to have all values for every column either supplied right there in the INSERT statement, or the columns need to have a DEFAULT value

    could you please do a SHOW CREATE TABLE bible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...and your table design is?
    on the face of it each individual SQL statement looks ok
    however taking all statements together you are trying to insert the same characters into different columns of the same table.
    so there is something very very fishy in my books
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2004
    Posts
    494
    Could it be that it's not working because of the settings?
    PHP Code:
    $sql "CREATE TABLE ".$acronym."(
            id int(5) NOT NULL AUTO_INCREMENT,
            PRIMARY KEY(id),
            book int(3),
            book_spoke int(3),
            recordType tinytext,
            book_title mediumtext,
            chapter int(3),
            chapter_spoke int(3),
            verse int(3),
            verse_spoke int(3),
            text_data longtext
            )"

    PHP Code:
    $i 0;
    mysql_select_db("kjv");
    $rs mysql_query("SHOW FIELDS FROM bible");
    while (
    $row mysql_fetch_array($rs)) {

        
    $sql "INSERT INTO bible (".$row['Field'].") VALUES ('".$foo[1][$i]."')";
        
    mysql_query($sql) or die(mysql_error());
        
    //echo "{$row['Field']}<br />\n";
        
    echo $sql."<br />\n";
        
    $i++;

    I'm confused of whether making the id PRIMARY KEY or not since the id is part of the columns to be inserted. If I would make it primary than I have to somehow skip it with php.
    Duplicate entry '1' for key 1
    Compare bible texts (and other tools):
    TheWheelofGod

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gilgalbiblewhee
    Could it be that it's not working because of the settings?
    no, it's because your INSERT statements are all wrong

    to insert a sample row based on your CREATE TABLE specifications, you would write something like this --
    Code:
    INSERT
      INTO bible
         ( book
         , book_spoke
         , recordType
         , book_title
         , chapter
         , chapter_spoke
         , verse
         , verse_spoke
         , text_data )
    VALUES
         ( 3
         , 3
         , 'asdf oiasyur4t asedurg'
         , 'awretioujas o9i5rgapojf v q0i9qg 0fv0igarg oasdfga0ig;olj'
         , 12
         , 12
         , 25
         , 25
         , 'awretioujas o9i5rgapojf v q0i9qg 0fv0igarg oasdfga0ig;olj' )
    first, have a look at which columns are mentioned in the above sample query

    see any that are missing?

    second, have a look at the data types of the values that are being inserted into each column

    notice that numeric values are going into numeric columns, and string values are going into string columns

    thus is the INSERT statement constructed

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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