looking for some info on mysql + php please!

[LisaDee]

Hi all,

 

May be could someone guide me what i should look into for to accomplish my goal for new DB, I've not event started search on google as I don't know what to look for.

 

For example I'll use DB with user records and information.

 

 

1)  I need to add specific ID e.g 1,2,3...10 etc to DB but if last ID is 10 and i deleted it then next new ID should be 10 again but if I delete ID 3 or any ID before last ID 10 then next new ID should be 11. If that makes sense!

 

2) How to control / prevent if more then 1 user try to edit same record?

 

Thanks 

 

 

[Psycho]

This sounds like a homework problem. If it isn't, can you state what is the business need to do as you are stating?

 

I would not implement this as part of the Primary ID field for the table - it shouldn't be used for any functionality where it is exposed to the user. It should be transparent and just do it's thing. I would use an alternate field to store such a value. But, the solution to your problem is pretty easy, I just want to know if this is homework or not. If it is I will provide some guidance and not a specific answer.

[LisaDee]

No it's not a homework it's kinda my side project for job registration system. I understand it is not a Primary ID. It will be something e.g JobNR00001, JobNR00002 etc. or JobNR1, JobNR2 etc. don't know which way is better presume 2nd as i don't know how many records i will have.

[Psycho]

So, why do you need to reuse a number when the last one is deleted vs. an earlier one deleted? You're definitely making this harder than it needs to be. If you need the number preceded with a text value then you should do that in the presentation logic rather than the database. If you include the text in the database it makes this near impossible.

 

Again, I would just have a numeric Primary ID column and use that and not worry about sequential numbering when items are deleted. In fact, I wouldn't ever delete them if the ID is that important - just update them to a "deleted" status for historical purposes.

 

Anyway, you can set the number for a field to be 1 more than the current highest value for that field in the other records in the database by creating a sub-query in your INSERT statement

 

 

INSERT INTO table_name
    (seqField, field1, filed2)
SELECT MAX(seqField)+1, '$field1Value', '$field2Value' FROM table_name

[LisaDee]

i think this is basically similar to invoicing. when numbers should be in order. and further i planing when row is deleted it will be moved to secondary DB where admin can revive and delete it completely. Just thinking what will happen with primary DB job_number if i'll move it out. It will be duplicate isn't it? 

 

Don't know if i'll be able pull all this off i'm not very good with php.  

 

ok i can add text part separate as label or so and then.

 

don't know which would be the best way to approach all this

[Psycho]

 . . .  when row is deleted it will be moved to secondary DB where admin can revive and delete it completely. Just thinking what will happen with primary DB job_number if i'll move it out. It will be duplicate isn't it?

 

Which is exactly why you should not delete/remove them, nor should you reuse a number. You should think of an invoice number like a checkbook. If you start to write a check and screw up, you just void that check and make a note of it in your register. You don't try to reuse that number by changing the number on the next check. If an invoice is invalid or otherwise unneeded, then change its status to deleted, voided, whatever and just leave it in the table. No need to move the record to a new table.

 

You are making this harder than it needs to be.

[LisaDee]

yes i was thinking to leave it simple on those job numbers. As in this demo shows exactly what u where saying.  I think this is the right syntax for mysql >>> ID int NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID) ???

 

Yes and that makes sense too to change a status to deleted and just need to hide from rest of them, valid ones.

 

ok finally getting somewhere now

 

 

@ Psycho Do u have any suggestions on 2. question about multi user thing?

 

[mac_gyver]

just need to hide from rest of them

that part is easy, you just make a view for your table that matches the valid/non-deleted rows and use that view name as the table name in your queries. you would query using the original table name when you want to operate on all the rows, deleted and non-deleted.

[Psycho]

 

@ Psycho Do u have any suggestions on 2. question about multi user thing? 

 

 

There are may ways. The hard way is to 'lock' the record when someone opens it to be edited. But, with a stateless application such as a web interface that introduces a whole host of problems to solve. The easier way is to allow any (authorized) user to open the record for editing. But, when you pull the information from the database, also pull a last updated timestamp. Then when they go to save verify that the timestamp is the same. If not, someone saved changes to the record since they opened it.

[LisaDee]

 

The easier way is to allow any (authorized) user to open the record for editing. But, when you pull the information from the database, also pull a last updated timestamp. Then when they go to save verify that the timestamp is the same. If not, someone saved changes to the record since they opened it.

Is this somehow easy to add later on into my script. because at the moment i have no clue how to do it. I'll crack on things what i know first i think.