独特的code基础上，MySQL表唯一的电子邮件地址？ [英] Unique code based on unique email address in mysql table?
问题描述
我有一个MySQL表，将存储用户的电子邮件地址（每个都是独特的，是主域）和时间戳。
我已经添加了另一列名为'unique_ code'（VARCHAR（64），utf8_uni code_ci）。
我会非常AP preciate援助的;
a）产生5位字母数字code，即：5ABH6
b）检查所有行的unique_ code'栏，以确保它是唯一的，否则重新生成并再次检查
C）将唯一生成5位字母数字code到'unique_ code'列，对应于刚输入的电子邮件地址。
D）在屏幕上显示code。
C $Ç我必须把什么$在哪里？
我当前的PHP如下：
要求包括/将connect.php;
$味精='';
如果（$ _ POST [电子邮件]）{
//要求与AJAX：
$阿贾克斯=（$ _ SERVER ['HTTP_X_REQUESTED_WITH'] =='XMLHtt prequest'）;
尝试{
如果（！filter_input（INPUT_POST，电子邮件，FILTER_VALIDATE_EMAIL））{
抛出新的异常（无效的电子邮件！'）;
}
$ mysqli-＆GT;查询（INSERT INTO coming_soon_emails
设置电子邮件='$ mysqli-＆GT; real_escape_string（$ _ POST [电子邮件]）。'）;
如果（$ mysqli-＆GT;！affected_rows = 1）{
抛出新的异常（你已经通知名单上。'）;
}
如果（$阿贾克斯）{
死亡（{地位：1}）;
}
$味精=谢谢你！
}
赶上（异常$ E）{
如果（$阿贾克斯）{
死亡（json_en code（阵列（'错误'=＆GT; $ E-＆GT;的getMessage（））））;
}
$味精= $ E-＆GT;的getMessage（）;
}
}
希望这有助于：
）我做了一件非常相似，这个我在那里生成唯一codeS这些都被用来作为网址。我写这个生成C $ CS的$：
私有函数_generate code（$长度= 5）{
$字符='bcdfghjkmnpqrstvwxyz';
$字符串='';
为（$ i = 0; $ I＆LT; $长度; $ I ++）{
。$字符串= $字[兰特（0，strlen的（$字符） - 1）]。
}
返回$串;
}
$字符是允许字符的字符串。我们选择要删除的元音，使没有机会让不受欢迎的词汇:)你可以改变这一点。还有更简单的方法来写，但我们需要的东西很具体。
您会使用这样的：
$ unique_ code = _generate code（）;
B）对于这个只是包装在SELECT语句中检查是否有独特的code INSERT语句中。如果code存在，那么产生另一个code，然后再试一次。你可以做到这一点的方法之一是（注：这不是测试，如果你碰巧进入一个情况下，你已经用完了所有的codeS就可能会被无限循环）和你或许应该增加一个检查，以确保INSERT是成功的）：
$ unique_ code =;
$插入=假;
//不断循环，直到我们插入一条记录
而（！$插入）{
//生成一个code
$ unique_ code = _generate code（）;
//检查它是否存在
如果（$结果= mysqli-＆GT;查询（选择unique_ code从coming_soon_emails WHERE unique_ code ='$ unique_ code'））{
//检查无记录存在
如果（$ result-＆GT; NUM_ROWS == 0）{
//创建新纪录
$ mysqli-＆GT;'。$ mysqli-＆GT; real_escape_string（$ _ POST查询（INSERT INTO coming_soon_emails（电子邮件，unique_ code）VALUES（[电子邮件]。），$ unique_ code'））;
//设置插入到真实的内线循环
$插入= TRUE;
//关闭结果对象
$ result-＆GT;关闭（）;
}
} 其他 {
//退出，如果我们不能检查数据库
死亡（东西出了毛病，选择'）;
}
}
//输出code
回声$ unique_ code;
C）要插入的唯一code只需添加这其中$ unique_ code是变量从功能上面指定的返回值的插入语句：
$ mysqli-＆GT;'。$ mysqli-＆GT; real_escape_string（$ _ POST [查询（INSERT INTO coming_soon_emails（电子邮件，unique_ code）VALUES（电子邮件 ]），$ unique_ code'））。
D）只是附和你指定为code中的变量，例如：
回声$ unique_ code;
I have a mysql table which will store users email addresses (each is unique and is the primary field) and a timestamp.
I have added another column called 'unique_code' (varchar(64), utf8_unicode_ci).
What I would very much appreciate assistance with is;
a) Generating a 5 digit alphanumeric code, ie: 5ABH6
b) Check all rows the 'unique_code' column to ensure it is unique, otherwise re-generate and check again
c) Insert the uniquely generated 5 digit alphanumeric code into 'unique_code' column, corresponding to the email address just entered.
d) display the code on screen.
What code must I put and where?
My current php is as follows:
require "includes/connect.php";
$msg = '';
if($_POST['email']){
// Requested with AJAX:
$ajax = ($_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest');
try{
if(!filter_input(INPUT_POST,'email',FILTER_VALIDATE_EMAIL)){
throw new Exception('Invalid Email!');
}
$mysqli->query("INSERT INTO coming_soon_emails
SET email='".$mysqli->real_escape_string($_POST['email'])."'");
if($mysqli->affected_rows != 1){
throw new Exception('You are already on the notification list.');
}
if($ajax){
die('{"status":1}');
}
$msg = "Thank you!";
}
catch (Exception $e){
if($ajax){
die(json_encode(array('error'=>$e->getMessage())));
}
$msg = $e->getMessage();
}
}
Hope this helps:
a) I did something very similar to this where I was generating unique codes which were to be used as URLs. I wrote this to generate the codes:
private function _generateCode($length = 5) {
$characters = 'bcdfghjkmnpqrstvwxyz';
$string = '';
for ($i = 0; $i < $length; $i++) {
$string .= $characters[rand(0, strlen($characters) - 1)];
}
return $string;
}
$characters is a string of "allowed" characters. We chose to remove the vowels so that there was no chance of making unwanted words :) You could change this. There are simpler ways to write it but we needed something quite specific.
You would use it like this:
$unique_code = _generateCode();
b) For this just wrap your insert statement in a select statement check for that unique code. If the code exists then generate another code and try again. One way you could do this is (NB: this isn't tested and it may be susceptible to an infinite loop if you happen to get into a situation where you've used up all your codes ;) And you should probably add a check to make sure the INSERT was succesful):
$unique_code = "";
$inserted = false;
// Keep looping until we've inserted a record
while(!$inserted) {
// Generate a code
$unique_code = _generateCode();
// Check if it exists
if ($result = mysqli->query("SELECT unique_code FROM coming_soon_emails WHERE unique_code = '$unique_code'")) {
// Check no record exists
if ($result->num_rows == 0) {
// Create new record
$mysqli->query("INSERT INTO coming_soon_emails (email,unique_code) VALUES ('" . $mysqli->real_escape_string($_POST['email']) . "','$unique_code')");
// Set inserted to true to ext loop
$inserted = true;
// Close the result object
$result->close();
}
} else {
// Quit if we can't check the database
die('Something went wrong with select');
}
}
// Output the code
echo $unique_code;
c) To insert the unique code just add this to your insert statement where $unique_code is the variable assigned the return value from the function above:
$mysqli->query("INSERT INTO coming_soon_emails (email,unique_code) VALUES ('".$mysqli->real_escape_string($_POST['email'])."','$unique_code')");
d) Just echo the variable you assigned the code to e.g:
echo $unique_code;
这篇关于独特的code基础上，MySQL表唯一的电子邮件地址？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
