How to check items in the database and add it if new?

Question

I have a page where the user want to add new category to the database, I want to check if the category is already added before in the database or not,if it is new one add it to the database, it is working fine but it adds it many times(equal to the data in the table) i know it is because of foreach,how can it be done?and also how to alert to the user that it is already exists?

Php code:

  <?php
    include_once "connect.php";

    $stmt ="SELECT distinct Category_Name FROM Categories";

    foreach ($conn->query($stmt) as $row) 
    {
        if ($row['Category_Name'] != $_POST["CatName"])
        {
            $sql ="INSERT INTO Categories (Category_Name) VALUES (:CatName)";

            $result=$conn->prepare($sql);
            $result->bindparam(':CatName', $_POST["CatName"], PDO::PARAM_INT);
            $result->execute();
        }
        else
        {
            return false;
        }
    }

?>

Javascript Code:

function AddNewCategory()
{
  var CatName=document.getElementById("CatNametxt").value;
  $.ajax({
            type:"POST",
            url:"add_category.php",
            data:'CatName=' + CatName,
            success: function(data)
            {
                alert("Category Added Successfully!");
            }
        })
}

Edit1 I changed it to the following and it worked fine,but how to alert to the user that this category is inserted before?

$stmt ="SELECT * FROM Categories WHERE Category_Name='".$_POST["CatName"]."'";
$queryresult = $conn->query($stmt)->fetchAll(PDO::FETCH_ASSOC);
if (count($queryresult) == 0)
        {
            $sql ="INSERT INTO Categories (Category_Name) VALUES (:CatName)";
            $result=$conn->prepare($sql);
            $result->bindparam(':CatName', $_POST["CatName"], PDO::PARAM_INT);
            $result->execute();
        }

Please add a tag for the specific database your using, if MySQL: [Best way to avoid duplicate entry into mysql database](http://stackoverflow.com/questions/2219786/best-way-to-avoid-duplicate-entry-into-mysql-database) — Alex K., Feb 02 '17 at 13:00
before insert statement check if any record exist with the same name if yes than make a response accordingly there is no need of two ajax call — Curiousdev, Feb 02 '17 at 13:10

Titus · Accepted Answer · 2017-02-02T13:22:36.280

Try this:

<?php
    include_once "connect.php";

    $stmt ="SELECT distinct Category_Name FROM Categories";
    $exists = false;
    foreach ($conn->query($stmt) as $row) {
        if ($row['Category_Name'] == $_POST["CatName"]) {
            $exists = true;
        }
    }
    if(!$exists) { // The category doesn't exist.
        $sql ="INSERT INTO Categories (Category_Name) VALUES (:CatName)";

        $result=$conn->prepare($sql);
        $result->bindparam(':CatName', $_POST["CatName"], PDO::PARAM_INT);
        $result->execute();
    }
    return $exists;
?>

The idea is to go through all the categories in order to determine if the one defined by the user exists or not.

As it is now. Your code inserts the new category each time a category from the database doesn't have the same name.

You can also do something like this:

$result=$conn->prepare("SELECT COUNT(Category_Name) as total FROM Categories WHERE Category_Name = (:CatName)");
$result->bindparam(':CatName', $_POST["CatName"], PDO::PARAM_STR);
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $exists = $row["total"] > 0;
}

I don't know PHP that well, there my be some syntax errors in this code.

This would work but there are much better ways that dont involve reading an entire table. What happens if a category is added by another user between the select & insert. — Alex K., Feb 02 '17 at 13:02
@AlexK. I was just trying to make the OP's approach work. Making that column unique as in the answer to the question you've mentioned will be a better approach. Even searching for the category in the database will be better. — Titus, Feb 02 '17 at 13:07
why not just search for the category the user is inserting then if found dont insert else insert, that way you won't have to read entire table — Masivuye Cokile, Feb 02 '17 at 13:15

score 1 · Answer 2 · answered Feb 02 '17 at 13:03

The logic is incorrect. You are finding all existing categories and then inserting one row for every one that does not match the category you give. You need to add a where clause to the SQL to select only rows that match your category and then it will either return one row when the category exists or none when not. The remaining code should then only insert wher row count is zero.

score 0 · Answer 3 · answered Feb 02 '17 at 13:03

0

You can make Category_Name in your data base As index unique So the database can't inserte a duplicated mane. and in your inerte SQl add ON DUPLICATE KEY

Exemple

answered Feb 02 '17 at 13:03

Barkati.med

620
5
11

How to check items in the database and add it if new?

3 Answers3