Jump to content


Photo

Data truncated for column.......


  • Please log in to reply
23 replies to this topic

#1 BrotherLogic

BrotherLogic
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 14 August 2006 - 03:22 AM

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

#2 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 14 August 2006 - 03:34 AM

Please post the MySQL query that produces this error.
Legend has it that reading the manual never killed anyone.
My site

#3 BrotherLogic

BrotherLogic
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 14 August 2006 - 03:45 AM

  $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")';


#4 BrotherLogic

BrotherLogic
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 14 August 2006 - 03:47 AM

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


#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 August 2006 - 05:23 AM

What is the value for $firstyear that you're trying to insert? It obviously doesn't match the column spec.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 BrotherLogic

BrotherLogic
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 14 August 2006 - 06:04 AM

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.

#7 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 14 August 2006 - 11:56 AM

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

Legend has it that reading the manual never killed anyone.
My site

#8 BrotherLogic

BrotherLogic
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 14 August 2006 - 04:32 PM

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.

#9 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 14 August 2006 - 04:46 PM

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.
Legend has it that reading the manual never killed anyone.
My site

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 August 2006 - 04:52 PM

Perhaps... not sure what the default would be with no (M,D)... post the SHOW CREATE output and you'll see for certain.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#11 BrotherLogic

BrotherLogic
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 14 August 2006 - 05:01 PM

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



#12 Chetan

Chetan
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts
  • LocationIndia

Posted 14 August 2006 - 05:02 PM

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
I am a PHP Guru, ask me questions if you want to

#13 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 14 August 2006 - 05:07 PM

@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:

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

Legend has it that reading the manual never killed anyone.
My site

#14 Chetan

Chetan
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts
  • LocationIndia

Posted 14 August 2006 - 05:18 PM

@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?
I am a PHP Guru, ask me questions if you want to

#15 BrotherLogic

BrotherLogic
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 14 August 2006 - 05:22 PM

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")

#16 Chetan

Chetan
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts
  • LocationIndia

Posted 14 August 2006 - 05:24 PM

See the $ are not considered as variables as i said it should be changed
I am a PHP Guru, ask me questions if you want to

#17 BrotherLogic

BrotherLogic
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 14 August 2006 - 05:37 PM

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

Thanks for your help guys.


#18 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 14 August 2006 - 05:40 PM

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.

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

Legend has it that reading the manual never killed anyone.
My site

#19 Chetan

Chetan
  • Members
  • PipPipPip
  • Advanced Member
  • 162 posts
  • LocationIndia

Posted 14 August 2006 - 05:45 PM

Yup! ~~ forgot my words
I am a PHP Guru, ask me questions if you want to

#20 BrotherLogic

BrotherLogic
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 14 August 2006 - 05:58 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users