Data truncated for column.......

[BrotherLogic]

I am trying some code in PHP and when I try to INSERT values into a MySQL database my program dies.


When I call mysql_error() it reads "Data truncated for column fundname at row 1"

Anybody know what a possible cause to the problem is?

Thanks EVERYONE

[AndyB]

Please post the MySQL query that produces this error.

[BrotherLogic]

  $createtablequery = 'CREATE TABLE longterm( '.
        'fid INT NOT NULL AUTO_INCREMENT, '.
        'fundname VARCHAR(75) NOT NULL, '.
        'starrating VARCHAR(60) NOT NULL, '.
        'firstyear FLOAT, '.
        'thirdyear FLOAT, '.
        'fifthyear FLOAT, '.
        'tenthyear FLOAT, '.
        'fifthteenyear FLOAT, '.
        'PRIMARY KEY(fid))';

  $insertfundquery = 'INSERT INTO longterm( fundname,'.
          'starrating, firstyear, thirdyear,'.
          'fifthyear, tenthyear, fifthteenyear)'.
          'VALUES ( "$fundname", "$starrating",'.
          '"$firstyear", "$thirdyear", "$fifthyear",'.
          '"$tenthyear", "$fifthteenyear")';

[BrotherLogic]

aand the exact error reads: "Data truncated for column 'firstyear' at row"

[fenway]

What is the value for $firstyear that you're trying to insert? It obviously doesn't match the column spec.

[BrotherLogic]

the values are as follows: 7.04 , 1.66, 21.11, 27.35, 0, etc. I am getting the same error with all the values.

Thanks for the help everyone. I am a mySQL newbian.

[AndyB]

[code]$insertfundquery = 'INSERT INTO longterm( fid, fundname, starrating, firstyear, thirdyear, fifthyear, tenthyear, fifthteenyear)
VALUES ("", "$fundname", "$starrating","$firstyear", "$thirdyear", "$fifthyear","$tenthyear","$fifthteenyear")';[/code]

[BrotherLogic]

Still not working.

I am getting "data truncated for column 'firstyear' at row 1" from mysql_error

and I am getting ERROR #1265 from mysql_errno.


Anybody know what the problem might be?

Thanks a bunch.

[AndyB]

My sql knowledge is rudimentary but I'm wondering if the FLOAT declarations (in your table definition) actually need to give the field definition rather than just 'FLOAT', i.e, FLOAT (5,2) to allow a signed four-figure number and 2 decimal places.

[fenway]

Perhaps... not sure what the default would be with no (M,D)... post the SHOW CREATE output and you'll see for certain.

[BrotherLogic]

I've tried the (5,2) in the definition of the table.
Still the same error number and error output.

[Chetan]

You guys, simple
he uses variables in ''
 $insertfundquery = 'INSERT INTO longterm( fundname,'.
          'starrating, firstyear, thirdyear,'.
          'fifthyear, tenthyear, fifthteenyear)'.
          'VALUES ( "$fundname", "$starrating",'.
          '"$firstyear", "$thirdyear", "$fifthyear",'.
          '"$tenthyear", "$fifthteenyear")';

Now you know wat to do

Thats ofcourse not all but still a little or else you had come up with this error later

[AndyB]

@RockingGroudon: I'm assuming the insert query was changed to match my suggestion.

To continue, I think we need to see a full error and query message. Change the relevant part of your insert code to this:

[code]$insertfundquery = " ... whatever you're using";
$result = mysql_query($insertfundquery) or die("Error: ". mysql_error(). " with query ". $insertquery);[/code]

[Chetan]

@Andyb - Sorry but I still dont see anywhere anybody giving a code which tells him not to use '' while putting vars in it, not is the use of . to do same here?

[BrotherLogic]

Error: Data truncated for column 'firstyear' at row 1 with query INSERT INTO longterm( fundname,starrating, firstyear, thirdyear,fifthyear, tenthyear, fifthteenyear)VALUES ( "$fundname", "$starrating","$firstyear", "$thirdyear", "$fifthyear","$tenthyear", "$fifthteenyear")


Error: Data truncated for column 'firstyear' at row 1 with query INSERT INTO longterm( fid, fundname, starrating, firstyear, thirdyear, fifthyear, tenthyear, fifthteenyear) VALUES ("0", "$fundname", "$starrating","$firstyear", "$thirdyear", "$fifthyear","$tenthyear","$fifthteenyear")

[Chetan]

See the $ are not considered as variables as i said it should be changed

[BrotherLogic]

@RG -- how should it be arranged? I'm lost, I'm a complete newbyyy.

Thanks for your help guys.

[AndyB]

Ah yes " and ' and all that stuff.  Try this insert query (which I ought to have spotted some time back) that avoids the literals RockingGroudon observed.

[code]$insertfundquery = "INSERT INTO longterm( fid, fundname, starrating, firstyear, thirdyear, fifthyear, tenthyear, fifthteenyear)
VALUES (' ', '$fundname', '$starrating', '$firstyear', '$thirdyear',$fifthyear','$tenthyear','$fifthteenyear')";[/code]

[Chetan]

Yup! ~~ forgot my words

[BrotherLogic]

Oh yeah. You guys rock. Totally my bad... I was getting syntax errors when I took the quotes out because I had the code in the wrong place in my script from testing.


Also...

I get this error:

Error: Out of range value adjusted for column 'fid' at row 1 with query INSERT INTO longterm( fid, fundname, starrating, firstyear, thirdyear, fifthyear, tenthyear, fifthteenyear) VALUES ('', Exposed Fund, '5Star', '7.04', '11.65','7.82','0','0')
________________

but when I put a zero in for the "fid" VALUE everything works fine.


Thanks a Bunch Guys.
I really appreciate your help and effort.

Jon

[Chetan]

You made it with NOT NULL remember, look at the code on the first page, it says fip is NOT NULL

[fenway]

Well, your variables aren't interpolating.... I'm assuming that:

[code]'VALUES ( "$fundname", "$starrating",'.[/code]

should be

[code]'VALUES ( "'.$fundname.'", "'.$starrating.'",'.[/code]

If PHP is like Perl, nothing in single quotes will be ever be interpolated.

[Chetan]

Well that was done before~~, fenway, thats what I hav been saying and AndyB corrected it.
And did you think you had it NOT NULL as I said

[fenway]

Sorry, I must have missed the second page of posts. ;-)

As for NOT NULL, assuming that fid is your PK column, don't even specify it in the column list, and you'll be fine.