Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hmm, I cant seem to figure out whats wrong here, it just wont add. 
  $sth = $dbh->prepare("INSERT INTO news(id, username, month, day, hou
+r, min, AP, title, description, year) VALUES ('', '$user', '$months[$
+mon]', '$mday', '@hours[$hour]', '$min', '$AP', ?, ?, '$year')");
  $sth->execute(param("title"), param("des"));

[download]

Replies are listed 'Best First'.
Re: MySQL Problem
by stajich (Chaplain) on Jul 19, 2002 at 03:54 UTC
    use strict would help and the error msg would probably help some too!

    Why mix bound params and within string variables? Make them all bound in case your $user or $AP vars have quotes in them which needed to be escaped.

    @hours[$hour] should be $hours[$hour].

    There may be more but it would be much easier if we had the error message which usually gives some hints.

[reply]
[d/l]
[select]
Re: MySQL Problem
by seattlejohn (Deacon) on Jul 19, 2002 at 05:17 UTC
    As others have said: check the error string ($DBI::errstr) after your prepare and execute statements. Also, consider using the quote method to prepare your SQL query so you don't run into trouble with embedded apostrophes in arguments and other similar nastiness.

    Also, do you really want @hours[$hour] or do you mean $hours[$hour]? Is param a function that returns something, or did you intend to use a hash ($param{title}) or a method call ($query->param("title"))?

    There are a couple of other debugging techniques that can be helpful in situations like this. One is to concatenate your SQL query into a string and print that to the console/log/whatever, then inspect it to make sure you got the results you were expecting. Another is to connect directly to the database using the command-line SQL monitor and tune your query there to make sure it's doing what you think.

[reply]
[d/l]
[select]
Re: MySQL Problem
by tstock (Curate) on Jul 19, 2002 at 03:49 UTC
    Did you try getting the error message back from MySQL ? Try to find out what's breaking by using something like: 
    warn $dbh->errstr if $dbh->errstr;

    [download]
[reply]
[d/l]
Re: MySQL Problem
by screamingeagle (Curate) on Jul 19, 2002 at 06:59 UTC
    besides the points that the other monks have made, it looks like you are trying to insert a blank or NULL value to the primary key of the table (i assume that the "id" column is the primary key of the "news" table. A primary key cannot be null. try adding a valid value to the id column and see what happens
[reply]
Re: MySQL Problem
by dsalada (Scribe) on Jul 19, 2002 at 12:04 UTC
    As screamingeagle said, if "id" is your primary key you can't enter a blank value for that. And if it is a column of type auto_increment, you probably won't want to put anything into the id column. Leave the "id" column off of the column list in that case.
[reply]

Back to Seekers of Perl Wisdom