[Resolved] mysql error 1136 know why.. but how to work around it...

[mig_soares]

Hello folks,

1st I'm a newbie with around 6 months of tinkering with a php script (link directory) and mysql.

I'll summarize this as much as I can:

The script is a link directory (Cncat.Free by cn-software.com) it's free I've asked if I could pay for support but had no answer..

In its original form the script has a certain number of fields (which are for informaton to be entered) as I needed more of them I had the not so bright idea of just entering extra fields in the config file

To these:
$cat["resfield1"]="1";
$cat["resfield2"]="2";
$cat["resfield3"]="3";

I just added

$cat["resfield4"]="4";
$cat["resfield5"]="5";
$cat["resfield6"]="6";
$cat["resfield7"]="7";
$cat["resfield8"]="8";
$cat["resfield9"]="9";
$cat["resfield10"]="10";

I also made relevant changes to the add.php file and edit.php which called the above mentioned resfields...

Then I got the mysql dump and added them there too like so:

CREATE TABLE _cncatmain (
lid int(11) NOT NULL auto_increment,
title text,
description text,
url text,
cat1 int(11) default NULL,
gin int(11) default NULL,
gout int(11) default NULL,
moder_vote int(11) default NULL,
email text,
type int(11) default NULL,
broken int(11) default '0',
insert_date datetime default NULL,
resfield1 text,
resfield2 text,
resfield3 text,
resfield4 text,
resfield5 text,
resfield6 text,
resfield7 text,
resfield8 text,
resfield9 text,
resfield10 text,

PRIMARY KEY (lid)
) TYPE=MyISAM AUTO_INCREMENT=4152 ;

And the relevant data:

INSERT INTO _cncatmain (lid, title, description, url, cat1, gin, gout, moder_vote, email, type, broken, insert_date, resfield1, resfield2, resfield3, resfield4, resfield5, resfield6, resfield7, resfield8, resfield9, resfield10) VALUES (1, 'yjhhg', 'ghfs fgsfdg ssdçlkasçldkas', 'http://bbc.co.uk', 1, 1, 3, 1, '[e-mail]', 2, 1, NULL, NULL, NULL, NULL);

It gave me the error mentioned in the subject line I now understand why... I'm telling it it has "x" arguments(?) and in reality it's finding "y"

The original _cncatman entries look like this:

INSERT INTO _cncatmain (lid, title, description, url, cat1, gin, gout, moder_vote, email, type, broken, insert_date, resfield1, resfield2, resfield3) VALUES (1, 'yjhhg', 'ghfs fgsfdg ssdçlkasçldkas', 'http://bbc.co.uk', 1, 1, 3, 1, '[e-mail]', 2, 1, NULL, NULL, NULL, NULL);

I have close to 4000 sites entered with a lot of hard work so I was wondering is there some way of adding the fields i and maintaining my existing database. The ideal scenario would be that the program assumed the old entries were ok and just kept the new fields in future links inserted as such on editing a link the fields would be empty in the data I now have and in future entries the new fields would be present.

I hope I can get some help in figuring this one out 🙂

Thanks very much for reading!

Miguel.:rolleyes:

[jayant]

Look it again closely
INSERT INTO _cncatmain (lid, title, description, url, cat1, gin, gout, moder_vote, email, type, broken, insert_date, resfield1, resfield2, resfield3, resfield4, resfield5, resfield6, resfield7, resfield8, resfield9, resfield10) VALUES (1, 'yjhhg', 'ghfs fgsfdg ssdçlkasçldkas', 'http://bbc.co.uk', 1, 1, 3, 1, '[e-mail]', 2, 1, NULL, NULL, NULL, NULL);

read, how many attributes are there in first () parenthesis
and in the second set following the VALUES

you have added attributes but not their values

INSERT INTO _cncatmain (lid, title, description, url, cat1, gin, gout, moder_vote, email, type, broken, insert_date, resfield1, resfield2, resfield3, resfield4, resfield5, resfield6, resfield7, resfield8, resfield9, resfield10) VALUES (1, 'yjhhg', 'ghfs fgsfdg ssdçlkasçldkas', 'http://bbc.co.uk', 1, 1, 3, 1, '[e-mail]', 2, 1, NULL, NULL, NULL, NULL, 'value for resfield4', 'value for resfield5', 'value for resfield6', 'value for resfield7', 'value for resfield8', 'value for resfield9', 'value for resfield10' );

got it?

[mig_soares]

Hello,

Originally posted by jayant


you have added attributes but not their values
(...)
got it?

Yes, I understood the problem I mentioned that fact in the original text.

My question was is there a work around what I mean is say I leave the present data (around 4000) with the original format; is there a way I can maintain those and make the new insertions have the new "attributes".

My way of thinking (I'm not a programmer) the simple way to "fix" this would be:

take a search and replace tool and look for all instances of NULL, NULL, NULL, NULL and substitute it with the number os NULL's needed to match the attributes. Does that sound like an ok way to fix my problem? I dont mind if the existing data keeps has null value.

[jayant]

Why not simply use ALTER TABLE and append new fields with default as NULL.
so the fields also get appended to old records as well and will have NULL values .
wot say?

[mig_soares]

I'd say its great 🙂 but how? If there is a FAQ or something of the sort I could refer too because I am really in the dark about this and would it fix my prob?

I'm assuming what you say is to insert the new ones like this:

Instead of

resfield3 text,

use:

resfield4 text default NULL,

Is this a correct assumption?

But then if the data is already there in another format I'm guessing mysql would give me the same error

[mig_soares]

I've just been reading up, would this do the trick in your opinion:

ALTER TABLE _cncatmain ADD COLUMN resfield4 text default NULL

Does the syntax look right? Could this bee done in phpmyadmin

[jayant]

yes can be done with PhpMyAdmin (PMA)
you just need to tell PMA how many fields are to be inserted. it will then ask their names, types, default value etc.
and will do the job perfectly.

[mig_soares]

I went into PMA and think i filled in the things properly gave the coloumns names default value null and chose the fulltext box I now need to go through all the php to check for references to the original 3 coloumns and insert the new ones.

I'm gonna mark this as resolved.

Many thanks for the help.

Miguel