[SOLVED] How to use PHP to query DB for available numbers

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

[SOLVED] How to use PHP to query DB for available numbers

Post by Janco »

Good morning all,

I'm new to PHP so your patience will be tested but hopefully not with silly questions.

First the scenario:
I have a MySQL database with all store users with their unique Login ID (user_number) in table called uni_bw_usr.

Currently I'm in the process writing a front-end to the database to try to make the user creating process easier and to try to take some pressure of staff because currently it is quite a vigorous and time consuming task to create users.

My question is:
The user_number range is >= 2000 and <=7000. The problem is that they do not run is sequence i.e. I'll have user 2000, 2001, 2002, 2007, 2008 and so on. How will I get PHP to check for available numbers between 2000 and 7000, that are not present then use the 1st number that it finds?

Thank you in advance.
Janco
Last edited by Janco on Tue May 27, 2008 1:01 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to use PHP to query DB for available numbers

Post by VladSun »

Why would you need this? Please, explain.
There are 10 types of people in this world, those who understand binary and those who don't
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

Re: How to use PHP to query DB for available numbers

Post by Janco »

We currently have a situation were if we want to create a user we have to do it at the central server and at the store, so what I've done is to populate aMysql DB with the user info with all it's flags and so on.

With this project we will only have to create a user at a central place thus descreasing the time it takes to create a user and someone with no knowledge of our software can create a user.

The problem is that we have allocated numbers which is between 2000 and 7000 and that's it. Over the years people created user numbers and deleted without re-using number so gaps started occurring in the sequence which I've been told to "close".

That is why I need to find out what PHP/MySQL query I have to write to first find the first missing number and use it.

BTW the number is used to log into the software so it is important.

I thought of using the mysql_fetch_assoc then do something like:
$startcounter=2000
$endcounter=3000
$strSQL = "SELECT user_number FROM uni_bw_usr ORDER BY user_number ASC";
$result = mysql_query($strSQL);
unset($avail_num);
unset($last);
$counter = 0;
while($row = mysql_fetch_assoc($result)) {
If (($startcounter>2000) && ($endcounter<7000)) {
If($row['user_code'] != $last+1) {
$avail_num = $last+1;
break;
}
}
$last = $row['user_code'];
$counter++;
}
If(!$avail_num) { $avail_num = $last+1; }

but I can't get it working
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: How to use PHP to query DB for available numbers

Post by onion2k »

You definitely don't want to be using a loop for this. That would be really inefficient. Join the table to itself instead..

Code: Select all

SELECT (user1.`user_number`+1) AS next_user_number 
FROM `uni_bw_usr` AS user1 
LEFT JOIN `uni_bw_usr` AS user2 ON user2.`user_number` = user1.`user_number`+1 
WHERE ISNULL(user2.`user_number`)
LIMIT 1
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to use PHP to query DB for available numbers

Post by VladSun »

Hm, that raises a question - what's the default order by when no ORDER BY clause is supplied. I've read that MySQL would use one of the indexes used to build the execution plan, but I don't know which one exactly.
onion2k, do you know how we can determinate the ordering used by MySQL when no ORDER BY is supplied.

In this case, there might be a problem with your query because it may always find the MAX()+1 number .. I don't know whether it would be the case, just suggesting the worst case ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: How to use PHP to query DB for available numbers

Post by onion2k »

VladSun wrote:Hm, that raises a question - what's the default order by when no ORDER BY clause is supplied. I've read that MySQL would use one of the indexes used to build the execution plan, but I don't know which one exactly.
onion2k, do you know how we can determinate the ordering used by MySQL when no ORDER BY is supplied.
MySQL orders by the primary key by default, or the first indexed column if there's no primary key, or the first column if there's no indexes I think. You're quite right though, there should really be "ORDER BY user1.`user_number` ASC" in my query.
In this case, there might be a problem with your query because it may always find the MAX()+1 number .. I don't know whether it would be the case, just suggesting thw worst case ;)
No, it'll find the first one. I tested it.

EDIT: Ooo.. that was your 1000th post. Well done.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to use PHP to query DB for available numbers

Post by VladSun »

Thanks for the info :)
onion2k wrote:EDIT: Ooo.. that was your 1000th post. Well done.
WOW, thanks. I haven't noticed it :)

So, what's next? ;)
There are 10 types of people in this world, those who understand binary and those who don't
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

Re: How to use PHP to query DB for available numbers

Post by Janco »

I tried the query onion2k gave me but and it worked except it doesn't start from 2000. How will I tell MySQL to start this query:

SELECT (user1.`user_number`+1) AS next_user_number FROM `uni_bw_usr` AS user1 LEFT JOIN `uni_bw_usr` AS user2 ON user2.`user_number` = user1.`user_number`+1 WHERE ISNULL(user2.`user_number`) order by next_user_number asc LIMIT 1;

from 2000 and stop at 7000?
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

Re: How to use PHP to query DB for available numbers

Post by Janco »

I've played around a bit with the SQL query and did this:

SELECT (user1.`user_number`+1) AS next_user_number FROM `uni_bw_usr` AS user1 LEFT JOIN `uni_bw_usr` AS user2 ON user2.`user_number` = user1.`user_number`+1 WHERE ISNULL(user2.`user_number`) AND user1.`user_number`+1 >2000 AND user1.`user_number`+1 <7000 order by next_user_number asc LIMIT 1;


Now - I don't know if it is technically correct but it seems to work..... now all that is left, IF IT IS THE CORRECT WAY OF DOING IT, is to pop this in PHP code and try very hard not to mess this up.

Any comments?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to use PHP to query DB for available numbers

Post by VladSun »

I think it's OK, but that one looks nicer ;)
[sql] SELECT     (user1.`user_number`+1) AS next_user_number FROM     `uni_bw_usr` AS user1 LEFT JOIN     `uni_bw_usr` AS user2 ON user2.`user_number` = user1.`user_number`+1 WHERE      ISNULL(user2.`user_number`)      AND user1.`user_number` BETWEEN 2000 AND 7000ORDER BY      next_user_number ASC LIMIT      1; [/sql]

PS: Just check border cases
PPS: Please, use [ sql] and [ /sql] (without spaces in brackets) to surround SQL code
There are 10 types of people in this world, those who understand binary and those who don't
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

Re: How to use PHP to query DB for available numbers

Post by Janco »

I've implemented the code and it works except that it is quite slow, it takes approx 60 seconds to execute the query and that is straight from MySQL. The PHP code was a little tricky I'm still trying to fix a Resource id #3 from the above in PHP. If I can't fix it I'll post it.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: How to use PHP to query DB for available numbers

Post by onion2k »

60s seems a bit mad. Try adding an index to `user_number`.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to use PHP to query DB for available numbers

Post by VladSun »

onion2k +++
you must make absolutely sure you have an index for user_num field.

Also, I've made some tests with PostgreSQL and it seems that a the ORDER BY increases the query time. So, remove it and see what happens :)
There are 10 types of people in this world, those who understand binary and those who don't
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

Re: How to use PHP to query DB for available numbers

Post by Janco »

Ah.... found the problem, it has to be hard having that wealth of knowledge and having to assist plonkers like me.

I took out the ORDER BY and query executed in 3.61s instead of 55.32s

I won't be able to add index at this time because there are duplicates in the database that I need to sort out duplications at store level thus making the duplicates quite important at this time but once they are sorted out I'll add indexing.

Thank you for all the help, I have to admit the learning curve at this point is quite steep.
Post Reply