Problem Inserting Into More Than "X" Number of Columns 1

[tyhand]

Hey all,

Hoping someone could help me out with this weird MySQL problem.

I have a Table with 30 Columns/Fields. Whenever I try to run a query to insert data into it, nothing gets inserted. Even worse is that NO Errors are being generated.

Originally, 13 of the Columns/Fields were of type TEXT; and 17 were of type INT(11). I suspected that maybe the column TYPES were causing an issue so I ran a test and only attempted to insert data into only a few columns at a time.

To my surprise, I was only able to insert data into only 5 columns/fields. Inserting into anything more than 5 would not work.

I then changed all the TEXT columns to VARCHAR() Type and ran another test.

In this test, I am able to insert data into only 14 columns/fields. Inserting into more than 15 doesn't work.

Again, no error is being generated. In fact, when checking my queries with IF-Else control, I get a "query successful" each time!

By the way, I've tested this for hours. The mysql/php syntax is correct. My code is not missing a comma, or quote, or anything else.

Anyone else encounter this? Is there some sort of limitation MySQL has when inserting into more than "x" number of columns/fields?

As I mentioned, I suspected this may be being caused by my declaration of column types. Can someone confirm that this may be the cause?

All comments are welcome.

Thanks in advance for the help.

Best!
- Tyhand

 

[towerbase]

What is the smallest PHP script that you can write that reproduces the error that you are experiencing?

Presumably it would consist of a CREATE TABLE statement followed by an INSERT statement.

Can you show us these two statements that exhibit the problem?


It would also be useful to know which Operating System and versions of PHP and MySQL you are using. And which TYPE you are using InnoDB, MyISAM etc.

Andrew
Hampshire, UK

 

[tyhand]

Hey Andrew,

I'm using MySQL 5.0.91-community, PHP 5.2.16 with Apache 2.2.17

Now, to be sure, the tables are successfully created upon my script's installation.

The problem I'm having is with INSERTING data into one particular table that holds the script's settings.

Thus, my PHP mySQL code follows this convention:

$settings = "INSERT INTO settings (ID, setting1, setting2, setting3, etc... all the way to the 30th field/column) VALUES (NULL,'$variable1', '$variable2', $variable3, etc... all the way to the 30th variable)";

mysql_query($settings); // run query, add to db

if ($settingsl) {
echo ("<p>Settings Successfully Uploaded</p>");
} else {
echo ("<p>Settings cannot be set at this time!</p>");
}

Again, I'm not getting any errors either. But, the data is still NOT being inserted.

This isn't my first tango with PHP/MySQL; but it's my biggest project thus far. I'm just not sure why I can't insert data into only less than 15 fields.

Thanks for the help.

- tyhand

 

[r937]

could you please do a SHOW CREATE TABLE for your table

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tyhand]

Hey all,

Here's the complete CREATE TABLE CODE:

(NOTE: The table always gets created successfully every time I install my script)


$sql = "CREATE TABLE IF NOT EXISTS locker_settings (
ID INT(5) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
set_skin VARCHAR(25) COLLATE ascii_general_ci NOT NULL DEFAULT 'not set',
SKID INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_image_url VARCHAR(255) COLLATE ascii_general_ci NOT NULL DEFAULT 'not set',
set_bgcolor INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_height INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_width INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_bdrcolor INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_bdrthickness INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_opacity INT(5) UNSIGNED NOT NULL DEFAULT 0,
FID INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_h_text VARCHAR(255) COLLATE ascii_general_ci NOT NULL DEFAULT 'not set',
set_h_text_size INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_h_color INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_ins_text VARCHAR(255) COLLATE ascii_general_ci NOT NULL DEFAULT 'not set',
set_ins_text_size INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_ins_color VARCHAR(10) COLLATE ascii_general_ci NOT NULL DEFAULT 'not set',
set_link_text_size INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_link_color INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_access_time INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_tease_time INT(5) UNSIGNED NOT NULL DEFAULT 0,
set_scrolling VARCHAR(25) COLLATE ascii_general_ci NOT NULL DEFAULT 'false',
set_disable_for_countries VARCHAR(25) COLLATE ascii_general_ci NOT NULL DEFAULT 'true',
set_nodisable_text VARCHAR(255) COLLATE ascii_general_ci NOT NULL DEFAULT 'not set',
set_close_button VARCHAR(25) COLLATE ascii_general_ci NOT NULL DEFAULT 'false',
set_retainer VARCHAR(25) COLLATE ascii_general_ci NOT NULL DEFAULT 'false',
set_retainer_text VARCHAR(255) COLLATE ascii_general_ci NOT NULL DEFAULT 'not set',
set_unlock_notify VARCHAR(25) COLLATE ascii_general_ci NOT NULL DEFAULT 'false',
set_unlock_notify_text VARCHAR(255) COLLATE ascii_general_ci NOT NULL DEFAULT 'not set',
LID INT(5) UNSIGNED NOT NULL)";

if (@mysql_query($sql)) {
echo ("SETTINGS TABLE successfully created!");
} else {
die("Encountered error creating SETTINGS TABLE:" . mysql_error() . "</p>");
}

Thanks for the help.

- T Y H A N D

 

[r937]

must be something you're doing wrong in your php code

i took your CREATE TABLE statement, installed it on my system, and then ran the following queries --

INSERT INTO locker_settings
VALUES ( null
,'x',1,'x',1,1,1,1,1,1,1,'x',1,1,'x',1,'x'
,1,1,1,1,'x','x','x','x','x','x','x','x',2 )
;
INSERT INTO locker_settings 
(LID)
VALUES ( 4 )
;

both INSERTs worked fine

in particular, all of the values in the first statement were inserted as expected

so it's not mysql, it's something else your code is doing


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tyhand]

Hey R937,

Sigh... I'll look over my code again. I'm thinking there may be a clash with one of the variable's values and the column type.

Thanks again for the help.

Regards,

- Tyhand

 

[ingresman]

Your code says

INSERT INTO settings (ID, setting1, setting2, setting3

but setting1 (for example) is not a column in your table, or have I missed something?

 

[vacunita]

I'm with Rudy. Try outputting your query after all values have been set and then running it on a mysql GUI to check for any errors

Also just for debugging purposes don't suppress errors with the @, and make sure your PHP.ini is set to display all errors and notices.

display_errors=E_ALL;


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[tyhand]

Hey R937,

You were write. Something was off with my code.

I'm using AJAX and submitting variables for PHP to process. My ajax and javascript codes were returning Empty and Undefined variables and that was what was causing my the issue.

Thank again for the help.

Best!

 

[DonQuichote]

If you have done so much testing, the SQL will probably correct. One way to check it is to switch on the query log.

Does it help to follow the INSERT statement with a "SHOW WARNINGS" statement? I mean, does that give a meaningful message?

But if the extra field is a problem, I would say that there is a buffer size set too small somewhere. So check your my.ini settings. You can see the the sizes also by issuing a "SHOW VARIABLES" statement.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)