php writing to db(check if entry exists?)

[TedCopple]

I am adding new entries into a MySQL table via a php script.  The script works fine,  but I want to prevent duplicate entries.

 

How can I check if an entry already exists on the table before initiating the query to write to the table.

 

It is for a user registration type of system.  The php script rights "AccountName" and "PassWord" to the table called "Accounts".  This was my attempt to check if it exists:

 

$query = "INSERT INTO Accounts(AccountName, Password)VALUES ('".$Accn."','".$Pwwd."')";
$Check = mysql_query("SELECT * FROM Accounts");

while($row = mysql_fetch_array($Check))
{
   $Account = $row['AccountName'];
}
if($accn != Account)
{
   mysql_query($query);
}

 

Where $accn = the new accountname entered.  I am trying to compare the new AccountName(accn) that was entered with the array of all AccountNames already in the database,  and if does not already exists,  then add it.

[kenrbnsn]

All you have to do is to see if the new accountname already exists, if it doesn't insert it:

<?php
$q = "select AccountName from Accounts where AccountName = '$Accn'";
$rs = mysql_query($q);
if (mysql_num_rows($rs) == 0) { // no account exists by this name, insert it
   $q = "INSERT INTO Accounts(AccountName, Password)VALUES ('$Accn','$Pwwd')";
   $rs = mysql_query($q);
}
?>

 

BTW, I've left out any error checking, which is left as an exercise for the OP. :-)

 

Ken

[TedCopple]

All you have to do is to see if the new accountname already exists, if it doesn't insert it:

<?php
$q = "select AccountName from Accounts where AccountName = '$Accn'";
$rs = mysql_query($q);
if (mysql_num_rows($rs) == 0) { // no account exists by this name, insert it
   $q = "INSERT INTO Accounts(AccountName, Password)VALUES ('$Accn','$Pwwd')";
   $rs = mysql_query($q);
}
?>

 

BTW, I've left out any error checking, which is left as an exercise for the OP. :-)

 

Ken

 

Hey,  thanks a lot for that.  Very informative for me.  Will help a lot for later functions as well.

 

No rep system on this forum it seems so +rep anyways

 

I have never done php until yesterday so forgive me if I am little slow,  I am starting up reading as much information the subject as I can.

[mnewberry]

Similar issue I am trying to work around.

 

$q = "select AccountName from Accounts where AccountName = '$Accn'";

 

How can i check more than one field? Example: '$1','$2''; will not work.

 

Thanks in advance.

[nethnet]

Hi mnewberry,

 

To check multiple fields in an SQL SELECT statement, use the AND and/or OR operators.

 

For example:

 

SELECT * FROM `people` WHERE `firstName` = "Sam" AND `lastName` = "Jones"

SELECT * FROM `people` WHERE `location` = "USA" OR `age` > 18

[mnewberry]

Thanks for the reply but now I am thrown off more. I need to check $name, $name2, and $name3 and below is what I have but is obviously only checking one.

 

$rs_duplicate = mysql_query("select count(*) as total from users where user_name='$name' ") or die(mysql_error());

list($total) = mysql_fetch_row($rs_duplicate);

 

if ($total > 0)

{

echo "Taken";

} else {

echo "Available";

}

}

[nethnet]

Just as I said, use the OR operator.

 

SELECT count(*) AS total FROM `users` WHERE `user_name` = '$name' OR `user_name` = '$name2' OR `user_name` = '$name3'

 

That way it will match any record where `user_name` is equal to any of the three $name variables.

[kenrbnsn]

You could us the "in" operator in your query:

<?php
$rs_duplicate = mysql_query("select count(*) as total from users where user_name in ('$name','$name2','$name3') ") or die(mysql_error());
?>

 

Ken

[mnewberry]

Thanks for the help!

[mnewberry]

Everything whether or not it is or is not available shows to be unavailable.

 

<?php

include 'database.php';

foreach($_GET as $key => $value) {
$get[$key] = filter($value);
}

$user = mysql_real_escape_string($get['user']);

if(isset($get['cmd']) && $get['cmd'] == 'check') {

if(!isUserID($user)) {
echo "Invalid User ID";
exit();
}

if(empty($user) && strlen($user) <=3) {
echo "Enter 5 chars or more";
exit();
}



$rs_duplicate = mysql_query("SELECT count(*) AS total FROM `users` WHERE `cusurl` = '$cusurl' OR `cusurl2` = '$cusurl2' OR `cusurl3` = '$cusurl3' ") or die(mysql_error());
list($total) = mysql_fetch_row($rs_duplicate);

if ($total > 0)
{
echo "Not Available";
} else {
echo "Available";
}
}

?>

 

[nethnet]

Replace this line..

 

list($total) = mysql_fetch_row($rs_duplicate);

 

With this line..

 

$total = mysql_num_rows($rs_duplicate);

[mnewberry]

It is still doing it. Could it be something from the other end?

 

<a href="#" STYLE="text-decoration:none" class="addspeech" rel="#speechbubble8">User</a>:<span class="required"> <font color="#CC0000">*</font></span><br />
            <input name="cusurl" type="text" id="cusurl" class="required username" minlength="5" > 
            <span style="color:red; font: bold 12px verdana; " id="user" >
		<input name="btnAvailable" type="button" id="btnAvailable" 
		onclick='$("#checkuser").html("Please wait..."); $.get("checkuser.php",{ cmd: "check", user: $("#user").val() } ,function(data){  $("#checkcusurl").html(data); });'
		value="Check Availability"> 
		</span>

[drisate]

<?php

$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT AccountName FROM page WHERE AccountName = '$Accn'")));
if ($check){
    echo "Sorry $Accn already exist!";
}else{
    $query = "INSERT INTO Accounts(AccountName, Password)VALUES ('".$Accn."','".$Pwwd."')";
    $insert = mysql_query($query);
    echo "Account created!";
}


?>

 

the @current(@mysql_fetch_assoc(@mysql_query( part is bad code but i use i use it when i wana test only 1 column name. if $check is equal to nothing there will be warnings so the @ will delete them

 

 

[nethnet]

Where are you setting the $cusurl variables?  I don't see them being defined anywhere.

[mnewberry]

> Where are you setting the $cusurl variables?  I don't see them being defined anywhere.

Earlier in the file.

 

The first portion is the checkuser.php file and the second is on the actual registration form.

 

When I change #user

{ cmd: "check", user: $("#user").val() }

I get different results back. I am thinking I have this labeled wrong?

 

 

When set to #user it will return 'Invalid User Id'

When set to #cusurl it will return 'Not Available' even though it is.

 

Just not sure what it should be based off the current:

<?php

include 'database.php';

foreach($_GET as $key => $value) {
$get[$key] = filter($value);
}

$user = mysql_real_escape_string($get['user']);

if(isset($get['cmd']) && $get['cmd'] == 'check') {

if(!isUserID($user)) {
echo "Invalid User ID";
exit();
}

if(empty($user) && strlen($user) <=3) {
echo "Enter 5 chars or more";
exit();
}



$rs_duplicate = mysql_query("SELECT count(*) AS total FROM `users` WHERE `cusurl` = '$cusurl' OR `cusurl2` = '$cusurl2' OR `cusurl3` = '$cusurl3' ") or die(mysql_error());
list($total) = mysql_fetch_row($rs_duplicate);

if ($total > 0)
{
echo "Not Available";
} else {
echo "Available";
}
}

?>

 

[dcro2]

I haven't really read into this thread but if this is jQuery, then you're trying to get the value of the span with id='user', not the input box where I'm guessing the username goes

{ cmd: "check", user: $("#user").val() }

 

So maybe you should try this:

{ cmd: "check", user: $("#cusurl").val() }

[mnewberry]

I haven't really read into this thread but if this is jQuery, then you're trying to get the value of the span with id='user', not the input box where I'm guessing the username goes

{ cmd: "check", user: $("#user").val() }

 

So maybe you should try this:

{ cmd: "check", user: $("#cusurl").val() }

 

Yes, I have tried that.