Database checking

[madcrazy1]

php5.2, mysql server5.02: if record exists,append random digit to end of information in rows column then add to database

 

If user mike already exists and another mike is trying to create a user id

I would like to keep the old mike record like it is, then add a new mike record, but add a unique digit to the end of the name mike before the record gets added to the database as a new row.

 

Thank you kindly!

[MrXHellboy]

1) Check if the name already exists by a select query...

2) Append a string to the username

Something like this

$ID = mysql_query("SELECT id FROM members WHERE name = '$name' ");

if ($ID)
{
$name = $name.'suffix';
  mysql_query("INSERT INTO members (name) values ('$name')"); 
}

[RussellReal]

You could probably use triggers to catch the insert, check if a "Mike" already exists, and then update the inserting row before it actually is inserted into the database..

 

But you see, without a trigger, you could PROBABLY use INSERT ON DUPLICATE KEY, but what that does is UPDATES the OLD row.. which means you'd be messing with the old row and TOTALLY IGNORING the new row which SHOULD be inserted..

 

you could ALSO do it the easiest possible way, like this, but its gonna look like crap for any people that look over your code later

<?php
$tryingName = 'Mike';
$originalName = 'Mike';
while (1) {
$qry = mysql_query("INSERT IGNORE INTO users (name) VALUES('$tryingName')");
if (!mysql_num_rows($qry)) { $tryingName = $originalName . rand(1000,9999); }
else { break; }
}
// there should be a unique row in the database now 
?>

 

 

NOTE: My code above will ONLY work if `name` is a unique index

[madcrazy1]

Sorry, I don't like the first answer, it is too abstract for me.

Thwswcond answeris a bit closer to what i'm looking for but i am not sure what is meant by "unique index" if anything I want the code executed on non-uniqueness

all in all thanks for the help but other solutions or additions to existing are welcome

 

[madcrazy1]

Sorry, actually, i do like the first answer very much, it took a couple looks at it to decifer it but i will like to add the random function that answer 2 has instead of arbitrary "suffix" good work! Will now try to implement and report back with result

[RussellReal]

Sorry, I don't like the first answer, it is too abstract for me.

Thwswcond answeris a bit closer to what i'm looking for but i am not sure what is meant by "unique index" if anything I want the code executed on non-uniqueness

all in all thanks for the help but other solutions or additions to existing are welcome

the best way to handle it is with my example..

 

the reason being, the first reply's code will generate a random number, attach it, then try and insert it.. if it fails to insert, it has no fallback functionality..

 

the code I gave you will KEEP TRYING to insert until it is successful..

 

a unique index is simple to achieve, you go into your PHPMYADMIN, and add the unique index to that field..

 

its better to index your fields anyway, makes your queries more efficient.

[madcrazy1]

well i just saw your post, thanks for the quick reply but i was trying to get this to work, if not i will try your way:

 

$p7=$userinput;
$ID = mysql_query("SELECT pid FROM ".$users6." WHERE row_namex = '$p7' ");
if ($ID){
$name = $p7.rand(0,9);
}else{
$name = $p7;
}

 

everything seems to be working except when i try to add the same name twice, it doesnt put the random number next to($p7) it, is that strange?

[RussellReal]

well i just saw your post, thanks for the quick reply but i was trying to get this to work, if not i will try your way:

 

$p7=$userinput;
$ID = mysql_query("SELECT pid FROM ".$users6." WHERE row_namex = '$p7' ");
if ($ID){
$name = $p7.rand(0,9);
}else{
$name = $p7;
}

 

everything seems to be working except when i try to add the same name twice, it doesnt put the random number next to($p7) it, is that strange?

 

because of the reason I told you... if it conflicts it will just fail.. there is no failsafe..

[madcrazy1]

 

I would like it to work this way if possible. All ive been getting is blank db entries, ive eliminated the rand til i can get this to work, is this possible?

i had a hard time trying to integrate/format into existing code the $tryingName example you gave before.

 

$l1="larry";

$RunThisQuery = "SELECT namx FROM mytable WHERE namx='$l1'";
$result = $connector->query($RunThisQuery);
$bbb=$row['memn'];

if($bbb==$l1){
$name = "fromdb";
}else{
$name = $l1;
}

 

 

[madcrazy1]

using insert ignore now with unique undex but cant get rand to work. 

have a nice day