Trying to create entries into Database via form using PHP

Question

I just don't know where I'm going wrong. Tried with multiple tables and was unsuccessful.

<?php

if(isset($_POST['submit'])){
    // create sql
    $catName = $_POST['category_name'];
    $sql = "INSERT INTO Categories(c_name) VALUES($catName)";
    

    }
if($catName->query($sql) === TRUE) {
    echo "success";
} else {
    echo "error" . $sql . "<br>" . $catName->error;
}
    
?>

<?php include ('templates/footer.php')?>

I get no error, but also no success. It just goes blank and no entry in the table is made.

Answer 1

Problem

There are a few problems with your code...

1. You don't connect to a database
2. You're trying to use a mysqli|pdo method on a string (i.e. not a mysqli|pdo)
3. Using variables directly inside of queries is bad practice and leaves you open to SQL injection
   • Additionally, in this case, your variable appears to be a string so needs to be in quotes even if you were to use it directly in the query
4. Having two separate if statements would mean that even if this code worked otherwise sometimes you would run a query with not SQL statement
5. You need to enable error reporting
   • Currently you're getting a BLANK page because there is an uncaught error. If you enable error reporting then you will get a message saying what caused the issue
6. Setting $catName as you have could result in Notice messages appearing in your log file

Solution

The key things to remember are:

1. Use Prepared Statements for variables
2. Enable error reporting
   • Display & log in dev environments
   • Hide & log on production

Code

// Enable error reporting in PHP; making errors output to page
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

// Database credentials:
//   You need to change these to your DB / DB User
$db_host = '127.0.0.1';
$db_user = 'db_username';
$db_pass = 'db_password';
$db_name = 'db_name';

// Database connection
//   - Setting error reporting mode in options
$pdo = new \pdo(
    "mysql:host={$db_host};dbname={$db_name}",
    $db_user,
    $db_pass,
    [
        \PDO::ATTR_ERRMODE          => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_EMULATE_PREPARES => FALSE
    ]
);

// Initialise the variable from the form
//   ?? null   => if the variable doesn't exist then the value will be null
$catName = $_POST["category_name"] ?? null;

// Check to see if the variable exists (and isn't false)
// Warning:
//    If false equivalent values can be entered then you should
//    use a different condition (e.g. `!empty($catName)` )
if ($catName) {

    // The SQL statement with ? as a placeholder for the
    // variable we want to insert
    $sql   = "INSERT INTO categories (c_name) VALUES (?)";  

    $query = $pdo->prepare($sql); // Prepare the query
    $query->execute([$catName]);  // Run the query; passing in the variable to bind

    // Ternary logic to check if "rows were inserted" and echo an appropriate
    // "success" or "failure" message
    echo $query->rowCount() ?
        "Success" :
        "Error, something went wrong!";
}

Code, no comments

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

$db_host = '127.0.0.1';
$db_user = 'db_username';
$db_pass = 'db_password';
$db_name = 'db_name';

$pdo = new \pdo(
    "mysql:host={$db_host};dbname={$db_name}",
    $db_user,
    $db_pass,
    [
        \PDO::ATTR_ERRMODE          => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_EMULATE_PREPARES => FALSE
    ]
);


$catName = $_POST["category_name"] ?? null;

if ($catName) {
    $sql   = "INSERT INTO categories (c_name) VALUES (?)";
    $query = $pdo->prepare($sql);
    $query->execute([$catName]);

    echo $query->rowCount() ?
        "Success" :
        "Error, something went wrong!";
}

Answer 2

My main issue with this was not in the PHP, even though some of the answers above have been tremendously helpful for improving my code.

Turns out that the issue was in the form. After redoing the whole thing, it suddenly worked.

Thanks for all the answers.