MYSQL导航栏将类别添加到类别表但检查类别是否已存在

I made a navigation bar where I store my menus on my webpage.

I created a table for it called categories, and I managed to add a category to it via a button. I can add everything to it, but if I add something to it, I want to check if it already exists in the categories table. If it does, then echo 'already exists`. If not, insert the data.

I can't solve this. Thanks for the help.

<?php

function addCategory(){
      if(isset($_POST['submit'])){
          global $connection;
          $cat_title = $_POST['cat_title']; 

          if($cat_title == "" || empty($cat_title)){
              echo "this cannot be blank";
          } else {                            
              $query="INSERT INTO categories(cat_title)";// ADD CATEGORIES IN CATEGORIES.PHP //
              $query .= "VALUES ('$cat_title')";
              $result = mysqli_query($connection,$query);

              if(!$result){
                 die('Query failed' . mysqli_error);
              }

          }  
     }
} 

?>

CREATE UNIQUE INDEX cat_title_index ON categories (cat_title)

This made my problem solved :)

The best way to do it has a couple of steps. One changes the database itself to enforce a rule that the cat_title column in your categories table never has a duplicate.

The documentation for the first step is here, but it takes a lot of practice to read that stuff. It's worth getting used to it, though, if you plan on using MySQL for any amount of time.

For you, the statement is probably as simple as:

CREATE UNIQUE INDEX cat_title_index ON categories (cat_title)

You run that command ONCE, and it permanently modifies your database. You might have a visual database admin tool that you can use to accomplish the same thing.

Once that's done, you will never have two categories with the same title (and searching by title will be faster).

Then, when you execute your code above, how do you know if an insert was done?

It turns out that's pretty easy, too. php has a way to check what was inserted - if the answer is zero, then nothing was:

$insertId = mysqli_insert_id($connection);
if ($insertId === 0) {
    // return "already have that category" message
}

Put this after you run your insert statement and you're good to go.

you should consider making cat_title UNIQUE in the data base and then catch the resulted sql exception

or:

$result = $mysqli->query("SELECT cat_title FROM 
 categories WHERE category =" +$cat_title);
if($result->num_rows == 0) {
     // insert the new category
} else {
    echo "category exists ";
}

you can further refine the query by using 'like' instead of equal to includ misspelled inserted categories and show the user the category name to deside to insert or not. a better design is to make a function1($cat_title) to check for exact equality and return a boolean and a second function2($cat_title) to check with 'like %c%' that returns a string or null sinse the returned value can be used as boolean in PHP if statment and a thired function3($cat_title) to insert and then simply call the 3 functions respectively in a (if ... else if... else) fashion and refine it in the else block to ask the user note that you can call function 1 and 2 as the if and else if conditions and call the insertion function inside the else block and inside the if else block in the midle you would prompt the user to insert or not and make an end point to insert the value.

sent from a mobile device by a none native english speaker ...please reformat