Insert data from textarea in PHP to MySQL database (also skip duplicate)

Question

I am trying to bulk insert some data in PHP from a textarea form, and then prevent duplications from already existing data. I tried to code something but right now it just inserts everything that I insert in the textarea in same "value" in the column. But I want each line from the textarea separated in each and own column.

Here is what I have so far, and this works but it inserts the whole row:

<?php
include '../includes/config.php';
if (isset($_POST["data"])) {

    $_POST["ban"] = $value;
    $line_data = explode("\n", $_POST["data"]);
    foreach ($line_data as $key => $value) {
        $checkQuery  = "SELECT ip FROM blacklist WHERE ip='" . $_POST["ip"] . "'";
        $checkResult = mysqli_query($con, $checkQuery);
        if (mysqli_num_rows($checkResult) != 0){
            // do nothing 
        } else {
            $sql = "INSERT IGNORE INTO blacklist (ip) VALUES ('{$value}')";
            $result = mysqli_query($con, $sql);
        }
    }
}
?>

<form action="bulk.php" method="post"> 
<textarea type="text" name="data" placeholder="IP address"></textarea><br/>
<button type="submit" name="ban">Ban</button>
</form>

So my question is, what am I doing wrong and is there good way to handle this? If so, how?

Edit: It inserts into it's own column now, but it still duplicates. Like it's still possible insert same thing many times even if data exists in database.

that depends on how you split the ip-addresses in your text area. by newline? by semicolon? Anyway, an `explode()` would help. Then iterate through the result, and insert. And checkout [How to 'insert if not exists' in MySQL?](https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — Jeff, Dec 04 '17 at 22:08
Updated my question, it still duplicates but it works inserting now. — Martin, Dec 04 '17 at 23:21
https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — Jeff, Dec 04 '17 at 23:22
`$checkresult` doesn't say if it returns zero or more rows. It only checks if the select was successfull (to be precice: it returns a restult-set that would evaluate to loose == '1' which is kinda true). You need to check for [num_rows](http://php.net/manual/en/mysqli-result.num-rows.php) — Jeff, Dec 04 '17 at 23:24
Hmm, I tried adding mysqli_num_rows. I updated question again, still inserting duplicates. I most likely did this wrong — Martin, Dec 04 '17 at 23:37

score 0 · Answer 1 · answered Dec 04 '17 at 22:13

0

You can function explode for splitting values in field ip into lines. Save each line with ip address as separate record in your db table blacklist

answered Dec 04 '17 at 22:13

Oleksandr Matviichuk

89
5

Insert data from textarea in PHP to MySQL database (also skip duplicate)

1 Answers1