eagle1771 Posted November 1, 2007 Share Posted November 1, 2007 Greetings All, I am new to the forum and new to mySql and Php. I am not sure if this is a problem related to mySql, Php or maybe even the editor that I am using. Editor: NoteTabPro version 5.5 Windows Vista Ultimate (new Toshiba laptop with a flaky spacebar) mySql 4.1.19 PHP Version 4.4.6 mySql statement: INSERT INTO `wchs_users` Error: Column Count Doesn't Match Value Count At Row 1 This may be more than you wanted but here's the table info: -- phpMyAdmin SQL Dump -- version 2.6.0-pl3 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Nov 01, 2007 at 06:02 PM -- Server version: 4.1.19 -- PHP Version: 4.4.6 -- -- Database: `wchs1969_com` -- -- -------------------------------------------------------- -- -- Table structure for table `wchs_users` -- CREATE TABLE `wchs_users` ( `ID` bigint(100) NOT NULL auto_increment, `username` varchar(100) NOT NULL default '', `prun` char(1) NOT NULL default 'r', `password` varchar(20) NOT NULL default '', `firstname` varchar(35) NOT NULL default '', `prfn` char(1) NOT NULL default 'r', `lastname` varchar(35) NOT NULL default '', `prln` char(1) NOT NULL default 'r', `middlename` varchar(35) NOT NULL default '', `prmin` char(1) NOT NULL default 'r', `maidenname` varchar(35) NOT NULL default '', `prman` char(1) NOT NULL default 'r', `streetaddress1` varchar(35) NOT NULL default '', `prsa1` char(1) NOT NULL default 'r', `streetaddress2` varchar(35) NOT NULL default '', `prsa2` char(1) NOT NULL default 'r', `streetaddress3` varchar(35) NOT NULL default '', `prsa3` char(1) NOT NULL default 'r', `city` varchar(30) NOT NULL default '', `prcty` char(1) NOT NULL default 'r', `state` char(2) NOT NULL default '', `prst` char(1) NOT NULL default 'r', `country` varchar(35) NOT NULL default '', `prctr` char(1) NOT NULL default 'r', `zippostalcode` varchar(11) NOT NULL default '', `przpc` char(1) NOT NULL default 'r', `prefcontact` char(1) NOT NULL default '', `prpfc` char(1) NOT NULL default 'r', `homephone` varchar(14) NOT NULL default '', `prph` char(1) NOT NULL default 'r', `mobilephone` varchar(14) NOT NULL default '', `prpm` char(1) NOT NULL default 'r', `businessphone` varchar(14) NOT NULL default '', `prpb` char(1) NOT NULL default 'r', `email` varchar(100) NOT NULL default '', `prem` char(1) NOT NULL default 'r', `imc` char(1) NOT NULL default '', `primc` char(1) NOT NULL default 'r', `dateofbirth` varchar( NOT NULL default '', `prdob` char(1) NOT NULL default 'r', `lastvisitdate` varchar( NOT NULL default '', `statusmpg` char(1) NOT NULL default '', `validkey` varchar(255) NOT NULL default '', `validated` char(1) NOT NULL default '0', `PIN` varchar(255) NOT NULL default '0', `lastip` varchar(255) NOT NULL default '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `wchs_users` -- INSERT INTO `wchs_users` VALUES (4, '', 'r', '', 'John', 'p', '', 'p', 'James', 'r', 'Jensen', 'p', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', '', '', '0', '0', '0'); INSERT INTO `wchs_users` VALUES (2, '', 'r', '', 'Kenneth', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', '', '', '0', '0', '0'); INSERT INTO `wchs_users` VALUES (5, '', 'r', '', '', 'r', '', 'p', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', 'r', '', '', '', '0', '0', '0'); The form we are using can be seen here: http://www.wchs1969.com/memberreg.html I am simply trying to get the php script to write any supplied form data to the appropriate fields in the table. I have "stripped down" the script to where there were only two form variables, namely firstname and lastname and the firstname was written to the database, but the lastname data was never inserted. Since I am so new to both Php and mySql, it is very possible that I am overlooking some very basic requirement(s) of one or the other. Script available upon request. Thank you for taking a look. (I have searched the forum for INSERT issues and am reading over some of what has been posted.) Ralph Moore Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/ Share on other sites More sharing options...
pocobueno1388 Posted November 1, 2007 Share Posted November 1, 2007 We are going to have to see your code Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383097 Share on other sites More sharing options...
Barand Posted November 1, 2007 Share Posted November 1, 2007 If you look at the INSERT statements in your dump they provide a value for every column in the table in the correct order. If you don't do it that way you have to specify the particular columns that you are providing values for. The columns you don't specify will be set to their specified default or NULL if no default. eg INSERT INTO `wchs_users` (username, password, firstname, lastname) VALUES ('$user', '$pwd', '$fname', '$lname') if the number of column specified and the number of values provided do not match, you get that error Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383113 Share on other sites More sharing options...
eagle1771 Posted November 1, 2007 Author Share Posted November 1, 2007 OK, here's the Php code attached as a .txt file. [attachment deleted by admin] Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383119 Share on other sites More sharing options...
Barand Posted November 1, 2007 Share Posted November 1, 2007 You want us to count them for you, then? Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383122 Share on other sites More sharing options...
pocobueno1388 Posted November 1, 2007 Share Posted November 1, 2007 You have 45 fields listed here (ID, username, prun, password, firstname, prfn, lastname, prln, middlename, prmin, maidenname, prman, streetaddress1, prsa1, streetaddress2, prsa2, streetaddress3, prsa3, city, prcty, state, prst, country, prctr, zippostalcode, przpc, prefcontact, prpfc, homephone, prph, mobilephone, prpm, businessphone, prpb, email, prem, imc, primc, dateofbirth, prdob, lastvisitdate, statusmpg, validkey, validated, PIN, lastip) And you have 46 values listed here ('', '$username', '$prun', '', '$firstname', '$prfn', '$lastname', '$prln', '$middlename', '$prmin', '$maidenname', '$prman', '$streetaddress1', '$prsa1', '$streetaddress2', '$prsa2', '$streetaddress3', '$prsa3', '$city', '$prcty', '$state' '$prst', '$country', '$prctr', '$zippostalcode', '$przpc', '$prefcontact', '$prpfc', '$homephone', '$prph', '$mobilephone', '$prpm', '$businessphone' '$prpb', '$email', '$prem', '$imc', '$primc', '$dateofbirth', '$prdob', '', '', '', '', '', '') The numbers need to match. Maybe you put one too many null values to the end of the 2nd list? edit - Hah Barand, I sadly gave in and already counted >.> Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383123 Share on other sites More sharing options...
Barand Posted November 1, 2007 Share Posted November 1, 2007 According to dump in first post there should be 46 columns/values Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383126 Share on other sites More sharing options...
pocobueno1388 Posted November 1, 2007 Share Posted November 1, 2007 Your right, I counted wrong. They do have 46 fields and 46 values...so they match. I'm not going to promise that I counted right the second time either though Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383128 Share on other sites More sharing options...
eagle1771 Posted November 2, 2007 Author Share Posted November 2, 2007 Trust me guys, I've counted these things many, many times. The thing is, even when I strip the file down to just firstname and lastname fields, and fill in the corresponding fields in the form, only the firstname field gets written to the table. That's why I wondered about hidden embedded characters or something. Any other ideas? Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383137 Share on other sites More sharing options...
Barand Posted November 2, 2007 Share Posted November 2, 2007 You have 2 commas missing between values ...,'$state' '$prst', ... ..., '$businessphone' '$prpb', ... Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383138 Share on other sites More sharing options...
Barand Posted November 2, 2007 Share Posted November 2, 2007 PS, The first value should be NULL, not '' ie VALUES (null, '$username', ... Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383142 Share on other sites More sharing options...
eagle1771 Posted November 2, 2007 Author Share Posted November 2, 2007 Barand, I must have looked this thing over 30 to 40 times looking for errors like the two missing commas. You have good eyes and I think I am losing my mind. Now, about the NULL vs '' - is that because it is a number field, or should all of my empty fields default to NULL or NOT NULL? Thank you, and I am off to test this. Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383154 Share on other sites More sharing options...
Barand Posted November 2, 2007 Share Posted November 2, 2007 NULL because it's an auto_inc field and you want it generate the next value automatically Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383158 Share on other sites More sharing options...
eagle1771 Posted November 2, 2007 Author Share Posted November 2, 2007 OK, this weird. First of all, Thank You as I am making major progress. I just tested the script and form after adding in the two commas and everything is now being written to the table, with the exception of the lastname field. Is that strange, or what? I wonder if mySql has some internal coding regarding the order of name fields, as they are probably so commonly used? I will try re-arranging the order to firstname, middlename lastname, but I can't imagine that really being the issue here. Ralph Moore Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383171 Share on other sites More sharing options...
Barand Posted November 2, 2007 Share Posted November 2, 2007 After inserting you print the data in a table. Does the lastname appear OK in that html table? Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383191 Share on other sites More sharing options...
eagle1771 Posted November 2, 2007 Author Share Posted November 2, 2007 No. It does not print at all. But everything else does. What I have done in the las t20 minutes or so. I dropped the the lastname and prln (Public / Restricted Last Name) fields and then re-inserted them after the middlename, prmin, maidenname, prman fields. Also changed the order in the script and the html, uploaded all and retested with the same results. Also, I set all of the rest of the empty fields to NOT NULL in the script (separate testing) but nothing seems to make any difference. As I said, it's weird. Ralph Moore Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383207 Share on other sites More sharing options...
Barand Posted November 2, 2007 Share Posted November 2, 2007 Check your input form. Is the field name correct there? Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383210 Share on other sites More sharing options...
eagle1771 Posted November 2, 2007 Author Share Posted November 2, 2007 Doh! I Must have looked at that field name ten times (at least) and even when I read it this time, I almost missed the extra "t". I had lasttname there. You are definitely "the man"! Thank You! I REALLY appreciate the help. I will mark this as solved. Raph Moore Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383228 Share on other sites More sharing options...
Barand Posted November 2, 2007 Share Posted November 2, 2007 Thank God for that. It's 2:15am here - I can get to bed now Quote Link to comment https://forums.phpfreaks.com/topic/75703-solved-insert-not-inserting-all-field-data-column-count-error/#findComment-383234 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.