[SOLVED] Check if record exists

deepermethod · October 21, 2008

I am somewhat new to php/mysql so bear with me.

I have a registration form when a new user signs up. I have a field that a new member must fill in. The field is a "member referral" field. What I need to know is how can I check my database to verify the current members username exists? And if so continue on, if not give an error.

I have the opposite code where an error is given if an email is already in use, I just need to do the opposite. If the record exists then continue on.

Sorry of this seems confusing.

deepermethod · October 21, 2008

Well, to help visualize here is the code that I have to make sure there isn't a duplicate email address when registering:

$checke = "SELECT * FROM $tbl_members WHERE email = ".quote_smart($email)."";
$check_e = @mysql_query($checke,$connection) or die("Couldn't execute email check query.");
while ($row = mysql_fetch_array($check_e)) {
$ch_email = $row['email'];
}
if(isset($ch_email)) {
$email_err = "That email address has already been used!";
$error = "1";
}

What I want to do is check to see if a username exists and send an error if the username does not exist for a referral.

I hoped I explained it well enough.

Thanks.

CroNiX · October 21, 2008

You're doing more than you have to.

<?php
$checke = "SELECT * FROM $tbl_members WHERE email = ".quote_smart($email)."";
$check_e = @mysql_query($checke,$connection) or die("Couldn't execute email check query.");
if(mysql_num_rows($check_e) > 0){  //tells you how many results were returned from your query
    echo 'email already in use';
} else {
    echo 'email is not used';
}

So for your question, you would just

<?php
if(mysql_num_rows($check_e) > 0){
//...continue
} else {
    echo 'We are sorry, that user name does not exist.';
}

deepermethod · October 21, 2008

Sorry if I don't understand. This script is not mine, I am modifying it. Scroll down to where I posted "//here is where I want to verify that the new member has entered a valid current members username//.

Here is the whole register.inc.php script:

<?php
if(basename($_SERVER[php_SELF])=="register.inc.php")
{
   header("Location: /index.php");
   exit;
}


if($_POST['action'] == "do_reg") {
//check input for errors
$login_length = strlen($_POST['login']);
$pass_length = strlen($_POST['pass1']);
$login = $_POST['login'];
$pass1 = $_POST['pass1'];
$pass2 = $_POST['pass2'];
$email = $_POST['email'];
$referral = $_POST['referral'];
$newsletter = $_POST['newsletter'];
$displayname = $_POST['displayname'];
$agreestoterms = $_POST['agreestoterms'];

if("$pass1" != "$pass2") {
$pass_err = "Your passwords do not match!";
$error = "1";
}
if(empty($referral)) {
$dn_err = "You MUST have a current lounge{in} member referral!";
$error = "1";
}
if(empty($displayname)) {
$dn_err = "You did not enter a Display Name!";
$error = "1";
}
if(empty($agreestoterms)) {
$terms_err = "You must agree to the terms of service!";
$error = "1";
}
if($login_length < 3) {
$login_err = "Your username must be at least 3 characters!";
$error = "1";
}
if($pass_length < 3) {
$pass_err = "Your password must be at least 3 charachters!";
$error = "1";
}
if(ereg("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@([a-zA-Z0-9-]+\.)+([a-zA-Z]{2,3})$", $email)) {
$okmail="1";
}
if($okmail != "1") {
$email_err = "Your email address is not properly formatted!";
$error = "1";
}
if (ereg("^[a-zA-Z0-9]+$",$login)) {
$oklogin="1";
}
if($oklogin != "1") {
$login_err = "You username may contain only letters and numbers!";
$error = "1";
}
if (ereg("^[a-zA-Z0-9]+$",$pass1)) {
$okpass="1";
}
if($okpass != "1") {
$pass_err = "Your password may contain only letters and numbers!";
$error = "1";
}
if (ereg("^[a-zA-Z0-9]+$",$displayname)) {
$okdisplay="1";
}
if($okdisplay != "1") {
$dn_err = "Your Display Name may contain only letters and numbers!";
$error = "1";
}
$connection = @mysql_connect("$db_host", "$db_user", "$db_pass") or die("Couldn't connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");
$checkl = "SELECT * FROM $tbl_members WHERE login = ".quote_smart($login)."";
$check_l = @mysql_query($checkl,$connection) or die("Couldn't execute login check query.");
while ($row = mysql_fetch_array($check_l)) {
$ch_login = $row['login'];
}
if(isset($ch_login)) {
$login_err = "That username is already taken!";
$error = "1";
}
$check2 = "SELECT * FROM $tbl_restricted WHERE r_login = ".quote_smart($login)."";
$check_2 = @mysql_query($check2,$connection) or die("Couldn't execute login check query.");
while ($row = mysql_fetch_array($check_2)) {
$r_login = $row['r_login'];
}
if(isset($r_login)) {
$login_err = "That username is reserved!";
$error = "1";
}

[size=5]here is where I want to verify that the new member has entered a valid current members username[/size]

$checke = "SELECT * FROM $tbl_members WHERE email = ".quote_smart($email)."";
$check_e = @mysql_query($checke,$connection) or die("Couldn't execute email check query.");
while ($row = mysql_fetch_array($check_e)) {
$ch_email = $row['email'];
}
if(isset($ch_email)) {
$email_err = "That email address has already been used!";
$error = "1";
}
if(empty($newsletter)) $newsletter = "no";
if(!$error) {
//input is ok, register new member!

$ipaddr = $_SERVER['REMOTE_ADDR'];

$sql = "INSERT INTO $tbl_members (login, enabled, password, email, displayname, newsletter, ipaddr, referral)
VALUES (
".quote_smart($login).",
".quote_smart($autoenable).",
".quote_smart($pass1).",
".quote_smart($email).",
".quote_smart($displayname).",
".quote_smart($newsletter).",
".quote_smart($ipaddr).",
".quote_smart($referral).",
)";
$result = @mysql_query($sql,$connection) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

if($mailonnew == "yes") {
$to = "$site_email";
$subject = "New $sitename Member!";
$from_mail = "$adminemail";
$message = "Dear Admin,\n\n";
$message .= "You have a new member with the login: $login.\n\n";
$message .= "$siteurl";
$headers = "From: $from_mail\r\n";
$headers .= "Reply-To: $from_mail\r\n";
$headers .= "X-Mailer: phpProfiles";
mail($to, $subject, $message, $headers);
}
include("./include/welcome_msg.inc.php");
include("./include/reg_success.inc.php");
exit;
}
}

$ipaddr = $_SERVER['REMOTE_ADDR'];

$connection = @mysql_connect("$db_host", "$db_user", "$db_pass") or die("Couldn't connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");

$sql = "SELECT * FROM $tbl_banned WHERE bip = \"$ipaddr\"";

$result = @mysql_query($sql,$connection) or die("Couldn't execute ban lookup query.");
$num=mysql_num_rows($result);
if($num > 0) {
while ($row = mysql_fetch_array($result)) {
$bcomment = $row['bcomment'];
}
if(empty($bcomment)) {
$bcomment = "no reason given.";
}
echo "<p>Sorry, you cannot register at this time. You have been banned because:</p>
<p align=\"center\"><i>$bcomment</i></p>";
include("./include/footer.inc.php");
exit;
}

if (!$_POST) { ?>

deepermethod · October 21, 2008

Any other help would be appreciated.

Thanks

deepermethod · October 21, 2008

any takers?

CroNiX · October 21, 2008

I can't guarantee anything as I dont know your db field names or know what your quote_smart() function does, but you can try this where your comment is in the code:

<?php
$checkref = "SELECT login FROM $tbl_members WHERE login = ".quote_smart($referral)."";
$check_ref = @mysql_query($check2,$connection) or die("Couldn't execute referral check query.");
if(mysql_num_rows($check_ref) < 1)){
    $login_err = "The referral name you used is not in the database.";
    $error = "1";
}

deepermethod · October 22, 2008

Thanks for the help. I keep getting an error that says "Couldn't execute referral check query."

If it helps I am posting the whole file. Thanks again.

<?php
if(basename($_SERVER[php_SELF])=="register.inc.php")
{
   header("Location: /index.php");
   exit;
}


if($_POST['action'] == "do_reg") {
//check input for errors
$login_length = strlen($_POST['login']);
$pass_length = strlen($_POST['pass1']);
$login = $_POST['login'];
$pass1 = $_POST['pass1'];
$pass2 = $_POST['pass2'];
$email = $_POST['email'];
$newsletter = $_POST['newsletter'];
$displayname = $_POST['displayname'];
$deferral = $_POST['deferral'];
$agreestoterms = $_POST['agreestoterms'];

if("$pass1" != "$pass2") {
$pass_err = "Your passwords do not match!";
$error = "1";
}
if(empty($displayname)) {
$dn_err = "You did not enter a Display Name!";
$error = "1";
}
if(empty($agreestoterms)) {
$terms_err = "You must agree to the terms of service!";
$error = "1";
}
if($login_length < 3) {
$login_err = "Your username must be at least 3 characters!";
$error = "1";
}
if($pass_length < 3) {
$pass_err = "Your password must be at least 3 charachters!";
$error = "1";
}
if(ereg("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@([a-zA-Z0-9-]+\.)+([a-zA-Z]{2,3})$", $email)) {
$okmail="1";
}
if($okmail != "1") {
$email_err = "Your email address is not properly formatted!";
$error = "1";
}
if (ereg("^[a-zA-Z0-9]+$",$login)) {
$oklogin="1";
}
if($oklogin != "1") {
$login_err = "You username may contain only letters and numbers!";
$error = "1";
}
if (ereg("^[a-zA-Z0-9]+$",$pass1)) {
$okpass="1";
}
if($okpass != "1") {
$pass_err = "Your password may contain only letters and numbers!";
$error = "1";
}
if (ereg("^[a-zA-Z0-9]+$",$displayname)) {
$okdisplay="1";
}
if($okdisplay != "1") {
$dn_err = "Your Display Name may contain only letters and numbers!";
$error = "1";
}
$connection = @mysql_connect("$db_host", "$db_user", "$db_pass") or die("Couldn't connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");
$checkl = "SELECT * FROM $tbl_members WHERE login = ".quote_smart($login)."";
$check_l = @mysql_query($checkl,$connection) or die("Couldn't execute login check query.");
while ($row = mysql_fetch_array($check_l)) {
$ch_login = $row['login'];
}
if(isset($ch_login)) {
$login_err = "That username is already taken!";
$error = "1";
}
$check2 = "SELECT * FROM $tbl_restricted WHERE r_login = ".quote_smart($login)."";
$check_2 = @mysql_query($check2,$connection) or die("Couldn't execute login check query.");
while ($row = mysql_fetch_array($check_2)) {
$r_login = $row['r_login'];
}
if(isset($r_login)) {
$login_err = "That username is reserved!";
$error = "1";
}

$checkref = "SELECT * FROM $tbl_profiles WHERE referral = ".quote_smart($referral)."";
$check_ref = @mysql_query($checkref,$connection) or die("Couldn't execute referral check query.");
if(mysql_num_rows($check_ref) < 1){
    $referral_err = "The referral name you used is not in the database.";
    $error = "1";
}

$checke = "SELECT * FROM $tbl_members WHERE email = ".quote_smart($email)."";
$check_e = @mysql_query($checke,$connection) or die("Couldn't execute email check query.");
while ($row = mysql_fetch_array($check_e)) {
$ch_email = $row['email'];
}
if(isset($ch_email)) {
$email_err = "That email address has already been used!";
$error = "1";
}
if(empty($newsletter)) $newsletter = "no";
if(!$error) {
//input is ok, register new member!

$ipaddr = $_SERVER['REMOTE_ADDR'];

$sql = "INSERT INTO $tbl_members (login, enabled, password, email, displayname, newsletter, ipaddr, referral)
VALUES (
".quote_smart($login).",
".quote_smart($autoenable).",
".quote_smart($pass1).",
".quote_smart($email).",
".quote_smart($displayname).",
".quote_smart($newsletter).",
".quote_smart($ipaddr).",
".quote_smart($referral)."
)";
$result = @mysql_query($sql,$connection) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

if($mailonnew == "yes") {
$to = "$site_email";
$subject = "New $sitename Member!";
$from_mail = "$adminemail";
$message = "Dear Admin,\n\n";
$message .= "You have a new member with the login: $login.\n\n";
$message .= "$siteurl";
$headers = "From: $from_mail\r\n";
$headers .= "Reply-To: $from_mail\r\n";
$headers .= "X-Mailer: phpProfiles";
mail($to, $subject, $message, $headers);
}
include("./include/welcome_msg.inc.php");
include("./include/reg_success.inc.php");
exit;
}
}

$ipaddr = $_SERVER['REMOTE_ADDR'];

$connection = @mysql_connect("$db_host", "$db_user", "$db_pass") or die("Couldn't connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");

$sql = "SELECT * FROM $tbl_banned WHERE bip = \"$ipaddr\"";

$result = @mysql_query($sql,$connection) or die("Couldn't execute ban lookup query.");
$num=mysql_num_rows($result);
if($num > 0) {
while ($row = mysql_fetch_array($result)) {
$bcomment = $row['bcomment'];
}
if(empty($bcomment)) {
$bcomment = "no reason given.";
}
echo "<p>Sorry, you cannot register at this time. You have been banned because:</p>
<p align=\"center\"><i>$bcomment</i></p>";
include("./include/footer.inc.php");
exit;
}

if (!$_POST) { ?>
            <form method="POST">
		<table align="center" cellpadding="4" cellspacing="0" width="60%">
                            <tr>
                                <td>
                                        <p><?php echo "$txt_reg1"; ?></p>

                                </td>
                            </tr>
                                <tr>
                                <td align="center">
                                        <p><img src="securimage_show.php?sid=<?php echo md5(uniqid(time())); ?>"></p>

                                </td>
                                </tr>
                                <tr>
                                <td align="center">
                                        <p><input type="text" tabindex="1" name="code" id="text"></p>

                                </td>
                                </tr>
                                <tr>
                                <td align="center">
                                        <p><input type="hidden" name="show" value="register">
                                        <input type="submit" value="Verify code" class="button"></p>

                                </td>
                                </tr>
                                <tr>
                                <td align="center">
                                        <p> <a href="register.php">I can't
                                        read the code.</a></p>

                                </td>
                                </tr>
                        </table>
                        </form>
<?php
} else { //form is posted
  include("securimage.php");
  $img = new securimage();
  $valid = $img->check($_POST['code']);
if($_POST['action'] == "do_reg") {
$valid = "true";
}
  if($valid == FALSE) {
  echo "<center>Sorry, the code you entered was invalid.
  <a href=\"register.php\">Go back</a> to try again.</center>";
  include("./include/footer.inc.php");
  exit;
  } else {
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
<table align="center" cellpadding="4" cellspacing="0">
<?php
if($error) {
?>
<tr>
	<td colspan="3"><p><span class="required">
<?php
if($login_err) { echo "$login_err<br>"; }
if($pass_err) { echo "$pass_err<br>"; }
if($email_err) { echo "$email_err<br>"; }
if($dn_err) { echo "$dn_err<br>"; }
if($referral_err) { echo "$referral_err<br>"; }
if($terms_err) { echo "$terms_err<br>"; }
?>
	</span></p></td>
</tr>
<?php
}
?>
    <tr>
        <td valign="top">
            <p>Choose Username:</p>
        </td>
        <td valign="top">
            <p><input type="text" name="login" maxlength="20" size="20" id="text<?php if($login_err) { echo "error"; } ?>" value="<?php echo $_POST['login'] ?>"></p>
        </td>
        <td valign="top">
            <p><span class="help">Your Username may be 3-20 characters, letters
            and or numbers only.</span></p>
        </td>
    </tr>
    <tr>
        <td valign="top">
            <p>Password:</p>
        </td>
        <td valign="top">
            <p><input type="password" name="pass1" maxlength="20" size="20" id="text<?php if($pass_err) { echo "error"; } ?>"></p>
        </td>
        <td valign="top">
            <p><span class="help">Your Password may be 3-20 characters, letters
            and or numbers only.</span></p>
        </td>
    </tr>
    <tr>
        <td valign="top">
            <p>Repeat Password:</p>
        </td>
        <td valign="top">
            <p><input type="password" name="pass2" maxlength="20" size="20" id="text<?php if($pass_err) { echo "error"; } ?>"></p>
        </td>
        <td valign="top">
            <p><span class="help">Please verify your password.</span></p>
        </td>
    </tr>
    <tr>
        <td valign="top">
            <p>Email Address:</p>
        </td>
        <td valign="top">
            <p><input type="text" name="email" maxlength="150" size="25" id="text<?php if($email_err) { echo "error"; } ?>" value="<?php echo $_POST['email'] ?>"></p>
        </td>
        <td valign="top">
            <p><span class="help">Your email address is used to retrieve lost
            password. It is not displayed to the public.</span></p>
        </td>
    </tr>
    <tr>
        <td valign="top">
            <p>Display Name:</p>
        </td>
        <td valign="top">
            <p><input type="text" name="displayname" maxlength="25" size="25" id="text<?php if($dn_err) { echo "error"; } ?>" value="<?php echo $_POST['displayname'] ?>"></p>
        </td>
        <td valign="top">
            <p><span class="help">This is your nickname or the name you want
            the system to refer to you by. No spaces!</span></p>
        </td>
    </tr>
    <tr>
    <tr>
        <td valign="top">
            <p>Member Referral:</p>
        </td>
        <td valign="top">
            <p><input type="text" name="referral" maxlength="25" size="25" id="text<?php if($referral_err) { echo "error"; } ?>" value="<?php echo $_POST['referral'] ?>"></p>
        </td>
        <td valign="top">
            <p><span class="help">This is your nickname or the name you want
            the system to refer to you by. No spaces!</span></p>
        </td>
    </tr>
    <tr>
        <td valign="top" colspan="3">
            <p> <input type="checkbox" name="agreestoterms" value="agreestoterms">
            I have read the <a href="aup.php" target="_blank">Acceptable Use
            Policy</a> and agree to the terms.</p>
        </td>
    </tr>
    <tr>
        <td valign="top" colspan="3">
            <p> <input type="checkbox" name="newsletter" value="yes" checked>Subscribe
            to Newsletter updates (occasional updates regarding the site).</p>
        </td>
    </tr>

    <tr>
        <td valign="top" colspan="3">
            <p>Patience! Registration will take a few moments.</p>
        </td>
    </tr>

    <tr>
        <td valign="top">
            <p> </p>
        </td>
        <td valign="top">
            <p><input type="hidden" name="action" value="do_reg">
            <input type="submit" name="register" value="Register!"></p>
        </td>
        <td valign="top">
            <p> </p>
        </td>
    </tr>
</table>
</form>
<? }} ?>

CroNiX · October 22, 2008

No that doesn't help much.

Change this line:

$check_ref = @mysql_query($check2,$connection) or die("Couldn't execute referral check query.");

to this:

$check_ref = @mysql_query($check2,$connection) or die("Couldn't execute referral check query.<br>".mysql_error());

and post the exact error please

deepermethod · October 22, 2008

Wow! That error message really helped! I had the wrong column in the SELECT. I got the referral registration to work perfectly.

You're the best CroNix!

CroNiX · October 22, 2008

Always good to be able to see the errors

deepermethod · October 24, 2008

Now here is part 2 to my registration problem. I got the referral check to work by username. Now I also want to verify a referral code. The thing is, I want to verify that the referral code matches the code from a particular member by name.

Here's how the referral registration works:

1. Current member sends a "tell-a-friend" email to a friend to join the website. The "tell-a-friend" form has a randomly generated string and stores the current members name and the generated string that was sent to friend in mysql.

2. The friend goes to website and registers. When registering he must enter the current members name along with the randomly generated string from the "tell-a-friend" form.

What I want to do is have the registration form check the database for the current member and the random string and make sure they match or are equal. If they do, registration complete. If they don't match, registration fails.

I want to search to make sure my_name (Tom) and code (5gT8Cx) exist then make sure they match.

Example:

+--------+
| Field  | 
+--------------+
| my_name = Tom  
| code = 5gT8Cx       
| fr_emaile   
+--------------+

Help please. Where you at CroNiX?

Thanks

CroNiX · October 24, 2008

something like this, but substitute your table name, $refname and $code with the variables from the form.

$sql="SELECT my_name FROM your_table_name WHERE my_name = '" . quote_smart($refname) . "' AND code = '" . quote_smart($code) . "'";

$check_refer = mysql_query($sql,$connection) or die("Couldn't execute referral check query.<br>".mysql_error());
if(mysql_num_rows($check_refer) > 0) {
  //there is a match
}

Sign In

[SOLVED] Check if record exists

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information