PHP MySQL while loop

Question

Why doesn't this code work?

It creates a random number, if it exists in database then it generates a new one until it can insert into the unique key, if not then it inserts straight away into database.

$id;
    do {
    $id = mt_rand(1, 10);
    "SELECT count(*) FROM accounts WHERE id = '$id'";
    } while (count != 0);

    $sql = "INSERT INTO accounts (id, username, email, password, sex)
    VALUES ('$id', '$username', '$email', '$password', '$sex')";

The reason the mt_rand is low for testing purposes.

No it is not, the purpose of my script is purposefull enough to need it working. — phpabuse
– phpabuse, Commented Apr 2, 2011 at 3:44
Check my solution then :-? and tell me if it works as intended. — Khez
– Khez, Commented Apr 2, 2011 at 3:50

UltraInstinct · Accepted Answer · 2011-04-02 03:49:38Z

That is because, you are not really firing a mySQL query. You just have the query string as such. You must pass the string the mysql_query(..). Retrieve the resource, and then check out the final number of rows.

Method #1: (modifying your code)

$id;$count;
do {
    $id = mt_rand(1, 10);
    $res = mysql_query("SELECT count(*) as c FROM accounts WHERE id = '$id'");
    $row = mysql_fetch_array($res);
    $count = $row['c'];
} while ($count != 0);

$sql = "INSERT INTO accounts ...";
//..

Method #2:

Modify your MySQL table, use an auto_increment. Create a table this way

create table myTABLE (
    id integer aut_increment,
    username varchar(128),
    email varchar(128),
    ..
    primary key(id)
);

Demian Brecht · Accepted Answer · 2011-04-02 03:44:31Z

1

Why are you going about generating an ID using this method? Why wouldn't you just use an auto-incrementing ID?

CREATE TABLE accounts (
     id INT NOT NULL AUTO_INCREMENT,
)

Generating an ID using using mt_rand doesn't make a whole lot of sense and will be incredibly expensive with the more accounts that you get.

answered Apr 2, 2011 at 3:44

Demian Brecht

21.4k5 gold badges44 silver badges47 bronze badges

Comments

cwallenpoole · Accepted Answer · 2011-04-02 03:45:19Z

0

Several things

It is not using mysql_query.
It is not fetching the result of the query (which doesn't exist)
count is a built in PHP method, not a number.
You should not be having that many round trips to the database (it is a very bad idea to generate a random identifier that way)

answered Apr 2, 2011 at 3:45

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Comments

Khez · Accepted Answer · 2011-04-02 03:57:00Z

0

Erm... there seems to be some mild issues in that code:

$id;
do {
    $id = mt_rand(1, 10);
    $c=mysql_query('SELECT id FROM accounts WHERE id = '.$id.' LIMIT 1;');
}while(mysql_num_rows($c)!=0);

P.S. You should consider looking into: uniqid in the PHP manual and auto-incrementing an sql field

edited Apr 2, 2011 at 3:57

answered Apr 2, 2011 at 3:45

Khez

10.3k2 gold badges35 silver badges53 bronze badges

1 Comment

Paul Creasey Over a year ago

this query always returns 1 row, the limit and the mysql_num_rows are pointless

Collectives™ on Stack Overflow

PHP MySQL while loop

4 Answers 4

Comments

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Related