autoNumber IDs (mysql)

Anon

hello everyone,

how do i go about assigning an incremental autonumber id to my records?

basically, my script goes like this. anyone can submit articles, giving a category, title, and the article contents. i would then like to store all this into a database (mysql). since i can't assign "title" as the unique key, (since it's a string abt 50 chars and contains spaces and all), i need an ID for each article.

i have tried using "SELECT count(*) from table", but it returns "resource id #2" consistently regardless of no_of_records.

please help!

khaine

You might need to cound a specific field such as "SELECT count(title) from table" but a better way would be to store the last used number in a table some where then you can select that increment it and put it back then each article has its own number. another benefit is that you can easily find out how many articles are stored and you can also extract the first 20 or articles 30-40 because each article has a sequential number instead of a random one.

Mark.

[deleted]

First, do some reading in the PHP manual about how MySQL works.
Also read this:
http://www.xs4all.nl/~vinny/index.html#4

The 'resource id #2' is a typical error for thos who do not yet understand how to use mysql with PHP (no offence)

When you're done reading all that, you'll also know how to let the database generate unique ids.

[deleted]

Never EVER try to create a unique number for use in a database, because you can't. Only the database can.

khaine

Why not its a simple enough thing to do. just have a number that you increase each time something is added. Im not 100% sure about mySQL, I use Oracle 8, but it works fine. Never had any problems.

Mark.

Anon

hi all,

thanks mark and vincent for your replies.
i found this works for me.

CREATE TABLE tblArticles( art_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, category VARCHAR(25), title VARCHAR(80), contents TEXT);

[deleted]

It is very simple, and you conveniently forgot about the multi-user aspect.

Your method will fail completely when two or more copies of the script run at the same time, which is often the case in a web environment.

Anon

The system I have written uses this method and has run successfully for about 8 months. The maximum connections at one time I have had is about 30 so I am not too worried about the multi user aspect. One select statement grabs the number increases it then puts it straight back.

Anyway each to there own. If it works it works. And as I said I dont know about mySQL due to being an oracle user.

Mark.

[deleted]

If has been running then you are extremely lucky. The method you are using is unreliable and will cause problems sooner or later.

Anon

yes, fellas.

there probably is a better way to get things done. however, as i mentioned, my method works well for me.

the reason for this is simple. my script is a basic script used to update the links for my personal homepage(PHP <grin>). there wouldn't be anyone else using the script except for me. i just needed a way for dynamic generation of links to various pages of my website.

besides not having to update every html page to include the new link, this also serves as a learning experience for me to pick up php.

so, if any of you have done a similar script, a more reliable one, with multi-user support and what not, please post a solution here, instead of critisizing the way some people do things.