Page 1 of 1
[SOLVED] How to use PHP to query DB for available numbers
Posted: Fri Apr 25, 2008 2:56 am
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
Re: How to use PHP to query DB for available numbers
Posted: Fri Apr 25, 2008 5:02 am
by VladSun
Why would you need this? Please, explain.
Re: How to use PHP to query DB for available numbers
Posted: Fri Apr 25, 2008 5:34 am
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
Re: How to use PHP to query DB for available numbers
Posted: Fri Apr 25, 2008 5:44 am
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
Re: How to use PHP to query DB for available numbers
Posted: Fri Apr 25, 2008 5:55 am
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

Re: How to use PHP to query DB for available numbers
Posted: Fri Apr 25, 2008 5:59 am
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.
Re: How to use PHP to query DB for available numbers
Posted: Fri Apr 25, 2008 6:04 am
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?

Re: How to use PHP to query DB for available numbers
Posted: Fri Apr 25, 2008 7:59 am
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?
Re: How to use PHP to query DB for available numbers
Posted: Fri Apr 25, 2008 8:25 am
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?
Re: How to use PHP to query DB for available numbers
Posted: Fri Apr 25, 2008 8:51 am
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
Re: How to use PHP to query DB for available numbers
Posted: Tue Apr 29, 2008 1:25 am
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.
Re: How to use PHP to query DB for available numbers
Posted: Tue Apr 29, 2008 3:52 am
by onion2k
60s seems a bit mad. Try adding an index to `user_number`.
Re: How to use PHP to query DB for available numbers
Posted: Tue Apr 29, 2008 3:59 am
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

Re: How to use PHP to query DB for available numbers
Posted: Tue Apr 29, 2008 4:12 am
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.