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

<?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

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! :)

First we read file content to the $content variable

$content = file('mytxt.txt')

As you posted, your file contains words separated with space (if not, skip this and make variable $words contain values you need) so we need to split content, to get each word as array item

$words = explode(" ", $content);

Finally, inserting value and checking if there is one like that existing in DB

foreach($words as $word)
{
  $sql = "INSERT  iutca_jomcl_locations (name) 
  SELECT  $word
  WHERE   NOT EXISTS 
          (   SELECT  1
              FROM    tblSoftwareTitles 
              WHERE   name = $word
          );"
  $result = $conn->query($sql);
}

iutca_jomcl_locations - table name
name - column to insert (also checking for unique values using this column)

I used This Code and it Worked for me

 foreach($lines as $line_num => $line)
        {
            $line = $line;
            //echo $line;
            $sql = "SELECT ordering, name FROM iutca_jomcl_locations WHERE name='$line'";
            $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"]. "<br>";
        echo "Found Locations: " . $row["name"]." The ordering Number Is " . $row["ordering"]."<br>";
        $ordering =$row["ordering"];
        }
        } else {

                if($ordering >=0)
                {
                    $count = $ordering;
                    //echo "0 results";
                    $lowerCase = strtolower($line);
                    $sql = "INSERT INTO iutca_jomcl_locations (name, alias , parent, published,ordering)
                    VALUES ('$line','$lowerCase','$parent','1','$count')";


                    $count = $count + 1;
                    if ($conn->query($sql) === TRUE) {
                    echo "New record created successfully <br />";
                    } else {
                    echo "Error: " . $sql . "<br>" . $conn->error;
                    }
                }
        }

        }