How do I determine the auto increment value

**navaldesign** · 08-23-2009, 11:16 AM

Re: How do I determine the auto increment value

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)** · 08-23-2009, 11:26 AM

Re: How do I determine the auto increment value

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** · 08-23-2009, 11:34 AM

Re: How do I determine the auto increment value

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)** · 08-23-2009, 11:42 AM

Re: How do I determine the auto increment value

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** · 08-23-2009, 11:49 AM

Re: How do I determine the auto increment value

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)** · 08-23-2009, 11:55 AM

Re: How do I determine the auto increment value

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)** · 08-23-2009, 12:00 PM

Re: How do I determine the auto increment value

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** · 08-23-2009, 12:06 PM

Re: How do I determine the auto increment value

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)** · 08-23-2009, 12:13 PM

Re: How do I determine the auto increment value

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** · 08-23-2009, 12:45 PM

Re: How do I determine the auto increment value

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)** · 08-23-2009, 12:51 PM

Re: How do I determine the auto increment value

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** · 08-23-2009, 12:54 PM

Re: How do I determine the auto increment value

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)** · 08-23-2009, 12:57 PM

Re: How do I determine the auto increment value

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** · 08-23-2009, 01:03 PM

Re: How do I determine the auto increment value

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.

How do I determine the auto increment value

How do I determine the auto increment value

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment