Check if all values in an array exist in a database column

Admin 1
Viewed 126

I have an array of emails and I want to check if all those emails are valid by verifying with my database. I want to add the invalid emails (if any) to an empty array. I'm not too familiar with MySQL so I need some help with doing this. This is what I have so far in my PHP file:

<?php

$conn = mysqli_connect($servername, $username, $password, $db);
$conn->select_db($db);

if($conn->connect_error) {
    die("Connection failed " . $conn->connect_error);
}
echo "Connected successfully\n";

$tags = preg_split("/\,/", $_POST['tags']);
$invalidEmails = array();
$count = 0;

$result = $conn->query("SELECT mail FROM dej_colleagues");
for ($i = 0; $i < sizeof($tags); $i++) {
    $trim_brackets = trim($tags[$i], '[]');
    $trim_quotes = trim($trim_brackets, '"');
    
    while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {
        if ($trim_quotes == $row["mail"]) {
            $count += 1;
        }
        else {
            array_push($invalidEmails, $tags[i]);
        }
    }
}
if (sizeof($tags) == $count) {
    echo "good";
}
?>

My database connection was successful, but even if the emails in my tags array exist in the database, it is not detecting it. Also, the database column has 100k entries so if you know a more efficient method please let me know.

mysqli mysql php database
2 Answers

It would be more efficient to build a statement with WHERE IN to get the matching mails, and then compare the resulting arrays. You can use array_diff() to get the difference of the arrays.

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect($servername, $username, $password, $db);

echo "Connected successfully\n";

$tags = preg_split("/\,/", $_POST['tags']);
$invalidEmails = array();
$count = 0;

// modify your array how you want it
foreach ($tags as $i => $tag) {
    $trim_brackets = trim($tag, '[]');
    $trim_quotes = trim($trim_brackets, '"');
    $tags[$i] = $trim_quotes;
}

// build placeholders for WHERE IN
$in = str_repeat('?,', count($tags) - 1) . '?';
// prepare query
$stmt = $conn->prepare("SELECT mail FROM dej_colleagues WHERE mail IN ($in)");
// bind an array of values. First params is a type list.
$stmt->bind_param(str_repeat('s', count($tags)), ...$tags);
$stmt->execute();
$results = $stmt->get_result();
// fetch the matching mails into an array
$mails = [];
foreach ($results as $row) {
    $mails[] = $row['mail'];
}

// get the difference between the two arrays
$invalidEmails = array_diff($tags, $mails);

if (count($tags) === count($mails)) {
    echo "good";
}

var_dump($invalidEmails);
user_18394390

The problem is likely to be that mysqli_fetch_array will return null once there are no more rows left in the results. This means the second time that the for ($i = 0; $i < sizeof($tags); $i++) loop runs, it will not find any rows and so the while loop won't run again.

You need to get the SQL results out into a regular PHP array so you can then loop over them repeatedly without this problem.

For example:

$result = $conn->query("SELECT mail FROM dej_colleagues");
$rows = mysqli_fetch_all($result, MYSQL_ASSOC);

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;
        }
        else {
            array_push($invalidEmails, $tags[i]);
        }
    }
}

Documentation: https://www.php.net/manual/en/mysqli-result.fetch-all.php

N.B. There may be more efficient ways to search the data than this, but that's outside the scope of my answer.

user_59470430
Related