Jump to content

Archived

This topic is now archived and is closed to further replies.

BrotherLogic

Data truncated for column.......

Recommended Posts

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

Share this post


Link to post
Share on other sites
Please post the MySQL query that produces this error.

Share this post


Link to post
Share on other sites
  $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")';

Share this post


Link to post
Share on other sites
What is the value for $firstyear that you're trying to insert? It obviously doesn't match the column spec.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[code]$insertfundquery = 'INSERT INTO longterm( fid, fundname, starrating, firstyear, thirdyear, fifthyear, tenthyear, fifthteenyear)
VALUES ("", "$fundname", "$starrating","$firstyear", "$thirdyear", "$fifthyear","$tenthyear","$fifthteenyear")';[/code]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
@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]

Share this post


Link to post
Share on other sites
@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?

Share this post


Link to post
Share on other sites
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")

Share this post


Link to post
Share on other sites
See the $ are not considered as variables as i said it should be changed

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
You made it with NOT NULL remember, look at the code on the first page, it says fip is NOT NULL

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.