[SOLVED] MySQL Problems [Problem with getting a certain piece of information]

[cpd]

The situation is this.

 

I have made a registration script whereby it automatically (Using Auto Inc) assigns an ID to the person that is registering. I also want to assign and ID that is 4 Chars long.

 

My aim is to get the last entry to the table and get that ID that i have assigned (First value is 1111) and add 1 to it so that the next one is 1112 etc etc.

 

In order to do this i came up with:

 

  $qry = mysql_query("SELECT * FROM `users` WHERE `key` = 'n' AND `key` = 'a' ORDER BY `user_id` DESC LIMIT 1");
  $qry_r = mysql_fetch_assoc($qry);
  
  $newid = $qry_r['id'] + 1;

 

I would have obviously connected up to the database etc etc. Now, ive made a "Key" column that contains one of two letters, a or n. Regardless of whats in the key ive told it to select all rows as its got "key = 'a' AND key = 'n'". In addition ive told it to order but the user_id (This is autoincrement) and then told it to get the last row by saying "DESC LIMIT 1". In other words it should be selecting the last row? (Is that right?)...

 

The column is set to Integer and with a max of 11 which is default.

 

The problem thats occuring is that it doesnt add one so i can input it into the next users, infact: it doesnt even select the id of the last row that ive assigned by simply typeing the first one in which was 1111. Therfore, it should just continue with + 1...1112...+1....1113 etc etc

 

Can anyone suggest why and better yet offer an AMAZING solution that will do the job. Much appreciated!

[Mchl]

WHERE `key` = 'n' AND `key` = 'a'

 

This will never be true. This query won't return any rows.

 

I think you want:

 

WHERE `key` = 'n' OR `key` = 'a'

[akitchin]

the issue is that you're telling it to SELECT only rows where the key='a' AND the key ='n'.  obviously, this will never be the case since it can only be one or the other.  either change the AND to OR, or get rid of the WHERE clause entirely (you don't really need it if the keys are all either 'a' or 'n').

 

it's also good practice to only select the columns you will be using in a query:

 

SELECT id FROM users ORDER BY user_id DESC LIMIT 1

 

also have a look at the MAX() function.

 

EDIT:  leaving this, but Mchl beat me to the punch

[cpd]

Much appreciated for your help guys, i dont get why i didnt see that in the first place.

 

Thanks a lot guys. First thread ive ever made and i only registered today and you guys have already solved one of, im sure many, problems.

 

Thanks again

[fenway]

Just realize that this solution may not be thread-safe.

[Mchl]

Just realize that this solution may not be thread-safe.

 

Explain please?

[fenway]

You may select the "max" id while another thread inserts a row before you get a chance to "use" it.

[Mchl]

You're right. That's where transactions become useful.

[akitchin]

You're right. That's where transactions become useful.

 

that, or adequate initial design for the application.