将sql表与txt文件逐行比较，如果变量不存在则将其插入[关闭]

Question

<?php
function db_query()
    {
        $servername = "localhost";
        $username = "root";
        $password = "";
        $dbname = "single4thenight";

        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        } 

        $sql = "SELECT id, name, alias, type, parent, ordering, published FROM iutca_jomcl_locations";  //selects locations from 
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                echo "id: " . $row["id"]. " - Name: " . $row["name"]. "  alias" . $row["alias"]. "  type: " . $row["type"]. " - parent: " . $row["parent"]. " ordering " . $row["ordering"]. "published " . $row["published"]."<br>";
            }
        } else {
            echo "0 results";
        }
        $conn->close();

    }
function read_location()
    {
        $servername = "localhost";
        $username = "root";
        $password = "";
        $dbname = "single4thenight";

        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        } 

        $sql = "SELECT name FROM iutca_jomcl_locations";  //selects locations from 
        $result = $conn->query($sql);

        if ($result->num_rows > 0) 
        {
            while($row = $result->fetch_assoc()) {
            //  echo "Location : " . $row["name"]."<br>";
                $row_name = $row["name"];
                echo $row_name.'<br />';
            }

        }
        $file1 = "./location.txt";
        $lines = file($file1);
        foreach($lines as $line_num => $line)
        {
        echo $line;

        }

    }

My location.txt file contains this

Auburn Birmingham Dothan Gadsden Huntsville Mobile Montgomery Muscle Shoals Tuscaloosa

I would like to compare my sql database with txt file to make sure that i do not arealdy have variables inside. I do not want to put duplicates in side my sql i would like to know what is the easiest way to update my sql

Answer 1

You could use INSERT IGNORE INTO instead of just INSERT INTO and MySQL will then ignore duplicate entries. See the MySQL documentation for INSERT for more information. So, based on what I see in your question, your SQL would look something like:

INSERT IGNORE INTO iutca_jomcl_locations ('name') values (?)

Hope this helps! :)