检查数组中的所有值是否都在数据库中，并存储不在新数组中的值

问题描述

我有一个看起来像这样的数组 ->["[email protected]"，"[email protected]"，"[email protected]"].我想为数据库中存在的每封电子邮件增加$ count.如果它不存在(无效)，那么我想将其推送到$ invalidEmails数组.

I have an array that looks something like this -> ["[email protected]", "[email protected]", "[email protected]"]. I want to increment $count for each email that exists in the database. If it doesn't exist (invalid), then I want to push it to the $invalidEmails array.

在那之后，我想根据原始数组中的所有电子邮件是否有效来设置$ output.如果它们全部存在于数据库中，则它们是有效的.我很乐意为此提供一些帮助，因为我不确定如何从这里开始.目前并非在所有情况下都有效，例如，如果第一封电子邮件有效但第二封电子邮件无效.

After that, I want to set my $output according to whether all the emails in the original array are valid or not. They're valid if all of them exist in the database. I'd appreciate some help with this as I'm not sure how to go about it from here. It doesn't work for all cases right now, for example if first email is valid but second one is invalid.

这是我到目前为止所拥有的:

This is what I have so far:

$result = $conn->query("SELECT mail FROM dej_colleagues");
$rows = mysqli_fetch_all($result, MYSQL_ASSOC);
$tags = preg_split("/\,/", $_POST['tags']);
$invalidEmails = array();
$count = 0;

for ($i = 0; $i < sizeof($tags); $i++) {
    $trim_brackets = trim($tags[$i], '[]');
    $trim_quotes = trim($trim_brackets, '"');

    foreach($rows as $row) {
        if ($trim_quotes == $row["mail"]) {
            $count += 1;
            break;
        }
    }
    if ($count == 0) {
        array_push($invalidEmails, $tags[$i]);
    }
}
$output = array();
if (sizeof($tags) == $count) {
    $output = array("validity => "valid emails");
}
else {
    $output = array("validity" => "invalid emails", "emails" => $invalidEmails;
}
echo json_encode($output);

推荐答案

您的代码似乎有些费解，因此，与其调试它，不如先从一个更加侧重的查询开始，然后从那里开始工作.

Your code seems convoluted, so rather than debug it I started with a more focussed query and worked from there.

基本上，查询会向数据库询问出现在 $ tags 数组中的电子邮件的列表，然后使用 array_diff()查找出现在$ tags中的所有电子邮件，但不在数据库中.

Basically, the query asks the database for a list of emails that appear in your $tags array, then uses array_diff() to find any that appear in $tags, but not in the database.

从那里您可以直接产生输出.

From there you can produce your output directly.

ini_set('display_errors',1);

$mysqli = new mysqli('mysql.lv.local','userName', 'userPassword','schemaName' );

// Assuming the input is a string and not an array, json_decode it.
$tags = '["[email protected]", "[email protected]", "[email protected]","[email protected]"]';
$tags = json_decode($tags);
// switch everything to lower case
$tags = array_map('strtolower', $tags);

// Build and prepare a query with placeholders. Note conversion to lower case
$sql = 'select distinct lower(`mail`) from `emails` where lower(`mail`) in (?'.str_repeat(',?', count($tags)-1).')';
//echo $sql;
$stmt = $mysqli->prepare($sql);

// Bind the values from $tags to the query
$stmt->bind_param(str_repeat('s', count($tags)), ...$tags);
// Execute
$stmt->execute();
// Bind a variable for the result
$stmt->bind_result($email);
// Retrieve the emails in to $dbMails
$dbMails = [];
while ($stmt->fetch()) {
    $dbMails[] = $email;
}
//var_dump($dbMails);
// Anything that's in $tags but not in $dbMails is invalid
$absentEmails = array_diff($tags, $dbMails);
//var_dump($absentEmails);

if ($absentEmails) {
    $op= ["validity"=>"Invalid enails", 'emails'=>array_values($absentEmails)];
} else {
    $op= ["validity"=>"Valid enails"];
}
echo json_encode($op);

这篇关于检查数组中的所有值是否都在数据库中，并存储不在新数组中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！