Doesn't mysql have sequences?
In pgsql at least, you would use the following sql to create/insert into a
table:
create table mytable (id integer, blah varchar(64));
create sequence mytable_id_seq start 1;
insert into mytable values (nextval('mytable_id_seq'), 'some text');
This nextval function is atomic, i.e. you get the value and it gets
incremented without fear of some other connection grabbing the same value
before it's been incremented. Other databases use slightly different
syntax/etc, so ymmv with mysql. But you get the drift.
Pete
>I'm modifying some PHP code I found in MySQL PHP Shopping Cart to work
>with a Books database.
>
>Basically the part add publisher (addpub.php)
> 1 - obtains the highest publisher_id already in the table publisher
>(fields publisher_id, publisher, description)
> 2 - increments it to get the next value
> 3 - sets up the form for you to enter the new publisher name and
>description.
> 4 - posts the data and clears the form everytime you hit the
>"create" button
>
>The problem is that that it doesn't increment the publisher_id for a
>second set of values, so you can end up with two identical
>publisher_id's.
>
>My guess is that the MySQL database is buffering the data insertion
>command and the value being read is the old maximum publisher_id, but
>this is updated by the time you do the second insertion, so for the
>third insertions, it is incremented.
>
>Is this correct?
>If so, how do I fix it?
>
>My guess might be that I should close the connection after each post.
>
>Code is below.
>
>owl:terryc> cat addpub.php
><?php
>
>require("../config.ini");
>
>// database connection
>mysql_connect("$host","$user","$pass");
>
>// database selection
>mysql_select_db("$database");
>
>echo "<html><body bgcolor=\"#FFFFCC\">";
>
>// selects the max id from products and adds 1 to it to make new id
>$result = mysql_query("select MAX(publisher_id) from publisher");
>
>echo "$result, <br>";
>
>/* some error checking if its the first item entered. We don't like
> products with an ID of "0" */
>$num_rows = @mysql_num_rows($result) or print("");
>
>
>// get the max id to determine the next one
>$s = mysql_fetch_row($result);
>
>
>
>
>// test if its the table is empty, if it is, put a nice starting number
>in, like 10001
>if(empty($s)) {
> $tsku = "10001";
>}
>
>// otherwise just increment the high number
>else {
> $tsku = $s[0] + 1;
>}
>
>// display the form
>echo "<table border=0><form method=post action=\"addpub.php\">
>
> <tr><td>Publisher ID:</td><td>$tsku<input type=hidden
>name=\"publisher_id\" value=\"$tsku\"></td></tr>";
>?>
> <tr><td>Publisher:</td><td><input name="publisher"
>size=75></td></tr>
> <tr><td>Description:</td><td><textarea cols=50 rows=10
>name="description" wrap=virtual>
> </textarea></td></tr>
> <tr><td><input type=submit name=page
>value="Create"></td></form></tr></table>
><?
>
>// page that inserts the values into the database
>if($page == "Create") {
>
>mysql_query("insert into publisher
>values('$publisher_id','$publisher','$description')");
>
>echo "Your publisher #$tsku has been created.";
>
>
>}
>
>?>
>owl:terryc>
>--
> Terry Collins {:-)}}} Ph(02) 4627 2186 Fax(02) 4628 7861
> email: [EMAIL PROTECTED] www: http://www.woa.com.au
> WOA Computer Services <lan/wan, linux/unix, novell>
>
> "People without trees are like fish without clean water"
>
>
>--
>SLUG - Sydney Linux User Group Mailing List - http://slug.org.au/
>More Info: http://slug.org.au/lists/listinfo/slug
>
--
SLUG - Sydney Linux User Group Mailing List - http://slug.org.au/
More Info: http://slug.org.au/lists/listinfo/slug
