1

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.

Improve this question
0

2 Answers 2

Reset to default
1

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Is there another way for me to unpack instead of doing ...$tags ? My php version is not 5.6
@viktor You have got to be kidding me. Can you upgrade to something more sensible? It's irresponsible to be running such an old version. It's really difficult to work with such an old version. You are hurting yourself. This should help, but please upgrade as soon as possible. stackoverflow.com/a/42373791/1839439
@viktor Also, try to move away from mysqli. Use PDO instead. It's much easier, especially considering on how old of PHP version you are. Start here phpdelusions.net/pdo & websitebeaver.com/…
0

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.

Improve this answer

4 Comments

Beware that this will be very slow when you are fetching 100k records. You might also run out of memory depending on circumstances. Better to fetch only the data you need
If I add mysqli_free_result($result) at then end after my for loop, would that help with freeing up some memory?
@viktor No, mysqli_free_result is absolutely useless. Either try my solution which is optimized or use unbuffered queries which are a nightmare.
@Dharman absolutely, I referred to that in the last part of my answer. I simply sought to point out the logic error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.