Don't know why this query doesn't work.

mysql_query("insert into mytablename (`id`) VALUES ('450')");

Here is the table structure.

CREATE TABLE `mytablename` (
  `id` int(255) NOT NULL,
  `test_1` longtext NOT NULL,
  `test_2` longtext NOT NULL,
  `test_3` longtext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Seems to me it doesn't like empty values for test_1, test_2, etc. I assume if I could set a default value for those fields it would accept it but you can't set a default value on a text/blob field.

I'm sure I'm missing some staring me right in the face but for the life of me can't figure out why the new line won't go in. I just want a ID inserted with blank data.

I'd insert data as well but test_1, test_2, etc. are user created so I'll never know exactly how many columns are in the table.

    Scriptmaker wrote:

    Try replacing id with 'id'

    Er... that would simply add more errors into the mix (single quotes delimit strings, not column names).

    Instead, you should always debug your SQL queries by first figuring out what MySQL is trying to tell you. For example, this is how I commonly debug queries:

    $query = 'query here';
$exec = mysql_query($query) or die('MySQL error: ' . mysql_error() . '<hr>Query: ' . $query);

    Now, rather than waiting for you to post back with the error message, I can tell you right now the problem lies in the fact that you have 4 columns in your table and all of them require values (hence the "NOT NULL" bit).

    You don't, however, specify default values for them in your table structure. Therefore, when you attempt to INSERT data into the table and don't give all of those NOT NULL columns a value (even if it's an empty string, which is not the same as NULL), the query fails.

      Very nice. Thank you. So I've modified the table structure like this:

      CREATE TABLE `mytablename` ( 
  `id` int(255) NOT NULL, 
  `test_1` longtext, 
  `test_2` longtext, 
  `test_3` longtext, 
  PRIMARY KEY  (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      It worked and added NULL to each of the test_* fields. When I go to edit the field, nothing shows. I as expecting to see the word "NULL" but don't which is a good thing. Upon editing the (blank) field, it updates the field as it should.

      From what I can see, everything works fine. As long as you tell me it's ok to have those "NULL" values in the empty fields, I'll mark this thread solved.

      Thanks again!

        The_PHP_Newb wrote:

        As long as you tell me it's ok to have those "NULL" values in the empty fields

        Eh... you have to tell yourself that; it's your DB.

        Would it make sense for those fields to not have a value? Would it make more sense to give them a default vaule?

        Just remember that letting fields be NULL allows you to do comparisons e.g. "WHERE test_1 IS NULL AND test_2 IS NOT NULL" and so on.

        Long story short: It's up to you.

          Write a Reply...