PHP Language - Database auto_increment not functioning

Author

Database auto_increment not functioning

clicktowalk.com

2006-07-11, 9:56 pm

Hello all,

Apologies in advance if this isn't strictly the correct forum for this post,
but it relates to phpMyAdmin which I assume many of you use often.

My issue relates to an auto_increment field in my database in this case
local_id. I have configured the field as shown below, and am using
phpMyAdmin temporarily to do adds/updates etc. to the table while I work on
an Admin page. The issues I have are.

1) While I configured this field as the primary key with 'not null' and
no default value the information below suggests that a 'null' value is
allowed with the default value set to NULL

2) When entering a record I leave this field blank expecting it to
auto_increment but receive the error message
#1366 - Incorrect integer value: '' for column 'local_id' at row 1

Field Type Collation Attributes
Null Default Extra Action
local_id smallint(4) UNSIGNED ZEROFILL Yes
NULL auto_increment

This is a snippet of sql exported from the table tbl_local in which this
field is found which seems to conflict with the information displayed in
phpMyAdmin.

--
Table structure for table `tbl_local` CREATE TABLE `tbl_local` (`local_id`
smallint(4) unsigned zerofill NOT NULL auto_increment,
--

I am relatively new to PHP/MySQL having previously worked extensively with
MS access.

I am being naive in expecting this to increment automatically i.e. should I
look to use the LAST_INSERT_ID()function and increment this in my code
before creating a new record. Or is there something fundamentally wrong with
the table that is causing this problem.

Windows system configured with

PHP 5.1.2
Apache 2.0.55
MySQL 5.0.18

Thanks in advance
Matt

"I'm an idealist. I don't know where I'm going, but I'm on my way." - Carl
Sandburg

http://clicktowalk.com

Page design © Matthew McCabe - http://mattmadethis.com

Site hosted by Xeriom Networks - http://xeriom.net/in/1019

Norman Peelman

2006-07-11, 9:56 pm

"clicktowalk.com" <m_t_hill@hotmail.com> wrote in message
news:44b45119$1_4@mk-nntp-2.news.uk.tiscali.com...
> Hello all,
>
> Apologies in advance if this isn't strictly the correct forum for this
post,
> but it relates to phpMyAdmin which I assume many of you use often.
>
> My issue relates to an auto_increment field in my database in this case
> local_id. I have configured the field as shown below, and am using
> phpMyAdmin temporarily to do adds/updates etc. to the table while I work
on
> an Admin page. The issues I have are.
>
> 1) While I configured this field as the primary key with 'not null' and
> no default value the information below suggests that a 'null' value is
> allowed with the default value set to NULL
>
> 2) When entering a record I leave this field blank expecting it to
> auto_increment but receive the error message
> #1366 - Incorrect integer value: '' for column 'local_id' at row 1
>
> Field Type Collation Attributes
> Null Default Extra Action
> local_id smallint(4) UNSIGNED ZEROFILL
Yes
> NULL auto_increment
>
>
> This is a snippet of sql exported from the table tbl_local in which this
> field is found which seems to conflict with the information displayed in
> phpMyAdmin.
>
> --
> Table structure for table `tbl_local` CREATE TABLE `tbl_local` (`local_id`
> smallint(4) unsigned zerofill NOT NULL auto_increment,
> --
>
> I am relatively new to PHP/MySQL having previously worked extensively with
> MS access.
>
> I am being naive in expecting this to increment automatically i.e. should
I
> look to use the LAST_INSERT_ID()function and increment this in my code
> before creating a new record. Or is there something fundamentally wrong
with
> the table that is causing this problem.
>
> Windows system configured with
>
> PHP 5.1.2
> apache 2.0.55
> MySQL 5.0.18
>
> Thanks in advance
> Matt
>
>
> "I'm an idealist. I don't know where I'm going, but I'm on my way." - Carl
> Sandburg
>
> http://clicktowalk.com
>
> Page design © Matthew McCabe - http://mattmadethis.com
>
> Site hosted by Xeriom Networks - http://xeriom.net/in/1019
>
>
>

I don't use PHPAdmin but I would imaging it works the same as most others so
I would imagine that if you selected that field and hit enter it would set
an id automatically... or else:

INSERT INTO `tbl_local` VALUES (NULL, yadda, yadda, yadda)

would set the auto_increment field automatically. Since it can't be NULL,
MySQL updates it accordingly, that's how it's suppossed to work.

Norm

benzo

2006-07-12, 3:56 am

clicktowalk.com wrote:
> Hello all,
>
> Apologies in advance if this isn't strictly the correct forum for this post,
> but it relates to phpMyAdmin which I assume many of you use often.
>
> My issue relates to an auto_increment field in my database in this case
> local_id. I have configured the field as shown below, and am using
> phpMyAdmin temporarily to do adds/updates etc. to the table while I work on
> an Admin page. The issues I have are.
>
> 1) While I configured this field as the primary key with 'not null' and
> no default value the information below suggests that a 'null' value is
> allowed with the default value set to NULL
>
> 2) When entering a record I leave this field blank expecting it to
> auto_increment but receive the error message
> #1366 - Incorrect integer value: '' for column 'local_id' at row 1
>
> Field Type Collation Attributes
> Null Default Extra Action
> local_id smallint(4) UNSIGNED ZEROFILL Yes
> NULL auto_increment
>
>
> This is a snippet of sql exported from the table tbl_local in which this
> field is found which seems to conflict with the information displayed in
> phpMyAdmin.
>
> --
> Table structure for table `tbl_local` CREATE TABLE `tbl_local` (`local_id`
> smallint(4) unsigned zerofill NOT NULL auto_increment,
> --
>
> I am relatively new to PHP/MySQL having previously worked extensively with
> MS access.
>
> I am being naive in expecting this to increment automatically i.e. should I
> look to use the LAST_INSERT_ID()function and increment this in my code
> before creating a new record. Or is there something fundamentally wrong with
> the table that is causing this problem.
>
> Windows system configured with
>
> PHP 5.1.2
> apache 2.0.55
> MySQL 5.0.18
>
> Thanks in advance
> Matt
>
>
> "I'm an idealist. I don't know where I'm going, but I'm on my way." - Carl
> Sandburg
>
> http://clicktowalk.com
>
> Page design © Matthew McCabe - http://mattmadethis.com
>
> Site hosted by Xeriom Networks - http://xeriom.net/in/1019
>
>
>

Nah mate, you completely ignore anything with auto increment with
phpMyAdmin I think...

eg. INSERT INTO `tablename` (local_id, suburb, state) VALUES
('valueforsuburb', 'valueforstate')

I think that's how it works :) You've just got to make sure you include
local_id in the first pair of brackets.

dt Kruger

2006-07-12, 7:56 am

benzo wrote:
> clicktowalk.com wrote:
>
> Nah mate, you completely ignore anything with auto increment with
> phpMyAdmin I think...
>
> eg. INSERT INTO `tablename` (local_id, suburb, state) VALUES
> ('valueforsuburb', 'valueforstate')
>
> I think that's how it works :) You've just got to make sure you include
> local_id in the first pair of brackets.

I do it similairly, but i dont specify the auto field at all ie:

INSERT INTO `tablename` (suburb, state) VALUES ('valueforsuburb',
'valueforstate');

Works every time!

ClickToWalk

2006-07-12, 6:57 pm

dt Kruger wrote:
> benzo wrote:
n=2E[color=darkred]
>
> I do it similairly, but i dont specify the auto field at all ie:
>
> INSERT INTO `tablename` (suburb, state) VALUES ('valueforsuburb',
> 'valueforstate');
>
> Works every time!

Thanks all for confirming that, I think I need to have a look at my
system
config. I have dumped the table to the live server (i.e. host's) using
sql
and import/export functions and on phpMyAdmin on the host the table
structure is displayed correctly and I am able to create records on the
table without entering data in the auto_increment field. I suspect that
if I
use a php/mysql functions everything will work fine.

Regards
Matt

jmelnick

2006-07-14, 6:56 pm

Hello ClickToWalk,

in phpmyadmin go to the query window and type.

show create table tablename;

you should see your auto_increment field listed.

If auto increment is not working It probably means that the
auto_increment counter is lower than rows that you already have
inserted and therefore the auto increment id is not available.

Joseph Melnick

ClickToWalk wrote:
> dt Kruger wrote:
is[color=darkred]
ten.[color=darkred]
l'[color=darkred]
de[color=darkred]
>
> Thanks all for confirming that, I think I need to have a look at my
> system
> config. I have dumped the table to the live server (i.e. host's) using
> sql
> and import/export functions and on phpMyAdmin on the host the table
> structure is displayed correctly and I am able to create records on the
> table without entering data in the auto_increment field. I suspect that
> if I
> use a php/mysql functions everything will work fine.
>=20
> Regards
> Matt

ClickToWalk

2006-07-14, 6:56 pm

clicktowalk.com wrote:
> Hello all,
>
> Apologies in advance if this isn't strictly the correct forum for this post,
> but it relates to phpMyAdmin which I assume many of you use often.
>
> My issue relates to an auto_increment field in my database in this case
> local_id. I have configured the field as shown below, and am using
> phpMyAdmin temporarily to do adds/updates etc. to the table while I work on
> an Admin page. The issues I have are.
>
> 1) While I configured this field as the primary key with 'not null' and
> no default value the information below suggests that a 'null' value is
> allowed with the default value set to NULL
>
> 2) When entering a record I leave this field blank expecting it to
> auto_increment but receive the error message
> #1366 - Incorrect integer value: '' for column 'local_id' at row 1
>
> Field Type Collation Attributes
> Null Default Extra Action
> local_id smallint(4) UNSIGNED ZEROFILL Yes
> NULL auto_increment
>
>
> This is a snippet of sql exported from the table tbl_local in which this
> field is found which seems to conflict with the information displayed in
> phpMyAdmin.
>
> --
> Table structure for table `tbl_local` CREATE TABLE `tbl_local` (`local_id`
> smallint(4) unsigned zerofill NOT NULL auto_increment,
> --
>
> I am relatively new to PHP/MySQL having previously worked extensively with
> MS access.
>
> I am being naive in expecting this to increment automatically i.e. should I
> look to use the LAST_INSERT_ID()function and increment this in my code
> before creating a new record. Or is there something fundamentally wrong with
> the table that is causing this problem.
>
> Windows system configured with
>
> PHP 5.1.2
> apache 2.0.55
> MySQL 5.0.18
>
> Thanks in advance
> Matt
>
>
> "I'm an idealist. I don't know where I'm going, but I'm on my way." - Carl
> Sandburg
>
> http://clicktowalk.com
>
> Page design © Matthew McCabe - http://mattmadethis.com
>
> Site hosted by Xeriom Networks - http://xeriom.net/in/1019
>
>
>

I have now written the scripts and all records are created fine, I omit
the primary/auto-increment field from the queries, as was suggested.
Apparently there can be some issues with versions of phpMyAdmin; when I
discussed it with my host he recommended always carrying out DB admin
from the command line. I'm afraid I have always worked with a GUI so the
command line is alien to me.

Thanks again for the help

Regards
Matt

--
"I'm an idealist. I don't know where I'm going, but I'm on my way." -
Carl Sandburg

http://clicktowalk.com

Page design © Matthew McCabe <http://mattmadethis.com> -
http://mattmadethis.com

Site hosted by Xeriom Networks <http://xeriom.net/in/1019> -
http://xeriom.net/in/1019

Sponsored Links