Password table with MySQL...

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
PPS
Forum Newbie
Posts: 2
Joined: Sun Aug 10, 2003 12:18 pm

Password table with MySQL...

Post by PPS »

Hello everybody!
I have this type of table.

Code: Select all

+------+----------+
|  ID  | password |
+------+----------+
|   1  |     X    |
+------+----------+
|   2  | 43543543 |
+------+----------+
|   3  | 34656546 |
+------+----------+
|   4  | 74378286 |
+------+----------+
|   5  | 54786588 |
+------+----------+
|  ... | ...      |
+------+----------+
|  ... | ...      |
+------+----------+

The number X indicates next available password.
Works this way: firs take the value of X (X= select password from table_name where ID=1), which points to the next password. Then SELECT password from table_name where ID=X; And then UPDATE this table - I need to update password++, where ID=1 (so that it points to the next password)
But the problem is obvious - two persons making simultaneous requests for a pass obtain the same value of this X (before it's updated) and therefore get the same password, which is anacceptable. I have solved this problem easily but sertainly not efficiently and I want to see someone eslse's solution - HOW IT SHOULD BE DONE, please step-by-step, I'm new to PHP.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

First I can think of is that you could use mysql's LOCK or similiar, making the table somewhat halt untill the first job is done.

Can I recommend the following:

Code: Select all

<?php
    // generate a random string with the lenght of $l
    // in your case it looks like you want to remove all but the numbers
    function randstring($l) {
        srand((double) microtime() * 1000000);
        $c = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
        $out = "";
        for ($i = 1; $i <= $l; $i++) {
            $ran = rand(1,strlen($c));
            $out .= substr($c,$ran-1,1);
        }
        return $out;
    }

echo "<pre>";
echo randstring(8)."\n";
echo randstring(8)."\n";
echo randstring(8)."\n";
echo randstring(8)."\n";
?>
would return something like:

Code: Select all

SJV9t8Xk
5UtmviN2
kDwtDhRn
a4aMhnyE
That way, you get a different pass each time a user requests one. Perhaps that is NOT what youre loking for, but it sounded like it. =)
PPS
Forum Newbie
Posts: 2
Joined: Sun Aug 10, 2003 12:18 pm

Post by PPS »

JAM, thnks for the reply!

LOCK that what I was looking for!

The passes in the table relate to some money transactions and they are all created in advance (300000+1rows) so that 100% they are not repetetive. That could have been solved some other way, but I think that would be more secure to do this way.
1) PHP sets LOCK on the table of this passes;
2)Before issuing a next pass PHP looks for the number of the ID of that pass wich is stored in the first row (ID=1) in cell PASS;
3) retrive the next pass;
4) increase the number stored in pass where ID=1 by 1 (so that it points to the next pass)
5)UNLOCK this table;
6)Then php performs task, creating a row in another database for users with this pass and some other parametrs. (INSERT INTO tb_users (userpass, cash) VALUES ('$result',1000)) - something like this.

Then php displays some info on a generated page, showing the pass and some other info.

How to make all these task + initialization(connect etc.) in as LESS as possible calls to mysql server???
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Not sure, never dealt with lock's much, apart from MSSql servers at work. There we used subqueries, and I'm not sure that works with your (on any) version of MySQL in the same aspect.

Only thing I can think of is that don't need to select the id, the update the id based on the result. Merely:

Code: Select all

update id set id =id+1
should be enough.

Otherwise, http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html and the rest of their online manual is worth browsing.

Good luck.
Post Reply