[Resolved] Check if Record Exist and Insert If not?

[jasons61]

Ive tried alot on this heres what I want to do:
these are the variables passed on to the script that I want.
$username
$password
$firstname
$secondName
I want to check if $username exists in my "users" table in my database. If the value of $username doesn't exist in the table then I want the script to add all the variables to my table. If more explanation is needed please ask. Thanks for any help. Jason
This has been bugging me 😕

[superwormy]

Do a SELECT ... WHERE username = '$username'

Check if you got anything back with mysql_num_rows()

If no, INSERT, else... error

[jasons61]

I tried it. Not sure if my scripting is wrong though? No errors come up? Could you or someone script it for me please. Thanks for the help. Jason

[superwormy]

No, but if you post your frickin code we might be able to help you figure it out.

[jasons61]

<?php
$username="Bob";
$user = "myUsername";
$pass = "mypassword";
$db = "mydatabase";
$link = mysql_connect( "localhost", $user, $pass );
if ( ! $link )
die( "couldn't connect" );
mysql_select_db( $db, $link )
or die ( "couldn't select" );
$query = "SELECT * FROM users WHERE firstName='$username'";
$result = mysql_query($query);
if ( mysql_num_rows($result) != 0 )
{
print "error";
} else {
print "Found more than 0 records";
}

?>

the script doesn't print anything??????

[superwormy]

Try this, see what you get:

$username="Bob";
$user = "myUsername";
$pass = "mypassword";
$db = "mydatabase";

$link = mysql_connect( "localhost", $user, $pass );
if ( ! $link ) die( "couldn't connect" );

mysql_select_db( $db, $link ) or die ( "couldn't select" );

$query = "SELECT * FROM users WHERE firstName='$username'";
$result = mysql_query($query) or die (mysql_error());

$number = mysql_num_rows ($result);

print ("Number Of Rows: " . $number);

[jasons61]

yes! thanx alot. 😃

[jasons61]

<?php
$username="Bob";
$user = "myusername";
$pass = "mypassword";
$db = "mydatabase";

$link = mysql_connect( "localhost", $user, $pass );
if ( ! $link ) die( "couldn't connect" );

mysql_select_db( $db, $link ) or die ( "couldn't select" );

$query = "SELECT * FROM users WHERE firstName='$username'";
$result = mysql_query($query) or die (mysql_error());

$number = mysql_num_rows ($result);

$checkNo = "$number";

print "$checkNo";

if ( $checkNo != 0 )
{
print "actionStatus=failed";
}
else
{
$insert = INSERT INTO users ( firstName ) VALUES ( '$username');
mysql_query($insert, $link);
print ("Done inserting:" . $username);
}

?>

Why dont it work?
Oh and - does anyone no of any free PHP coders for windows. Im using notepad at the moment lol.

[superwormy]

PHPEdit

DO NOT do this:
$checkNo = "$number";

You're forcing it to be treated as a string.
$checkNo = $number;

And when you use mysql_query(), USE or die (mysql_error());
mysql_query ($insert, $link) or die (mysql_error());

[TruckStuff]

Just use a "REPLACE INTO..." query.

http://www.mysql.com/doc/en/REPLACE.html

[jasons61]

thanks suerwormy. my script at the moment is complete now. 😃

[ahundiak]

You should also consider creating a unique index on username. This prevents any possibility of getting duplicate user names when multiple users happen to try to insert the same user name at the same time.

After creating your table do something like:
CREATE UNIQUE INDEX i_username ON users(firstName);

An added benefit to this approach is that it's no longer necessary to first check to see if a record exists. Just insert the new record and then check the error_no() code to see if it worked.