MySQL next autoindex.

FinalMjolnir

Is there a way, through PHP to retrive what the next autoindex of a table is?

bastien

get the last insert via [man]mysql_insert_id/man and add 1

FinalMjolnir

Hmm, I looked at the definition, and I'm not sure how to use it.

I want to get the next autoindex for the table 'threads'. And the last INSERT I did was not in the table 'threads'... I'm a little confused how to use this.

[deleted]

using bastien 's advice will result in errors if the last record is deleted

and pay attention to the following :
mysql> select * from up;
+----+------------+------------+
| id | txt | date |
+----+------------+------------+
| 1 | wqwmo | 0211041039 |
| 2 | mote | 0210120016 |
| 3 | phpbuilder | 0211161642 |
| 4 | txt1 | 0211181109 |
| 5 | txt2 | 0211181109 |
+----+------------+------------+
5 rows in set (0.00 sec)

mysql> select last_insert_id() from up limit 1;
+------------------+
| last_insert_id() |
+------------------+
| 8 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into up (txt) values ('txttx');
Query OK, 1 row affected (0.00 sec)

mysql> select last_insert_id() from up limit 1;
+------------------+
| last_insert_id() |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)

mysql> select * from up;
+----+------------+------------+
| id | txt | date |
+----+------------+------------+
| 1 | wqwmo | 0211041039 |
| 2 | mote | 0210120016 |
| 3 | phpbuilder | 0211161642 |
| 4 | txt1 | 0211181109 |
| 5 | txt2 | 0211181109 |
| 6 | txttx | 0211202318 |
+----+------------+------------+
6 rows in set (0.00 sec)

i wolud recomend you to check the information given by
show table status , but try to avoid using it in your scripts

devinemke

assuming your using mySQL and the primary key field in the "threads" table is called "thread_id":

extract (mysql_fetch_assoc (mysql_query ("SELECT MAX(thread_id) AS max_thread_id FROM threads")));
$next_autoindex = $max_thread_id + 1;

if you need to find this number right after doing and INSERT to threads then you should use mysql_insert_id as bastien mentioned:

mysql_query ("INSERT INTO threads SET thread = 'whatever'");
$next_autoindex = mysql_insert_id () + 1;

FinalMjolnir

Orange: No clue what you're talking about. Sorry.

Devinemke: I think the first one is the one I'm looking for, the thing is, I'm not touching the table before hand. So I don't think the mysql_insert_id() would help.

Could you explain the coding in the first one, I don't like just plopping code in, I'd rather understand it than just trusting it, if you know what I mean.

[deleted]

guys, please pay attention to the way mysql increments
it worked as describes in
devinemke 's in some prior versions but take a look :
mysql> select max(id)+1 as next_id from up;
+---------+
| next_id |
+---------+
| 7 |
+---------+
1 row in set (0.00 sec)

mysql> delete from up where id=6;
Query OK, 1 row affected (0.00 sec)

mysql> select max(id)+1 as next_id from up;
+---------+
| next_id |
+---------+
| 6 |
+---------+
1 row in set (0.00 sec)

mysql> insert into up (txt) values('next id');
Query OK, 1 row affected (0.00 sec)

mysql> select * from up;
+----+------------+------------+
| id | txt | date |
+----+------------+------------+
| 1 | wqwmo | 0211041039 |
| 2 | mote | 0210120016 |
| 3 | phpbuilder | 0211161642 |
| 4 | txt1 | 0211181109 |
| 5 | txt2 | 0211181109 |
| 7 | next id | 0211202334 |
+----+------------+------------+
6 rows in set (0.00 sec)

devinemke

let me break it down:

$sql = 'SELECT MAX(thread_id) AS max_thread_id FROM threads';

this creates a variable for an SQL query that gets the highest value in the "thread_id" column using the MAX() aggregate function and stores it in a varibale called "max_thread_id".

$result = mysql_query ($sql);

...runs the query and stores the result set as $result

$array = mysql_fetch_assoc ($result);

...spools the result set into an associative array

extract ($array);

...extracts out the array element as a global variable so now you have a variable $max_thread_id that is the last thread_id that was inserted. All of the above code can be done in a single line:

extract (mysql_fetch_assoc (mysql_query ("SELECT MAX(thread_id) AS max_thread_id FROM threads")));

then add 1 to that varibale:

$next_autoindex = $max_thread_id + 1;

$next_autoindex is the value of whatever the next insert auto_incerement will be.

FinalMjolnir

Okay, that makes a lot more sense, when it was all on one line I just got confused.

But looking at this, I think what orange is saying is making more sense.

If the last thing was deleted, then the autoindex, would still stay the same, but then when you run this, you'll get the same number as the row that was deleted, messing things up.

[deleted]

as I've told you show table status
contains the needed information
if you read the manual you will easily find out what you need

$q = mysql_query("show table status from DATABASE like 'TABLE'") or die(mysql_error());
print  mysql_result($q, 0, 'Auto_increment'); // which prints the next autoincrement value

but as I said : don't rely on it !
if you have a table in which many users read and write simultaneously the above information is outdated once retrieved.
I know it's a matter of seconds (even miliseconds) but if you are working on something really important this could (and will ) mess the things up

FinalMjolnir

Well, I'm need this for forums that I'm writing. So yes, many people could be doing things at one time.

I've tried looking at how other forums did it, but I can't find my way around their code very well.

Any suggestions?

leatherback

Uhm..
Maybe a silly quetion, BUT:
If you are working on a forum, I can only see one reason you want to know the next ID to use for a message (???)

use an auto_increment type colum to store ID's and you don't have to worry anymory?

Or am I now oversimplifying the problem?

You do not need to solve the problem to get a solution!? <

FinalMjolnir

Yeah, I need it because, when you make a post, it actually puts data into two tables.

One called 'threads', that has the firstpost, lastpost, views, ID's. And then it inserts a row into a table called 'replies' (posts would make more sense, but who cares), which has all the data.

When the topic listing is called, it gets the threads, then checks the firstpost ID's and gets the subject from the firstpost...

If you know what I mean... lol

leatherback

haha,

Consider renaming some things while you are still on top! 😉

Also, look at the need for storing all this in sepearte tables (Seems like the right way though).

But here you can do an easy thing: you can submit the data to one table, query that table for the inserted data, extract the ID and use that to submit.

So:

submit message metadata
Query metadatatable, and extract the message ID there
Submit in the new table, using message_ID from metadata as a MSS number?

FinalMjolnir

Well, I actually just think I figured out that I don't actually need to do this...

[EDIT] Yup, completely unneeded. My bad guys. 😉

[deleted]

Well, I actually just think I figured out that I don't actually need to do this...

yes, believe me you don't
if it was actually needed a working solution would exist
work on your database design (read some articles on the topic) and I am sure you will create a better forum 🙂)