Problems inserting data into a database

[eMonk]

I have a mysql table with about 20 columns. The 2 giving me problems are:

 

age tinyint(2) unsigned,

weight tinyint(3) unsigned,

 

For some reason these 2 fields have to be entered in when submitting from a HTML form or I get the following error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '', '', '', '', '', , '', ................. at line 20

 

I tried changing these 2 fields to varchar like the rest of the columns but phpmyadmin is giving me an error. Can anyone shed some light on this? I would like the user to be able to leave these 2 fields blank if they want to.

[Maq]

You should only include those columns in your query if the user fills them out.  In your table definition you can specify a default for each column.

[ManiacDan]

You can't just have an empty field.  Look at your syntax in the error message, see how you have two commas in a row?  That's illegal in SQL.  You have to explicitly make it NULL or 0.

[eMonk]

Good idea Maq, I could just run a separate query when the user fills these 2 fields out but there must be a way to do this in 1 query?

 

All columns in the table are NULL by default.

 

BTW ManiacDan: I can leave all the fields blank just not these 2 for some reason.

[ManiacDan]

Good idea Maq, I could just run a separate query when the user fills these 2 fields out but there must be a way to do this in 1 query?

 

All columns in the table are NULL by default.

 

BTW ManiacDan: I can leave all the fields blank just not these 2 for some reason.

Because those fields are numbers, and if you leave a number blank you end up with an empty field just like the one in the error I've already pointed out.

 

Hello!  Look!

 

'' <-- something

  <-- nothing

 

You can't have nothing between two commas.

 

For your string fields, what you're thinking of as "nothing" is actually the string ''

 

For your numeric fields, "nothing" is actually nothing and that's illegal in SQL

 

Make it something.

[eMonk]

Oh I see, thanks for clearing that up ManiacDan!

 

I tried changing these 2 tinyint fields into varchar but get the following error in phpmyadmin:

 

Error

SQL query:

 

ALTER TABLE  `model` CHANGE  `weight`  `weight` VARCHAR( 3 ) UNSIGNED NULL DEFAULT NULL

 

MySQL said:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNSIGNED NULL DEFAULT NULL' at line 1

 

Why is there a syntax error when I'm using phpmyadmin. See anything wrong with this sql syntax?

[ManiacDan]

Don't change a number to a string on phpmyadmin, fix the bug in your PHP code.  If the field isn't submitted, put a zero there.  you're done, no more work required.

[fenway]

That means you have parallel arrays -- that's what a hash is for.

[ManiacDan]

The error from your PHPMyAdmin change comes from the fact that you're trying to make an unsigned varchar, which is an impossible datatype.

 

-Dan

[eMonk]

Thanks ManiacDan that worked but it wasn't the source of the problem.

 

In my query I didn't have '' around the $age and $weight variable because I thought number values didn't need them but it makes a syntax error if they are left blank.

[ManiacDan]

Yes, I know that.  That's why I told you that three times.  Are you having a problem with this sentence? 

 

IF THEY ARE NOT SUBMITTED, MAKE THEM A ZERO (0) IN YOUR QUERY. 

[eMonk]

I understand that and it was what I was doing.

 

$query = "INSERT INTO model VALUES ($age, $weight)";

 

Should have been:

 

$query = "INSERT INTO model VALUES ('$age', '$weight')";

[ManiacDan]

NOT EVEN CLOSE

 

But whatever, if the error goes away you can be happy.