How can I query the mysql database for a variable, if exists create another variable, if not insert?

Question

say I have a variable

$id = mt_rand();

how can I query the mysql database to see if the variable exists in the row id, if it does exist then change the variable $id, once the variable is unique to all other stored ids, then insert it into the database?

Thanks you guys.

explain "once the variable is unique to all other stored ids" — Deele
– Deele, Commented Mar 31, 2011 at 21:00
say I have a number 10994566, I need to check it with other numbers in that particular row, if it unique then insert it, if not generate another number. — Beginner PHPer
– Beginner PHPer, Commented Mar 31, 2011 at 21:03

Deele · Accepted Answer · 2011-03-31 21:10:11Z

1

$con = mysql_connect("<host>","<login>","<pass>");
if ($con) {
    mysql_select_db('<schemata>', $con);
    $found = false;
    while (!$found) {
        $idIamSearching = mt_rand();
        $query = mysql_query("SELECT count(*) FROM <table> WHERE <idColumnName>='".$idIamSearching."'");
        $result = mysql_fetch_row($query);
        if ($result[0] > 0) {
            mysql_query("INSERT INTO <table> (<column>) VALUES ('".$idIamSearching."')");
            $found = true;
        }
    }
    mysql_close($con);
}

Your description is hard to understand, so, this is something that could give you pointers...

edited Mar 31, 2011 at 21:10

answered Mar 31, 2011 at 21:03

Deele

3,6792 gold badges35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Beginner PHPer Over a year ago

Thanks for your comment. Basically what I am doing is I am creating a little online game from scratch as a project. When a user signs up to the site to play I will generate a random ID for their character. Now seeing as mt_rand isn't random, I need to check if the number that is generated isn't already someone's id. If it is already someones id, generate another id until it is unique to there account. If that makes sense :)

Beginner PHPer Over a year ago

And auto increment is not an option, I know the benefits of it, but this problem got me stumped :/

Beginner PHPer Over a year ago

Can I ask what this bit of code is? if ($result[0] > 0) { what does the [0] do? Thanks for your help much appreciated!

Wh1T3h4Ck5 Over a year ago

I don't see any loop in this code... It might check if $id exists in database and if does, generate new variable, if doesn't... INSERT. So if your $id exists, you have to generate new $id and query table again.

Deele Over a year ago

@Beginner PHPer $result is fetched as array, [0] is array item number 0 - first array item, that is count(*) from that query.

|

Jase · Accepted Answer · 2011-03-31 20:57:04Z

0

'SELECT COUNT(*) as count from table where row_id="'.$variable.'" LIMIT 1'

make sure to escape the variable if it's user input or if it's going to have more than alphanumeric characters

then fetch the row and check if count is 1 or greater than 0

if one, then it exists and try again (in a loop)

although, auto increment on the id field would allow you to avoid this step

answered Mar 31, 2011 at 20:57

Jase

5993 silver badges9 bronze badges

Comments

S.. · Accepted Answer · 2011-03-31 21:05:14Z

0

$bExists = 0;
while(!$bExists){
    // Randomly generate id variable
    $result = mysql_query("SELECT * FROM table WHERE id=$id");
    if($result){
        if(mysql_num_rows($result) > 0){
        $bExists = 1;
    } else {
        // Insert into database
        $bExists = 1;
    }
}

1 Randomly generate id variable 2 Query database for it 2.1 Result? exit 2.2 No result? Insert

answered Mar 31, 2011 at 21:05

S..

5,8083 gold badges40 silver badges44 bronze badges

Collectives™ on Stack Overflow

How can I query the mysql database for a variable, if exists create another variable, if not insert?

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related