Tackle this problem...Function

jumpenjuhosaphat · 07-12-2006, 11:44 PM

Can anyone help?
Find the first available id# in the primary field of a table.

So, if there are the following entries:

id# Name
1 Joe
2 John
3 Bill
5 Sue
6 Allen

The 4 is missing, I need a script that returns the first available id#. The field is not auto_increment, but it is the primary_key.

jumpenjuhosaphat · 07-13-2006, 01:30 AM

Okay, here is what I came up with, tell me what you think.

PHP Code:

   $nextnum = 1;
$result = mysql_query('SELECT * FROM table WHERE id =" ' . $nextnum . ' ");
while ($row = mysql_fetch_array($result))
{
$nextnum ++;
}

The next available row would be in the variable $nextnum. This stops as soon as a the script comes across an available row, so if you have row's 1, 2, 3, 5, this script would return "4". I hope this comes in handy for someone else.

kenni · 07-13-2006, 01:33 AM

It is quite easy to do, but it may raise your mysql load a little high. Is there any particular reason why you want this? Why not have the auto_increment setting on the #id?

jumpenjuhosaphat · 07-13-2006, 01:35 AM

auto_increment, when a row is deleted, will leave a hole in the table. I thought about the load it might put on the server, but the most records I'll have in a table will be no more than 100, so it will process this relatively quick. Also, I am the only one to use this, it won't be available for visitors to use, so I don't have to worry about careless overload.

rohan2kool · 07-15-2006, 02:49 AM

Don't worry for these holes in the table. You are wasting resources if you are looking for holes. Use auto_increment. After a long long time, when it reaches the maximum id's, it might itself search for holes since it's been set to primary_key and after all holes are filled, it might obviously give a SQL error.

jumpenjuhosaphat · 07-15-2006, 02:55 AM

I normally wouldn't have worried about them, but I am using the primary keys as part of my indexing. I would have a page, based on that table that was named:
Table_primarykey.php
And also, the primary key would be used as page numbers, and it would look strange if there were missing numbers.
I'm sure there are better ways of doing this, but I am still extremely new to web design. I just started php last weekend, so it's the best I could come up with with my limited knowledge base.

Daniel Malone · 07-18-2006, 03:46 PM

I would forget about the "holes" in the table. It's not a really big deal. That way, if you wanted to, you could order them by "time" (order by id DESC).

rohan2kool · 07-18-2006, 04:43 PM

@jumpenjuhosaphat: We are leading nowhere here. Can you tell us the EXACT purpose of ur application, so that we can help you better...

jumpenjuhosaphat · 07-18-2006, 07:33 PM

Its all good now, I figured out my problem a few posts back. Thanks for everyones help.

07-12-2006, 11:44 PM	#1
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Tackle this problem...Function Can anyone help? Find the first available id# in the primary field of a table. So, if there are the following entries: id# Name 1 Joe 2 John 3 Bill 5 Sue 6 Allen The 4 is missing, I need a script that returns the first available id#. The field is not auto_increment, but it is the primary_key. __________________ Storage Sheds Lost Forum Last edited 07-13-2006 at 12:21 AM.

07-13-2006, 01:30 AM	#2
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: Tackle this problem...Function Okay, here is what I came up with, tell me what you think. PHP Code: `$nextnum = 1; $result = mysql_query('SELECT * FROM table WHERE id =" ' . $nextnum . ' "); while ($row = mysql_fetch_array($result)) { $nextnum ++; }` The next available row would be in the variable $nextnum. This stops as soon as a the script comes across an available row, so if you have row's 1, 2, 3, 5, this script would return "4". I hope this comes in handy for someone else. __________________ Storage Sheds Lost Forum

07-13-2006, 01:33 AM	#3
kenni Registered Member Join Date: Dec 2005 Posts: 1,543	Re: Tackle this problem...Function It is quite easy to do, but it may raise your mysql load a little high. Is there any particular reason why you want this? Why not have the auto_increment setting on the #id? __________________ Wanna thank someone? Give 'em a rep. More info.

07-13-2006, 01:35 AM	#4
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: Tackle this problem...Function auto_increment, when a row is deleted, will leave a hole in the table. I thought about the load it might put on the server, but the most records I'll have in a table will be no more than 100, so it will process this relatively quick. Also, I am the only one to use this, it won't be available for visitors to use, so I don't have to worry about careless overload. __________________ Storage Sheds Lost Forum

07-15-2006, 02:49 AM	#5
rohan2kool Member Join Date: Dec 2005 Posts: 132	Re: Tackle this problem...Function Don't worry for these holes in the table. You are wasting resources if you are looking for holes. Use auto_increment. After a long long time, when it reaches the maximum id's, it might itself search for holes since it's been set to primary_key and after all holes are filled, it might obviously give a SQL error. __________________ Eat healthy, Stay Fit, Die Anyway. --==-- If there is a man who knows everything about women, there is a Windows distribution without bugs.

07-15-2006, 02:55 AM	#6
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: Tackle this problem...Function I normally wouldn't have worried about them, but I am using the primary keys as part of my indexing. I would have a page, based on that table that was named: Table_primarykey.php And also, the primary key would be used as page numbers, and it would look strange if there were missing numbers. I'm sure there are better ways of doing this, but I am still extremely new to web design. I just started php last weekend, so it's the best I could come up with with my limited knowledge base. __________________ Storage Sheds Lost Forum

07-18-2006, 03:46 PM	#7
Daniel Malone Senior Member Join Date: May 2006 Posts: 252	Re: Tackle this problem...Function I would forget about the "holes" in the table. It's not a really big deal. That way, if you wanted to, you could order them by "time" (order by id DESC). __________________ Webmaster News and Daniel's blog can be found at Daniel Malone Free Webmaster Tools

07-18-2006, 04:43 PM	#8
rohan2kool Member Join Date: Dec 2005 Posts: 132	Re: Tackle this problem...Function @jumpenjuhosaphat: We are leading nowhere here. Can you tell us the EXACT purpose of ur application, so that we can help you better... __________________ Eat healthy, Stay Fit, Die Anyway. --==-- If there is a man who knows everything about women, there is a Windows distribution without bugs.

07-18-2006, 07:33 PM	#9
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: Tackle this problem...Function Its all good now, I figured out my problem a few posts back. Thanks for everyones help. __________________ Storage Sheds Lost Forum

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
The Problem With Google	anielsen	Google	6	11-20-2006 01:13 PM
iWebTool Problem	Daniel Malone	Help and Support	4	06-19-2006 07:11 PM
frame problem	marionn	HTML	1	05-27-2006 01:11 PM
problem with my order	sydney	Help and Support	1	03-31-2006 11:50 PM