How do I determine the auto increment value

[Rob (SA)]

Re: How do I determine the auto increment value

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]

Re: How do I determine the auto increment value

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)]

Re: How do I determine the auto increment value

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)]

Re: How do I determine the auto increment value

Hi George,

NOt working for me - keeps giving me line errors.

[navaldesign]

Re: How do I determine the auto increment value

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)]

Re: How do I determine the auto increment value

Hi George,

Thanks for the reply.

1. The name is the persons first name
2. 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]

Re: How do I determine the auto increment value

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)]

Re: How do I determine the auto increment value

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]

Re: How do I determine the auto increment value

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)]

Re: How do I determine the auto increment value

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]

Re: How do I determine the auto increment value

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)]

Re: How do I determine the auto increment value

Hi George,

Thanks I will try it like this.

[Rob (SA)]

Re: How do I determine the auto increment value

Hi George,

Table created - thanks very much