Input text upload to MySQL DB

Question

I am uploading and selecting code from my MySQL DB and I have a feeling some of it could be written in a much shorter way but I don't know how. Can someone tell me if this is clean code or if it's too much?

<?php

error_reporting(E_ALL);
ini_set('display_errors', 1);

include 'config/connect.php';
$con = new mysqli(...$dbCredentials);

if (isset($_POST['add_execise'])) {
    # code...

    $create_exercise = $con->prepare("INSERT INTO exercises (exercise_name, difficulty) VALUES (?, ?)");
    $create_exercise->bind_param("ss", $_POST['exercise_creation'], $_POST['difficulty']);
    $create_exercise->execute();
}

if (isset($_POST['beginner'])) {
    # code...
    $get_exercises = $con->prepare("SELECT exercise_name, difficulty FROM exercises WHERE difficulty = ?");
    $get_exercises->bind_param("s", $_POST['beginner']);
    $get_exercises->execute();
    $get_exercises->store_result();
    $num_of_rows = $get_exercises->num_rows;
    $get_exercises->bind_result($exercise_selected, $difficulty);
}

if (isset($_POST['intermediate'])) {
    # code...
    $get_exercises = $con->prepare("SELECT exercise_name, difficulty FROM exercises WHERE difficulty = ?");
    $get_exercises->bind_param("s", $_POST['intermediate']);
    $get_exercises->execute();
    $get_exercises->store_result();
    $num_of_rows = $get_exercises->num_rows;
    $get_exercises->bind_result($exercise_selected, $difficulty);
}

if (isset($_POST['advanced'])) {
    # code...
    $get_exercises = $con->prepare("SELECT exercise_name, difficulty FROM exercises WHERE difficulty = ?");
    $get_exercises->bind_param("s", $_POST['advanced']);
    $get_exercises->execute();
    $get_exercises->store_result();
    $num_of_rows = $get_exercises->num_rows;
    $get_exercises->bind_result($exercise_selected, $difficulty);
}
?>

<form action="create.php" method="POST">
    <input type="text" name="exercise_creation" placeholder="exercise">
    <input type="text" name="difficulty" placeholder="difficulty">
    <input type="submit" name="add_execise">
</form>

<p>Choose level of difficulty:</p> &nbsp;&nbsp; 
<form action="create.php" method="POST">

    <button id="beginner" name="beginner" value="beginner">Beginner</button><br><br>

    <button id="intermediate" name="intermediate" value="intermediate">Intermediate</button><br><br>

    <button id="advanced" name="advanced" value="advanced">Advanced</button>
</form><br><br><br>

<?php 


    while ($get_exercises->fetch()) {
        # code...

        echo $exercise_selected . "<br>";
    }

?>

Answer 1

Can someone tell me if this is clean code...

The code is somewhat easy to read (e.g. it appears to somewhat align with the recommendations of PSR-12. It is also great that parameters are bound to the queries so SQL injection should be less likely to occur.

...or if it's too much?

The mixing of PHP within HTML is not great. It is recommended to have the PHP fetch data and then within the HTML just output any data that might have been retrieved.

There are quite a few duplicate lines in these three blocks:

if (isset($_POST['beginner'])) {
    # code...
    $get_exercises = $con->prepare("SELECT exercise_name, difficulty FROM exercises WHERE difficulty = ?");
    $get_exercises->bind_param("s", $_POST['beginner']);
    $get_exercises->execute();
    $get_exercises->store_result();
    $num_of_rows = $get_exercises->num_rows;
    $get_exercises->bind_result($exercise_selected, $difficulty);
}

if (isset($_POST['intermediate'])) {
    # code...
    $get_exercises = $con->prepare("SELECT exercise_name, difficulty FROM exercises WHERE difficulty = ?");
    $get_exercises->bind_param("s", $_POST['intermediate']);
    $get_exercises->execute();
    $get_exercises->store_result();
    $num_of_rows = $get_exercises->num_rows;
    $get_exercises->bind_result($exercise_selected, $difficulty);
}

if (isset($_POST['advanced'])) {
    # code...
    $get_exercises = $con->prepare("SELECT exercise_name, difficulty FROM exercises WHERE difficulty = ?");
    $get_exercises->bind_param("s", $_POST['advanced']);
    $get_exercises->execute();
    $get_exercises->store_result();
    $num_of_rows = $get_exercises->num_rows;
    $get_exercises->bind_result($exercise_selected, $difficulty);
}

It appears that only two of each eight lines in each block changes - i.e. the if condition and the second parameter passed to $get_exercises->bind_param().

A commonly accepted principle is the Don't Repeat Yourself. principle. The common lines could be abstracted to a function, or even a simple check for common values:

Instead of setting the name attribute uniquely on each button

<button id="beginner" name="beginner" value="beginner">Beginner</button><br><br>

Set the name on all three the same - e.g. name=“difficulty”. That can make the query logic much simpler:

//initialize to avoid undefined variables if POST not set
$exercise_selected = $difficulty = null;
if (isset($_POST['difficulty']) {
    $get_exercises = $con->prepare("SELECT exercise_name, difficulty FROM exercises WHERE difficulty = ?");
    $get_exercises->bind_param("s", $_POST['difficulty']);
    $get_exercises->execute();
    $get_exercises->store_result();
    $num_of_rows = $get_exercises->num_rows;
    $get_exercises->bind_result($exercise_selected, $difficulty);
}