MySQL Lowest Number Start

Nixon

Hi,

I am having a problem with a very simple php website I have made. My problem is that my id number always starts from the last record entered even if the record before has been deleted. I know this is a very simple problem but I am new to PHP and amjust learning the basics.

An example of my prblem would be

Make 5 records

1,2,3,4,5

Delete record 3

Make a new record, it automatically assigns it 6, then I have

1,2,4,5,6

Please can somebody help me?

Thanks. Nixon.

cgraz

it's actually a mysql thing, not php. But that's just the way mysql works. What's the problem?

Cgraz

Nixon

Well, I want it to automatically select the lowest ID number it can. So I have an ordered database (1,2,3,4,5,6) other than an unordered one (4,7,11,12,15).

Any way of getting round this?

tomhath

Well, when you think about it (4,7,11,12,15) is ordered.

You can't use an autoincrement column to do what you want, and trying to do it yourself is asking for trouble until you can wrap the insert statement in a transaction or use a subselect.

Must the sequence be stored in the database?

Nixon

No the information does not have to be stored in a database. I just want to be able to the use the numbers to seperate row. For example I have a text box which asks the user:

Please enter which record you want to delete: [textbox]

Then the user can specify the row they want deleting and it will delete it.

Another of my main points is that I need to be able to view records with a textbox as above and I need to beable to browse records with the numbers unless there is a simpler way to do all this. As I have mentioned I am a complete beginner and only know a few commands.

Thanks for all your help.

tomhath

The basic problem as I understand it is that you need to map from the number entered in a text box to an ID.

I'd store that mapping somewhere on the form as an array of hidden fields, indexed the same as the rows the user sees, but the value of the element is the ID.

So when the form is submitted you have the array of IDs in the hidden fields and the index of the row to delete in the text field; just index into the array to find the ID.

Nixon

Well that would kinda work, but I need to be able to add new records through it as-well. I am making a guitar record so that a user can input information about guitars they will have no mysql or php knowledge. I am just putting it in the form of a web page so it is easy to use. I think this example will help:

[index.php]

Add New Record - this is working fine

Delete Record [textbox] - this is working fine

Browse Records - havent got rount to try to figure out how to do this yet, will be future posts about it.

View Specific Record [textbox] - this is working fine

So as you can see I connot see how hidden fields would work this out because I need it to be updated when the user adds more information.

It may just be me and my noviceness or I may have some sort of a problem. Any more suggestions.

Thanks for all your help so far.

cgraz

because I need it to be updated when the user adds more information.

I'm just browsing this over, but what would it matter if your id's were

1,2,3,4,5

1,5,24,200,10001

All the id does is identify that particular row using a unique number. Whether or not they go in order doesn't matter. YOu query the database, grab the id (whatever it is. It doesn't matter if it's in order or not. The database will give you the id for that row), then use that to update the database.

Cgraz

Sxooter

We're all dancing around the subject of why serial numbers are serial, and non-repeating.

the purpose of an auto-increment field is to provide a lifetime of data set guarantee of uniqueness for a record.

Suppose that you hang a table off of this table, and put in data like so:

main table

id | maker | model | year | color

1 | bigson | pontiac | 64 | red
2 | defner | rattlecaster | 72 | maple

previous owners

id | owner

1 | Edward Van Heflin
1 | KK Bing
2 | Eilvirus

Now, you sell number 1, and put one in it's place, if you weren't careful, you might wind up saying that the 2003 RC Hirc was owned by KK Bing, which isn't possible as he's never played anything but Bigsons. :-)

Putting the 2003 RC Hirc in as number 3, and leaving 1 empty is the proper thing to do for your data...

Making a pretty numbering system to put on top of it to present it to customers is PHP's job.

tomhath

As I understand it, he doesn't really care if the ID is monotonically increasing, he just wants a nice looking display so the user can enter a row number from 1 to n (those row numbers appear on the page).

The actual ID in the database doesn't matter, but I can understand why he doesn't want to display slightly goofy looking internal IDs where a user sees them. If you display five rows, let the user see them as 1 through 5 🆒

Nixon

Thanks, tomhath.

Youve got closest so far. It really is hard to explain or else I wouldn't of tried to explain it over 3 times. Is there anyway to make it so that when the database row is deleted another row takes it place or the next row created takes its place?

Thanks again.

tomhath

I think you're trying to solve the problem in the wrong place. Make the display look pretty with php. Use autoincrement in the database with mysql. The two entities are different, there's no reason to force them to be the same.

Nixon

So how would I use the specific record view? Would I have to think up a different method or have a search query or something along those lines? Anybody any ideas, code basics to build on would be nice.

Thanks.

TTVN

Ok, to view/edit records:

You have to do a query first if I'm not mistaken,

e.g:

$sql = "SELECT * FROM table_name WHERE id=$edit_id";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);

	$e_maker=$row["mkater"];
	$e_model=$row["model"];
            $e_year=$row["year"];
	$e_color=$row["color"];

And in HTML do a form:

this to pull the record from the database.

And for displaying the records in number list, just create a variable $numlist++; so your record will show up nicely in order 1,2,3... and so on.