On Sun, Nov 13, 2011 at 3:43 PM, drive view <driveview@xxxxxxxxx> wrote:
> I'm a novice to MySQL and am currently facing the following difficulty.
> I'm trying to update a table with a row of data the primary key of which is
> an ID which I believe is an auto incrementing serial number. My first
> question is how to check if this is the case.
Note: this question would probably be better directed at a mysql
forum. It's not really a PHP question.
If you can run mysql at the command line on the server, you should be
able to examine the table to see it's structure:
EXPLAIN tablename;
Will show you the schema for that table and whether your ID is set
auto_increment.
> Secondly while trying to insert such a row leaving out this column mySql is
> inserting the row at ID 0 (the previous ID's in the table are from 1 to 9),
> but then will not take further inserts.
This seems to indicate to me that ID is not set auto_increment. Once
you set a value that is the primary key, MySQL will not allow more
inserts over this value.
But do take a look at the table structure just to be sure.
Here's an excerpt of explain from one of my data bases with ID as the
primary key, auto_increment set:
mysql> explain wp_posts;
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default
| Extra |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL
| auto_increment |
| post_author | bigint(20) unsigned | NO | MUL | 0
| |
| post_date | datetime | NO | |
0000-00-00 00:00:00 | |
(I hope that doesn't come out too garbled, if possible look at it with
a monospaced font. If it did, you can see it better at
http://pastebin.com/13uG7s9g )
At any rate, you can see in the "Extra" column it says that ID is set
to auto increment.
If that is missing from your ID, then your ID is going to have to be
set manually from by the insert, and you're going to have to do
something like query the data base for the max ID number currently and
increment it for next insert. This can be problematic with possible
concurrent updates, so you'll also probably want to look at locks to
prevent someone from doing the same thing, and getting their insert in
before you have a chance to.
Take a look at
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-transactions.html to
get some idea of what needs to happen.
On the other hand, if your ID *is* set to auto_increment, then you may
have another issue.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
