我正在尝试获取在我的数据库表中注册的公司/客户的多个字段中列出的电子邮件，以便在使用注册的公司/客户电子邮件在我的网站上注册时进行验证。我目前有一个用于公司 （tbl_companies） 的表和一个用于客户 （tbl_clients） 的表，我通过我的管理面板存储电子邮件、电子邮件 1 和 email2 字段中列出的电子邮件。一旦列出的电子邮件存储在任一表中，公司/客户就会通过company_signup表单注册为他们列出的电子邮件，该表单会向他们发送一封带有验证链接的电子邮件，以验证他们对公司/客户管理面板的访问权限修改他们的个人资料。
我遇到的问题是，在两个表中列出公司/客户的所有电子邮件后，company_signup表单仅看到电子邮件字段中tbl_companies（电子邮件）和tbl_clients（电子邮件）的电子邮件。我希望在company_signup表单检查正在验证注册和验证的电子邮件时，tbl_companies和tbl_clients中看到电子邮件、电子邮件 1 和 email2 字段。
到目前为止，我已经尝试了 SQL 函数的 SELECT *、UNION、JOIN 和 WHERE 来尝试将tbl_companies和tbl_clients合并在一起，使用它们调用两个表中多个字段的能力来检查它们。
我有一个类中调用的函数.php该函数包含在管理面板的company_form.php client_form.php和注册表单company_signup.php中。
这是我的类.php页面中的代码
public function get_email_artist($email){
$this->sql = "SELECT * FROM tbl_music_artists WHERE email ='{$email}'
OR email1 ='{$email}'
OR email2 ='{$email}'";
$this->data = $this->fetch_row_assoc($this->sql);
if(!empty($this->data))
return $this->data;
else{
return false;
}
}
public function get_email_company($email){
$this->sql = "SELECT * FROM tbl_music_companies WHERE email ='{$email}'
OR email1 ='{$email}'
OR email2 ='{$email}'";
$this->data = $this->fetch_row_assoc($this->sql);
if(!empty($this->data))
return $this->data;
else{
return false;
}
}
public function get_company_email($email){
$this->sql = "SELECT * FROM login WHERE email ='{$email}'";
$this->data = $this->fetch_row_assoc($this->sql);
print_r($this->data);exit;
if(!empty($this->data)){
return $this->data;
}
else{
$sql = "(SELECT email, email1, email2 FROM tbl_music_artists WHERE email ='{$email}'
OR email1 ='{$email}'
OR email2 ='{$email}')
UNION
(SELECT email, email1, email2 FROM tbl_music_companies WHERE email ='{$email}'
OR email1 ='{$email}'
OR email2 ='{$email}')
";
$data = $this->fetch_row_assoc($sql);
if(!empty($data)){
return false;
}
return true;
}
} public function register_company($inputs)
{
if(is_array($inputs)){
// $pwdHasher = new PasswordHash(8, FALSE);
// $hash is what you would store in your database
// $hash = $pwdHasher->HashPassword( $_POST['com_password'] );
$hash = base64_encode($_POST['com_password']);
$uname = preg_replace('/@.*?$/', '', $_POST['com_email']);
$uname .= rand();
$input_array = array(
'email' => trim($_POST['com_email']),
'u_type' => 'company',
'password' => $hash,
'username' => $uname,
'name ' => ucwords($_POST['com_name']),
'phone' => $_POST['com_phone'],
'city ' => $_POST['com_city'],
'country' => $_POST['com_country'],
'website' => $_POST['com_url'],
'gender' => $_POST['com_gender'],
'security_question' => $_POST['com_quest'],
'security_answer' => $_POST['com_ans'],
'status' => 0,
);
$data = $this->get_email_artist(trim($_POST['com_email']));
if($data) {
$this->sendRegisterEmailCompany(array($data[email],$data[email1],$data[email2]), ucwords($_POST['com_name']));
return $this->insert($input_array, 'login');
}
else {
$data = $this->get_email_company(trim($_POST['com_email']));
if($data) {
$this->sendRegisterEmailCompany(array($data[email],$data[email1],$data[email2]), ucwords($_POST['com_name']));
return $this->insert($input_array, 'login');
}
else{
return 'invalid input';
}
}
}
else{
return 'invalid input';
}
}
public function sendRegisterEmailCompany($email, $name)
{
// ini_set("SMTP","smtp.vianet.com.np");
// ini_set("smtp_port","25");
$url = 'http://'.$_SERVER['HTTP_HOST'].dirname($_SERVER['PHP_SELF']) . '/company_validate.php?identifier=' . base64_encode($email);
$message = '<html><body>';
$message .= '<h1>World Music Listing</h1>';
$message .= '<table rules="all" style="border-color: #666;" cellpadding="10">';
$message .= "<tr style='background: #eee;'><td>Dear {$name},<br /> You have signed up successfully in the wml guide directory.<br />Before we can proceed please <strong>confirm</strong> your email address. <a href='$url'>Click here</a> OR copy below url and paste into your browser</td></tr>";
$message .= "<tr style='background: #eee;'><td>$url</td></tr>";
$message .= "<tr style='background: #eee;'><td>With Regards, <br />World Music Listing</td></tr>";
$message .= "</table>";
$message .= "</body></html>";
$to = $email;
$subject = 'Company Sign up successful notification- WML Guide';
$headers = "From: " . $email . "'r'n";
$headers .= "Reply-To: ". $email . "'r'n";
$headers .= "MIME-Version: 1.0'r'n";
$headers .= "Content-Type: text/html; charset=ISO-8859-1'r'n";
mail($to, $subject, $message, $headers);
}这是我的 ajax 中的代码.php
//////// Do not Edit below /////////
try {
$dbo = new PDO('mysql:host='.$host_name.';dbname='.$database, $username, $password);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
// Select all artists (clients) and order by name //
$sql = "SELECT email, email1, email2
FROM tbl_music_artists
WHERE email ='{$com_email}' OR email1 = '{$com_email}' OR email2 = '{$com_email}'
UNION
SELECT email, email1, email2
FROM tbl_music_companies
WHERE email ='{$com_email}' OR email1 = '{$com_email}' OR email2 = '{$com_email}'";
$data = $this->fetch_row_assoc($sql);
if(!empty($data)){
$valid = true;
echo json_encode($valid);
}
else{
echo json_encode($valid);
}
};这是我company_signup页面的代码
<div class="tab-content">
<div id="page-heading">
<h1>World Music Listing Company Registration</h1>
<hr />
</div>
<div id="tab1" class="tab active">
<?php if(isset($_SESSION['error'])) { ?>
<div class="alert alert-error"><?php echo $_SESSION['error']; unset($_SESSION['error']); ?></div>
<?php } ?>
<form class="form-style-1" id="register" method="POST" action="signupcompanycontroller.php">
<ul class="form-style-1">
<li class="bottom"><label>Account Details</label></li>
<li>
<label>Email <span class="required">*</span></label>
<input type="email" name="com_email" class="field-long" placeholder="enter your email address" size="50" required />
</li>
<li>
<label>Password <span class="required">*</span></label>
<input type="password" name="com_password" class="field-long" placeholder="enter password" size="50" required id="password" />
</li>
<li>
<label>Confirm Password <span class="required">*</span></label>
<input type="password" name="com_conpassword" class="field-long" placeholder="enter confirm password" size="50" required />
</li>
<li class="bottom"><label>Personal Details</label></li>
<li>
<label>Full Name <span class="required">*</span></label>
<input type="text" name="com_name" class="field-long" placeholder="enter your full name" size="50" required />
</li>
<li>
<label>Phone <span class="required">*</span></label>
<input type="text" name="com_phone" class="field-long" placeholder="enter your phone number" size="50" required />
</li>
<!-- <li>
<label>Street Address</label>
<input type="text" name="com_address" class="field-long" placeholder="enter your Street Address" size="50" />
</li> -->
<li>
<label>City <span class="required">*</span></label>
<input type="text" name="com_city" class="field-long" placeholder="enter your city name" size="50" required />
</li>
<li>
<label>Country <span class="required">*</span></label>
<select name="com_country" class="field-divided" required>
<option value="">Select One</option>
<?php
$country_list = country();
foreach ($country_list as $key => $value) {
if(!empty($value))
echo '<option>' . $value . '</option>';
}
?>
</select>
</li>
<li>
<label>Website</label>
<input type="url" name="com_url" class="field-long" placeholder="enter website url" size="50" />
</li>
<li>
<label>Gender</label>
<select name="com_gender" class="field-divided">
<option value="">Select One</option>
<option>Male</option>
<option>Female</option>
</select>
</li>
<li class="bottom"><label>Security</label></li>
<li>
<label>Security Question <span class="required">*</span></label>
<select name="com_quest" class="field-long" required>
<option value="">Select One</option>
<option value="What is the name of your favorite pet?">What is the name of your favorite pet?</option>
<option value="What is your preferred musical genre?">What is your preferred musical genre?</option>
<option value="What is the street number of the house you grew up in">What is the street number of the house you grew up in?</option>
<option value="What time of the day were you born?">What time of the day were you born?</option>
<option value="What is the name of your favorite childhood friend?">What is the name of your favorite childhood friend?</option>
<option value="What is the name of the company of your first job?">What is the name of the company of your first job?</option>
<option value="What is the middle name of your oldest sibling?">What is the middle name of your oldest sibling?</option>
<option value="What is the middle name of your oldest child?">What is the middle name of your oldest child?</option>
<option value="What was the last name of your third grade teacher?">What was the last name of your third grade teacher?</option>
<option value="What was your childhood nickname?">What was your childhood nickname?</option>
<option value="What is your spouse’s mother’s maiden name?">What is your spouse’s mother’s maiden name?</option>
<option value="What is your mother’s maiden name?">What is your mother’s maiden name?</option>
<option value="What was your high school mascot?">What was your high school mascot?</option>
</select>
</li>
<li>
<label>Answer</label>
<input type="text" name="com_ans" class="field-long" placeholder="enter your answer" size="50" required />
</li>
<li class="bottom"><label>Terms and Mailing</label></li>
<li>
<label class="term"><input type="checkbox" name="com_terms" value="1" required class="condition"> <span class="required">*</span> <span id="terms">I accept the Terms and Conditions</span></label>
<!-- <label><input type="checkbox" name="com_offer" value="1"> I want to receive personalized offers by your site</label>
<label><input type="checkbox" name="com_offer_partner" value="1"> Allow partners to send me personalized offers and related services</label> -->
</li>
<li>
<label> </label>
<input type="submit" value="Register" id="form_submit" />
</li>
</ul>
</form>
</div>
</div>
</div>
</div>
<div id="termscondition" style="display: none;" title="Terms and Conditions">
A. By using or visiting the World Music Listing website or any World Music Listing products, software, data feeds, including but not limited to its music/entertainment directory/list of contacts, and services provided to you on, from, or through the World Music Listing website (collectively the "Service") you signify your agreement to (1) these terms and conditions (the "Terms of Service"), (2) World Music Listing's Privacy Policy, found at http://www.wmlmusicguide.com/terms.php and incorporated herein by reference, and (3) World Music Listing's Community Guidelines, found at http://www.wmlmusicguide.com/terms.php and also incorporated herein by reference. If you do not agree to any of these terms, the World Music Listing Privacy Policy, or the Community Guidelines, please do not use the Service.
<a href="terms.php" target="_blank">Please read more</a>
</div>
<!-- end content-outer......END -->
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<script src="public/js/jquery.validate.min.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<script>
$().ready(function(){
$('#register').validate({
rules: {
com_email: {
required: true,
email: true,
remote: 'ajax_company.php'
},
com_password: {
minlength: 6,
required: true
},
com_conpassword: {
equalTo : '#password;'
},
com_name: {
minlength: 3,
required: true
},
com_phone: {
required: true
},
com_city: {
required: true
},
com_country: {
required: true
},
com_quest: {
required: true
},
com_ans: {
required: true
},
com_terms: {
required: true
}
},
messages: {
com_email: {
remote: 'Entered email address not found.'
}
},
errorElement: 'span',
errorPlacement: function (error, element) {
if (element.attr('type') == 'checkbox') {
error.insertAfter($('.term'));
} else {
error.insertAfter(element);
}
}
});
$('.condition').click(function () {
if ($(this).is(':checked')) {
$('#termscondition').dialog({
modal: true,
width: 600,
buttons: {
Ok: function() {
$( this ).dialog('close');
}
}
});
} else {
$('#termscondition').dialog('close');
}
});
});
</script>问题似乎是您只针对第一个电子邮件字段进行选择。如果要检查所有电子邮件字段，则需要更新WHERE条件，如下所示：
$sql = "SELECT email, email1, email2
FROM tbl_music_artists
WHERE email ='{$com_email}' OR email1 = '{$com_email}' OR email2 = '{$com_email}'
UNION
SELECT email, email1, email2
FROM tbl_music_companies
WHERE email ='{$com_email}' OR email1 = '{$com_email}' OR email2 = '{$com_email}'
编辑：
此函数：
$data = $this->get_email_artist(trim($_POST['com_email']));
必须返回所有电子邮件地址。
然后这个函数需要通过电子邮件发送所有地址：
$this->sendRegisterEmailCompany(trim($_POST['com_email']), ucwords($_POST['com_name']));
目前它只是发送到 $_POST['com_email']。这取决于该函数，但您应该能够向其传递电子邮件地址数组，即：
$this->sendRegisterEmailCompany(array($data['email1'],$data['email2'],$data['email3']), ucwords($_POST['com_name']));