HeidiSQL not inserting in db table

gilgalbiblewhee · #1 (**permalink**) 07-22-08, 18:28

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

Quote:

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.')

r937 · #2 (**permalink**) 07-22-08, 18:44

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

healdem · #3 (**permalink**) 07-22-08, 18:46

...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

gilgalbiblewhee · #4 (**permalink**) 07-22-08, 19:41

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.

Quote:

Duplicate entry '1' for key 1

r937 · #5 (**permalink**) 07-22-08, 22:28

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

Internet Services

Web Development

Digital Marketing

Consumer Tech