停止向数据库PHP SQLITE添加重复条目

问题描述：

I have created a PHP form which adds to a database that I have created in PHP, however, I am trying to add a function which will stop the user from adding the same fruit into the database how would I try to do this as I have been trying to do it for a while thanks.

As you can see below the PHP script works fine by adding the variable's to the database however when it comes to implementing a check to make sure the fruit name does not match one from the database already I am struggling.

 <?php
    //SQLite Database test query
    $db=sqlite_open("fruitshop.db"); 

    if(isset( $_POST['fruit']) && strcmp($_POST['fruit'],"") != 0 ){ //Adds to Database

        $item = sqlite_escape_string($_POST["fruit"]);

        $number=$_POST['number'];


        sqlite_query($db,"INSERT INTO fruit (fruit) VALUES ('$item')");
        sqlite_query($db,"INSERT INTO stock (Number) VALUES ($number)");

        $query = "SELECT * from stock, fruit WHERE stock.Item = fruit.id AND fruit.fruit = '$item', 'fruit' = '{$item}'";
        $result=sqlite_query($db, $query);





        echo "<table border=1>";
        echo "<tr><th>Fruit</th><th>Qty</th>";
        echo "<h2>". "Newly added Fruit"."</h2>";
        while($row=sqlite_fetch_array($result,SQLITE_ASSOC ))
        {
            echo "<tr>";
             echo "<td>" . $row['fruit.fruit'] . "</td><td>" . $row['stock.Number'] . "</td>"; 
           echo "</tr>";
        }
        echo "</table>";
    echo "<h2>". "Show All Fruits"."</h2>";

    echo "<table border=1>
";



    //NOte the use of SQLITE_ASSOC
    echo "</br>
";


    $result=sqlite_query($db,"SELECT * from stock, fruit WHERE stock.Item = fruit.ID"); //Shows Databse

    echo "<th>Fruit</th><th>Qty</th>
";

    while($row=sqlite_fetch_array($result,SQLITE_ASSOC))
    {
        echo "<tr>
";
        echo "<td>" . $row['fruit.fruit'] . "</td>
"; 
        echo "<td>" . $row['stock.Number'] . "</td>
"; 
       echo "</tr>
";
    }
    echo "</table>
";




    }
    sqlite_close($db);


    ?>

    <html>
    <h2> Add Fruits to Database </h2>
    <form name="CheckFruit" action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
    Fruit
    <input type="text" name="fruit" />
    <br>
    Stock
    <input type="number" name="number" />
    <br>

    <input type="submit" value="Submit" />
    </form> 



    </html>

答

You can use my updated code. Didn't get to try the code though but it should work. Also note how I used empty instead of strcmp. That's a more elegant PHP code.

<?php
    //SQLite Database test query
    $db=sqlite_open("fruitshop.db"); 

    if(isset( $_POST['fruit']) && !empty($_POST['fruit']) ){ //Adds to Database

        $item = sqlite_escape_string($_POST["fruit"]);

        $number = $_POST['number'];


        $test = sqlite_query($db, "SELECT * FROM fruit WHERE (fruit = '$item')");

        if(sqlite_num_rows($test) == 0){

            sqlite_query($db,"INSERT INTO fruit (fruit) VALUES ('$item')");
            sqlite_query($db,"INSERT INTO stock (Number) VALUES ($number)");

        } else {
            // Just in case you want this too.
            // echo "This database already contains a fruit called {$_POST['fruit]'}";
        }

        $query = "SELECT * from stock, fruit WHERE stock.Item = fruit.id AND fruit.fruit = '$item', 'fruit' = '{$item}'";
        $result=sqlite_query($db, $query);





        echo "<table border=1>";
        echo "<tr><th>Fruit</th><th>Qty</th>";
        echo "<h2>". "Newly added Fruit"."</h2>";
        while($row=sqlite_fetch_array($result,SQLITE_ASSOC ))
        {
            echo "<tr>";
             echo "<td>" . $row['fruit.fruit'] . "</td><td>" . $row['stock.Number'] . "</td>"; 
           echo "</tr>";
        }
        echo "</table>";
    echo "<h2>". "Show All Fruits"."</h2>";

    echo "<table border=1>
";



    //NOte the use of SQLITE_ASSOC
    echo "</br>
";


    $result=sqlite_query($db,"SELECT * from stock, fruit WHERE stock.Item = fruit.ID"); //Shows Databse

    echo "<th>Fruit</th><th>Qty</th>
";

    while($row=sqlite_fetch_array($result,SQLITE_ASSOC))
    {
        echo "<tr>
";
        echo "<td>" . $row['fruit.fruit'] . "</td>
"; 
        echo "<td>" . $row['stock.Number'] . "</td>
"; 
       echo "</tr>
";
    }
    echo "</table>
";




    }
    sqlite_close($db);


    ?>

    <html>
    <h2> Add Fruits to Database </h2>
    <form name="CheckFruit" action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
    Fruit
    <input type="text" name="fruit" />
    <br>
    Stock
    <input type="number" name="number" />
    <br>

    <input type="submit" value="Submit" />
    </form> 



    </html>

答

You could declare the fruit column as UNIQUE in your column schema, so the database will reject a duplicate value without further controls on your side.

If instead you want to check if the value is already present in your PHP code, you can do a query for that value and check if rows are returned. If rows are returned, a value is already present and you can handle that situation before doing your inserts

$query = "SELECT * from fruit WHERE fruit.fruit = '$item', 'fruit' = '{$item}'";
$result=sqlite_query($db, $query);
if (sqlite_num_rows($result) === 0) {
    sqlite_query($db,"INSERT INTO fruit (fruit) VALUES ('$item')");
    sqlite_query($db,"INSERT INTO stock (Number) VALUES ($number)");
} else {
    // Value is already present
}

Note: I've never used SQLITE, so i hope the syntax is correct

停止向数据库PHP SQLITE添加重复条目

相关推荐