How do I determine the auto increment value

Rob (SA) · #1 08-23-2009, 07:57 AM

Hi Folks,

I am trying to load a table into MySql by uploading a text file.

Example:

#
# Table structure for table `strokeaverages`
#
CREATE TABLE `strokeaverage` (
`ID` int(5) NOT NULL auto_increment,
`no` varchar(10) NOT NULL default '',
`gender` varchar(1) NOT NULL default '',
`surname` varchar(25) NOT NULL default '',
`name` varchar(25) NOT NULL default ",
`concat` varchar(50) NOT NULL default ",
`2009` varchar(4) NOT NULL default '',
`age` varchar(4) NOT NULL default '',
`gnsa16` varchar(15) NOT NULL default '',
`d11` varchar(3) NOT NULL default '',
`d12` varchar(3) NOT NULL default '',
`d21` varchar(3) NOT NULL default '',
`d22` varchar(3) NOT NULL default '',
`d31` varchar(3) NOT NULL default '',
`d32` varchar(3) NOT NULL default '',
`d41` varchar(3) NOT NULL default '',
`d42` varchar(3) NOT NULL default '',
`d51` varchar(3) NOT NULL default '',
`d52` varchar(3) NOT NULL default '',
`d61` varchar(3) NOT NULL default '',
`d62` varchar(3) NOT NULL default '',
`d71` varchar(3) NOT NULL default '',
`d72` varchar(3) NOT NULL default '',
`d81` varchar(3) NOT NULL default '',
`d82` varchar(3) NOT NULL default '',
`d91` varchar(3) NOT NULL default '',
`d92` varchar(3) NOT NULL default '',
`d101` varchar(3) NOT NULL default '',
`d102` varchar(3) NOT NULL default '',
`d111` varchar(3) NOT NULL default '',
`d112` varchar(3) NOT NULL default '',
`d121` varchar(3) NOT NULL default '',
`d122` varchar(3) NOT NULL default '',
`d131` varchar(3) NOT NULL default '',
`d132` varchar(3) NOT NULL default '',
`rounds` varchar(15) NOT NULL default '',
`avglowest` varchar(15) NOT NULL default '',
`avgall` varchar(15) NOT NULL default '',
`n11` varchar(3) NOT NULL default '',
`n12` varchar(3) NOT NULL default '',
`n21` varchar(3) NOT NULL default '',
`n22` varchar(3) NOT NULL default '',
`n23` varchar(3) NOT NULL default '',
`n31` varchar(3) NOT NULL default '',
`n32` varchar(3) NOT NULL default '',
`n41` varchar(3) NOT NULL default '',
`n42` varchar(3) NOT NULL default '',
`n51` varchar(3) NOT NULL default '',
`n52` varchar(3) NOT NULL default '',
`n61` varchar(3) NOT NULL default '',
`n62` varchar(3) NOT NULL default '',
`sa1801` varchar(3) NOT NULL default '',
`sa1802` varchar(3) NOT NULL default '',
`sa1803` varchar(3) NOT NULL default '',
`sa1804` varchar(3) NOT NULL default '',
`sa1601` varchar(3) NOT NULL default '',
`sa1602` varchar(3) NOT NULL default '',
`sa1603` varchar(3) NOT NULL default '',
`sa1604` varchar(3) NOT NULL default '',
`sa1401` varchar(3) NOT NULL default '',
`sa1402` varchar(3) NOT NULL default '',
`sa1403` varchar(3) NOT NULL default '',
`sa1404` varchar(3) NOT NULL default '',
`sa1201` varchar(3) NOT NULL default '',
`sa1202` varchar(3) NOT NULL default '',
`sa1203` varchar(3) NOT NULL default '',
`sa1204` varchar(3) NOT NULL default '',
`gn01` varchar(3) NOT NULL default '',
`gn02` varchar(3) NOT NULL default '',
`gn03` varchar(3) NOT NULL default '',
`gn04` varchar(3) NOT NULL default '',
PRIMARY KEY (`ID`)
) TYPE=MyISAM COMMENT='Game Organisation' AUTO_INCREMENT=22 ;
# --------------------------------------------------------

I have the following questions:

How is the auto increment value determined?
What exactly does this line do in laymans terms for the MySql to upload and be succesful?

I am sure there is a whiz kid amoungst you that can help.

navaldesign · #2 08-23-2009, 12:16 PM

The autoincrement should have no argument
TYPE=MyISAM COMMENT='Game Organisation' AUTO_INCREMENT ;

meaning that each new record takes the next number in the table.

Rob (SA) · #3 08-23-2009, 12:26 PM

Hi George,

Thanks for the reply.

I hope my understanding is correct and that the auto_increment = . . . does not need to be stated.

If it is not stated does it know when to stop?

navaldesign · #4 08-23-2009, 12:34 PM

Rob, i don't know whatyour understanding of MYSQl is.

The AUTOINCREMENT statement simply says MySQL that whenever a new record is added, the column ID (in your case) should assume the first available value
So if you have already inserted in example 20 records (which means 20 players) , in the next record (next player) the ID column will automatically have the value 21.
As simple as that.

So i don't really understand your question "How does it know when to stop"

Rob (SA) · #5 08-23-2009, 12:42 PM

Hi George,

My knowledge here is extremely limited.

Trail and error as usaul.

I was having the understanding that should there be a statement like

INSERT INTO 'strokeaverage' VALUES (1,d11,25)
INSERT INTO 'strokeaverage' VALUES (2,d12,26)
INSERT INTO 'strokeaverage' VALUES (3,d21,27)

etc etc

the result would be that in conjunction with these files (3 off) it would have a autoincrement of 4 to now when to stop asking for new values.

I am trying to load this info as a table to my database so maybe that info can helpyou understand where I am at

navaldesign · #6 08-23-2009, 12:49 PM

An "insert" statement must specify both column names and values, unless the values passed equals the number of columns. So you above insert statements are invalid since you are passing only three values in a table that has 50 + columns.
How would MySQL know in which column it will need to store each value ?

And, you are not saying, is these values (in your last post) intended to be stored in the same row (player) or in different rows ?

I'm sorry but i just can't understand what you are trying to do.

Rob (SA) · #7 08-23-2009, 12:55 PM

Hi George,

I have a table that i have been using in excel and that table generates stroke averages "averages" for scores that golfers have played.

I want to create interactivity on my website that if I add a score then it will go to the table and then should I need to see the average it will appear on a prepared page.

The info shown below is the field name that they are different.

If there is an easier way the I would appreciate to now - as you can see it is going to take me time in the manner I am currently moving.

I would be more than glad to send my excel apreadsheet of that would assit you.

Rob (SA) · #8 08-23-2009, 01:00 PM

Hi George,

Herewith some additional info.

#
# Table structure for table `strokeaverages`
#
CREATE TABLE `strokeaverage` (
`ID` int(5) NOT NULL auto_increment,
`no` varchar(10) NOT NULL default '',
`gender` varchar(1) NOT NULL default '',
`surname` varchar(25) NOT NULL default '',
`name` varchar(25) NOT NULL default ",
`concat` varchar(50) NOT NULL default ",
`2009` varchar(4) NOT NULL default '',
`age` varchar(4) NOT NULL default '',
`gnsa16` varchar(15) NOT NULL default '',
`d11` varchar(3) NOT NULL default '',
`d12` varchar(3) NOT NULL default '',
`d21` varchar(3) NOT NULL default '',
`d22` varchar(3) NOT NULL default '',
`d31` varchar(3) NOT NULL default '',
`d32` varchar(3) NOT NULL default '',
`d41` varchar(3) NOT NULL default '',
`d42` varchar(3) NOT NULL default '',
`d51` varchar(3) NOT NULL default '',
`d52` varchar(3) NOT NULL default '',
`d61` varchar(3) NOT NULL default '',
`d62` varchar(3) NOT NULL default '',
`d71` varchar(3) NOT NULL default '',
`d72` varchar(3) NOT NULL default '',
`d81` varchar(3) NOT NULL default '',
`d82` varchar(3) NOT NULL default '',
`d91` varchar(3) NOT NULL default '',
`d92` varchar(3) NOT NULL default '',
`d101` varchar(3) NOT NULL default '',
`d102` varchar(3) NOT NULL default '',
`d111` varchar(3) NOT NULL default '',
`d112` varchar(3) NOT NULL default '',
`d121` varchar(3) NOT NULL default '',
`d122` varchar(3) NOT NULL default '',
`d131` varchar(3) NOT NULL default '',
`d132` varchar(3) NOT NULL default '',
`rounds` varchar(15) NOT NULL default '',
`avglowest` varchar(15) NOT NULL default '',
`avgall` varchar(15) NOT NULL default '',
`n11` varchar(3) NOT NULL default '',
`n12` varchar(3) NOT NULL default '',
`n21` varchar(3) NOT NULL default '',
`n22` varchar(3) NOT NULL default '',
`n23` varchar(3) NOT NULL default '',
`n31` varchar(3) NOT NULL default '',
`n32` varchar(3) NOT NULL default '',
`n41` varchar(3) NOT NULL default '',
`n42` varchar(3) NOT NULL default '',
`n51` varchar(3) NOT NULL default '',
`n52` varchar(3) NOT NULL default '',
`n61` varchar(3) NOT NULL default '',
`n62` varchar(3) NOT NULL default '',
`sa1801` varchar(3) NOT NULL default '',
`sa1802` varchar(3) NOT NULL default '',
`sa1803` varchar(3) NOT NULL default '',
`sa1804` varchar(3) NOT NULL default '',
`sa1601` varchar(3) NOT NULL default '',
`sa1602` varchar(3) NOT NULL default '',
`sa1603` varchar(3) NOT NULL default '',
`sa1604` varchar(3) NOT NULL default '',
`sa1401` varchar(3) NOT NULL default '',
`sa1402` varchar(3) NOT NULL default '',
`sa1403` varchar(3) NOT NULL default '',
`sa1404` varchar(3) NOT NULL default '',
`sa1201` varchar(3) NOT NULL default '',
`sa1202` varchar(3) NOT NULL default '',
`sa1203` varchar(3) NOT NULL default '',
`sa1204` varchar(3) NOT NULL default '',
`gn01` varchar(3) NOT NULL default '',
`gn02` varchar(3) NOT NULL default '',
`gn03` varchar(3) NOT NULL default '',
`gn04` varchar(3) NOT NULL default '',
PRIMARY KEY (`ID`)
) TYPE=MyISAM COMMENT='Domestic Stroke Averages' AUTO_INCREMENT=71 ;
# --------------------------------------------------------
#
# Dumping data for table `strokeaverage`
#
INSERT INTO `strokeaverage` VALUES (1,'d11', 9);
INSERT INTO `strokeaverage` VALUES (2,'d12', 10);
INSERT INTO `strokeaverage` VALUES (3,'d21', 11);
INSERT INTO `strokeaverage` VALUES (4,'d22', 12);
INSERT INTO `strokeaverage` VALUES (5,'d31',13);
INSERT INTO `strokeaverage` VALUES (6,'d32',14);
INSERT INTO `strokeaverage` VALUES (7,'d41', 15);
INSERT INTO `strokeaverage` VALUES (8,'d42', 16);
INSERT INTO `strokeaverage` VALUES (9,'d51', 17);
INSERT INTO `strokeaverage` VALUES (10,'d52',1

;
INSERT INTO `strokeaverage` VALUES (11,'d61',19);
INSERT INTO `strokeaverage` VALUES (12,'d62',20);
INSERT INTO `strokeaverage` VALUES (13,'d71',21);
INSERT INTO `strokeaverage` VALUES (14,'d72',22);
INSERT INTO `strokeaverage` VALUES (15,'d81',23);
INSERT INTO `strokeaverage` VALUES (16,'d82',24);
INSERT INTO `strokeaverage` VALUES (17,'d91',25);
INSERT INTO `strokeaverage` VALUES (18,'d92',26);
INSERT INTO `strokeaverage` VALUES (19,'d101',27);
INSERT INTO `strokeaverage` VALUES (20,'d102',2

;
INSERT INTO `strokeaverage` VALUES (21,'d121',29);
INSERT INTO `strokeaverage` VALUES (22,'d122',30);
INSERT INTO `strokeaverage` VALUES (23,'d131',31);
INSERT INTO `strokeaverage` VALUES (24,'d132',32);
INSERT INTO `strokeaverage` VALUES (25,'n11',36);
INSERT INTO `strokeaverage` VALUES (26,'n12',37);
INSERT INTO `strokeaverage` VALUES (27,'n21',3

;
INSERT INTO `strokeaverage` VALUES (28,'n22',39);
INSERT INTO `strokeaverage` VALUES (29,'n31',40);
INSERT INTO `strokeaverage` VALUES (30,'n32',41);
INSERT INTO `strokeaverage` VALUES (31,'n41',42);
INSERT INTO `strokeaverage` VALUES (32,'n42',43);
INSERT INTO `strokeaverage` VALUES (33,'n51',44);
INSERT INTO `strokeaverage` VALUES (34,'n52',45);
INSERT INTO `strokeaverage` VALUES (35,'n61',46);
INSERT INTO `strokeaverage` VALUES (36,'n62',47);
INSERT INTO `strokeaverage` VALUES (37,'sa1801',4

;
INSERT INTO `strokeaverage` VALUES (38,'sa1802',49);
INSERT INTO `strokeaverage` VALUES (39,'sa1803',50);
INSERT INTO `strokeaverage` VALUES (40,'sa1804',51);
INSERT INTO `strokeaverage` VALUES (41,'sa1601',52);
INSERT INTO `strokeaverage` VALUES (42,'sa1602',53);
INSERT INTO `strokeaverage` VALUES (43,'sa1603',54);
INSERT INTO `strokeaverage` VALUES (44,'sa1604',55);
INSERT INTO `strokeaverage` VALUES (45,'sa1401',56);
INSERT INTO `strokeaverage` VALUES (46,'sa1402',57);
INSERT INTO `strokeaverage` VALUES (47,'sa1403',5

;
INSERT INTO `strokeaverage` VALUES (48,'sa1404',59);
INSERT INTO `strokeaverage` VALUES (49,'sa1201',60);
INSERT INTO `strokeaverage` VALUES (50,'sa1202',61);
INSERT INTO `strokeaverage` VALUES (51,'sa1203',62);
INSERT INTO `strokeaverage` VALUES (52,'sa1204',63);
INSERT INTO `strokeaverage` VALUES (53,'gn01',64);
INSERT INTO `strokeaverage` VALUES (54,'gn02',65);
INSERT INTO `strokeaverage` VALUES (55,'gn03',66);
INSERT INTO `strokeaverage` VALUES (56,'gn04',67);

Posting this in phpadmin and trying to upload as a txt file creates an error

So it is more than likely not quite correct or the file is too big

navaldesign · #9 08-23-2009, 01:06 PM

Sorry Rob, i don't have the time to do this.

You need to plan what the table structure will be in relationship to your excel table.

The simplest way i can see is that you have in both the same structure, and then, after updating the excel file, you export it in csv format wich you then import in the MySQL table through phpMyAdmin. Of course you will need to empty the table completely before you upload the updated csv. Otherwise you should also have some code that would checkthe new values and instead of "insert" ing it would "update" the table correctly.

Rob (SA) · #10 08-23-2009, 01:13 PM

Hi George,

Thanks for the advice.

I have tried loading the CSV file previously but also had some problems in loading.

Maybe becuase I was loading it with all the data and you are saying I shouldnt do that at first.

Once the csv file it loaded to mySql do i then create the form to populate the data ?

navaldesign · #11 08-23-2009, 01:45 PM

Rob, you are confusing me.

If you load the data directly from your (updated) Excel csv file, what would you need a form for ?

I'm not yet sure what you are trying to do. Do you ONLY want to display the info ? if yes, you don't need a MySQL database. Simply create a script that will read the csv file (that you will need to upload on the site using FTP) and let the script simply display the info.

A MySQL table would only be needed if you wat to add single entries 8(ach tournaments results) and have MySQL in conjunction with a php script calculate averages and display them.

Rob (SA) · #12 08-23-2009, 01:51 PM

hi George,

I will be uploading new tournament dat later and thus the MySql option is what we have in mind.

I have tried loading the csv file but error #1064 persists

navaldesign · #13 08-23-2009, 01:54 PM

You can't just upload a scv file. It needs to be formatted in a specific way so that it can be loaded into a pre-made table.

I suggest that you read some MySQL tutorials on importing csv files.

Rob (SA) · #14 08-23-2009, 01:57 PM

Hi George,

So then if i understand correctly by removing all the data form the excel spreadsheet and leaving only the titlesof each columns(field name) and saving it as csv.

I have read some posts in the forum.

I then import the saved csv file through phpadmin.

Or is this process even more complex?

navaldesign · #15 08-23-2009, 02:03 PM

The simplest way to import a csv file is this:

1. Create a table that has EXCACTLY the same structure (column names, type of data etc) as your Excel file.
2. Upload this csv file in your server.
3. Create a simple php script that will:
a. Empty the table
b. Read the file records
c. insert each csv row in a MySQL row.

I don't understand however: if you already have to do this work in your desktop (Excel) why do you need to insert the same dat ain your MySQl ?

As i said, instead of the above, the php script could simply read the CSV file (as if it was reading drom MySQL) and display the data in your page.
After all, a CSV file has the same structure as a MySQL table.

Rob (SA) · #16 08-23-2009, 02:04 PM

Hi George,

Sorry for all the questions.

If i take the information listed in small blue text below and post it under the Run sql query/queries on database "eghfya_DOOM" and then press GO

Would this not create the table I am looking to create under the Database above?

navaldesign · #17 08-23-2009, 02:08 PM

I didn't examine it for syntax errors. If it is correct, then yes, it will create the table.

I mean only the first part, up to

TYPE=MyISAM COMMENT='Domestic Stroke Averages' AUTO_INCREMENT=71 ;

Without the =71 part

Rob (SA) · #18 08-23-2009, 02:12 PM

Hi George,

I am glad as that was my understanding and the first task at hand

-create the table.

It comes up with an error when I do it this way so either it takes to long or the file syntax as you describe it is wrong.

Thaks for taking the time and helping me - it is appreciated

Rob (SA) · #19 08-23-2009, 02:38 PM

Hi George,

NOt working for me - keeps giving me line errors.

navaldesign · #20 08-23-2009, 03:57 PM

CREATE TABLE `strokeaverage` (
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`no` varchar(10) NOT NULL default '',
`gender` varchar(1) NOT NULL default '',
`surname` varchar(25) NOT NULL default '',
`name` varchar(25) NOT NULL default ",
`concat` varchar(50) NOT NULL default ",
`2009` varchar(4) NOT NULL default '',
`age` varchar(4) NOT NULL default '',
`gnsa16` varchar(15) NOT NULL default '',
`d11` varchar(3) NOT NULL default '',
`d12` varchar(3) NOT NULL default '',
`d21` varchar(3) NOT NULL default '',
`d22` varchar(3) NOT NULL default '',
`d31` varchar(3) NOT NULL default '',
`d32` varchar(3) NOT NULL default '',
`d41` varchar(3) NOT NULL default '',
`d42` varchar(3) NOT NULL default '',
`d51` varchar(3) NOT NULL default '',
`d52` varchar(3) NOT NULL default '',
`d61` varchar(3) NOT NULL default '',
`d62` varchar(3) NOT NULL default '',
`d71` varchar(3) NOT NULL default '',
`d72` varchar(3) NOT NULL default '',
`d81` varchar(3) NOT NULL default '',
`d82` varchar(3) NOT NULL default '',
`d91` varchar(3) NOT NULL default '',
`d92` varchar(3) NOT NULL default '',
`d101` varchar(3) NOT NULL default '',
`d102` varchar(3) NOT NULL default '',
`d111` varchar(3) NOT NULL default '',
`d112` varchar(3) NOT NULL default '',
`d121` varchar(3) NOT NULL default '',
`d122` varchar(3) NOT NULL default '',
`d131` varchar(3) NOT NULL default '',
`d132` varchar(3) NOT NULL default '',
`rounds` varchar(15) NOT NULL default '',
`avglowest` varchar(15) NOT NULL default '',
`avgall` varchar(15) NOT NULL default '',
`n11` varchar(3) NOT NULL default '',
`n12` varchar(3) NOT NULL default '',
`n21` varchar(3) NOT NULL default '',
`n22` varchar(3) NOT NULL default '',
`n23` varchar(3) NOT NULL default '',
`n31` varchar(3) NOT NULL default '',
`n32` varchar(3) NOT NULL default '',
`n41` varchar(3) NOT NULL default '',
`n42` varchar(3) NOT NULL default '',
`n51` varchar(3) NOT NULL default '',
`n52` varchar(3) NOT NULL default '',
`n61` varchar(3) NOT NULL default '',
`n62` varchar(3) NOT NULL default '',
`sa1801` varchar(3) NOT NULL default '',
`sa1802` varchar(3) NOT NULL default '',
`sa1803` varchar(3) NOT NULL default '',
`sa1804` varchar(3) NOT NULL default '',
`sa1601` varchar(3) NOT NULL default '',
`sa1602` varchar(3) NOT NULL default '',
`sa1603` varchar(3) NOT NULL default '',
`sa1604` varchar(3) NOT NULL default '',
`sa1401` varchar(3) NOT NULL default '',
`sa1402` varchar(3) NOT NULL default '',
`sa1403` varchar(3) NOT NULL default '',
`sa1404` varchar(3) NOT NULL default '',
`sa1201` varchar(3) NOT NULL default '',
`sa1202` varchar(3) NOT NULL default '',
`sa1203` varchar(3) NOT NULL default '',
`sa1204` varchar(3) NOT NULL default '',
`gn01` varchar(3) NOT NULL default '',
`gn02` varchar(3) NOT NULL default '',
`gn03` varchar(3) NOT NULL default '',
`gn04` varchar(3) NOT NULL default '')
TYPE=MyISAM COMMENT='Domestic Stroke Averages';

What are you trying to do with these lines?

`name` varchar(25) NOT NULL default ",
`concat` varchar(50) NOT NULL default ",

?

As you have it, it giìves the "name" column the default value concat` varchar(50) NOT NULL default

which i don't understand.

Also, it is controversial that you assign the NOT NULL property but you set as default ''

Rob (SA) · #21 08-23-2009, 04:04 PM

Hi George,

Thanks for the reply.

The name is the persons first name
The 'concat' would have been the combination of both surname and name as I had on my spreadsheet. I guess this is no longer required as it can be done by a script.

With regards to the NOT NULL Defaults - I do not know what is correct or not correct - as I do not know the actual requirement.

The stuff in () I understand is the amount of characters that is required for the field.

thanks for taking the time again

navaldesign · #22 08-23-2009, 04:08 PM

The above query as i have posted it is functional. However it will set the NOT NULL columns to NOT NULL, which means that MySQL will deny a new insert if no value exists (at least a ' ' white space should be entered)

Just delete the NOT NULL from the query and you will be ok.

Rob (SA) · #23 08-23-2009, 04:17 PM

Hi George,

Thanks for the support - much appreciated.

I I understand correctly in the origanl "blue Text" there should have been a space

i.e `d12` varchar(3) NOT NULL default ' ',

and it should not read as

`d12` varchar(3) NOT NULL default '',

navaldesign · #24 08-23-2009, 04:20 PM

Correct. BUT there is no reason to set the column to NOT NULL, if you need to insert at least the white space.

When you insert / update records in a MySQL through a php script, it is the script that should perform a validation and allow or disallow an empty value. This way you also have control over the error message that will be displayed to the user.

Rob (SA) · #25 08-23-2009, 04:27 PM

Hi George,

I think I am getting the info into my head eventually.

There will be instances when my dat will have open spaces - no inputs and thus the entry would be correct to have spaces.

So would I be correct in suggesting the lines must all be changed to show as:

`d12` varchar(3) NOT NULL default ' ',

or should it be

`d12` varchar(3) default ' ',

navaldesign · #26 08-23-2009, 04:51 PM

A default is only used when you MUST have a value.

In example a date column can have, as default, now() which will add date and time.

Otherwise it is meaningless.

Just make your query lines

`d12` varchar(3) ,

Rob (SA) · #27 08-23-2009, 04:57 PM

Hi George,

Thanks I will try it like this.

Rob (SA) · #28 08-23-2009, 05:08 PM

Hi George,

Table created - thanks very much

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)