I have a script that populates a form from MySQL database table row for editing. I'm performing MySQL SELECT and UPDATE via PDO. After editing a particular table row and clicking on the submit button, the table row doesn't update.

The following is "modify-course.php":

<?php

    // configuration
    require("../includes/config.php");

    // query admin table to retrieve current admin's profile    
    //select a particular admin by id

    // query users table to retrieve current admin's profile
    if (array_key_exists('aid', $_GET)) {

    // select a particular admin by id
    $stmt = $pdo->prepare("SELECT * FROM admin WHERE aid=?");
    $stmt->execute([$_GET["aid"]]); 
    $admin = $stmt->fetch(); # get admin data

    if (!$admin)
    {
       header("Location: login.php");
    }

    // query users table to retrieve admin homepage's contents
    // $users = query("SELECT * FROM users WHERE id = ?");

    //Class import for image uploading
    //classes is the map where the class file is stored (one above the root)
    include ("../classes/upload/upload_class.php");         

    //select a particular course by id
    if (array_key_exists('cid', $_GET)) {

    // select a particular course by id
    $stmt = $pdo->prepare("SELECT * FROM courses WHERE cid=?");
    $stmt->execute([$_GET["cid"]]); 
    $course = $stmt->fetch(); # get course data

    if ($_SERVER["REQUEST_METHOD"] == "POST")
    {

        // validate submission
        if (empty($_POST["c_name"]))
        {
             echo "Provide the course name.";
        }
        if (empty($_POST["duration"]))
        {
            echo "Provide the course duration.";
        }
        if (empty($_POST["code"]))
        {
            echo "Provide the course code.";
        }
        if (empty($_POST["fees"]))
        {
            echo "Enter total fees for the course.";
        }

            // validate course name         
            if(isset($_POST['c_name'])){
                $name = ($_POST["c_name"]);

            if (!preg_match("/^[a-zA-Z0-9]*$/", $name))
            {
                echo "A course name must contain only letters and/or numbers.";
            }
            if (strlen($_POST["c_name"]) < 20 || strlen($_POST["c_name"]) > 50)
            {
                echo "A course name must be from 20 to 50 characters.";
            }
            }
            // validate course duration
            $duration = ($_POST["duration"]);

            if (!preg_match("/^[a-zA-Z0-9]*$/", $duration))
            {
                echo "Invalid course duration.";
            }
            // validate course ID
            $code = ($_POST["code"]);

            if (!preg_match("/^[a-zA-Z0-9]*$/", $code))
            {
                echo "A course ID can only contain letters and numbers.";
            }
            if (strlen($_POST["code"]) < 3 || strlen($_POST["code"]) > 10)
            {
                echo "A course code must be from 3 to 10 characters.";
            }
            if ($_POST["code"] === false)
            {
                echo "The course code has already been taken.";
            }

        //This is the directory where images will be saved 
        $max_size = 1024*250; // the max. size for uploading

        $my_upload = new file_upload;

        $my_upload->upload_dir = "../images/courses/"; // "files" is the folder for the uploaded files (you have to create this folder)
        $my_upload->extensions = array(".png", ".gif", ".jpeg", ".jpg"); // specify the allowed extensions here
        // $my_upload->extensions = "de"; // use this to switch the messages into an other language (translate first!!!)
        $my_upload->max_length_filename = 50; // change this value to fit your field length in your database (standard 100)
        $my_upload->rename_file = true;
        if(isset($_FILES['image'])) {
        $my_upload->the_temp_file = $_FILES['image']['tmp_name'];
        $my_upload->the_file = $_FILES['image']['name'];
        $my_upload->http_error = $_FILES['image']['error'];
        }
        $my_upload->replace = "y";
        $my_upload->do_filename_check = "n"; // use this boolean to check for a valid filename
        if ($my_upload->upload()) // new name is an additional filename information, use this to rename the uploaded file
        {
            $full_path = $my_upload->upload_dir.$my_upload->file_copy;
            $imagename = $my_upload->file_copy;
        }
        else
        {
            $imagename = "";
        }

        if (!empty($_POST["c_name"]))
        {
            $result = "UPDATE courses SET c_name=?, title=?, meta_keywords=?, meta_description=?, short_desc=?, c_desc=?, duration=?, code=?, fees=?, image=? WHERE cid=?";
            $stmt= $pdo->prepare($result);
            $stmt->execute([$c_name, $c_title, $meta_keywords, $meta_description, $short_desc, $c_desc, $duration, $code, $fees, $image]);

            // if username is in database
            if ($stmt === false)
            {
                echo "There was an error modifying this course.";
            }

            // update courses' DB table to reference the image's new file name
            //query(sprintf("UPDATE courses SET image = '%s' WHERE id = $id", $my_upload->file_copy));

            // find out course ID
            //$rows = $pdo->query("SELECT LAST_INSERT_ID() AS id");
            //$id = $rows[0]["id"];

            // redirect to list courses page
            header("Location: list-courses.php");
        }

    }
    }

    }
    // render the header
    include("templates/header.php");

    // render modify course template
    include("templates/modify-course_template.php");

    // render the footer
    include("templates/footer.php");

?>

The following is the template file, "modify-course_template.php":

<h1>Admin - Modify a Course</h1>
<?php

    /*
        I would advise AGAINST using getdata for any edit command. Runs the 
        risk of a XSS exploit. Sucks, but use a form instead of an anchor.
    */
    if (array_key_exists('cid', $_GET)) {

        $stmt = $pdo->prepare('
            SELECT *
            FROM courses
            WHERE cid = ?
        ');
        $stmt->execute([$_GET["cid"]]);

        if ($row = $stmt->fetch()) {

            echo sprintf('<form
                    enctype="multipart/form-data"
                    action="modify-course.php?cid=%d"
                    method="post"
                    id="modifyCourse"
                >', $row["cid"]);
                    echo '<h2>Course ID #', $row['cid'], '</h2>
                    <fieldset>
                        <div>
                            <label for="modifyCourse_name">Course Name:</label>
                            <textarea
                                cols="32" rows="2"
                                name="c_name"
                                id="modifyCourse_name"
                            >', htmlspecialchars($row['c_name']), '</textarea>
                        </div><div>
                            <label for="modifyCourse_title">Course Title:</label>
                            <textarea
                                cols="32" rows="2"
                                name="c_title"
                                id="modifyCourse_title"
                            >', htmlspecialchars($row['c_title']), '</textarea>
                        </div><div>
                            <label for="modifyCourse_metaKeywords">Meta Keywords:</label>
                            <textarea
                                cols="32" rows="2"
                                name="meta_keywords"
                                id="modifyCourse_metaKeywords"
                            >', htmlspecialchars($row['meta_keywords']), '</textarea>
                        </div><div>
                            <label for="modifyCourse_metaDescription">Meta Description:</label>
                            <textarea
                                cols="32" rows="2" 
                                name="meta_description"
                                id="modifyCourse_metaDescription"
                            >', htmlspecialchars($row['meta_description']), '</textarea>
                        </div><div>
                            <label for="modifyCourse_shortDesc">Short Description:</label>
                            <textarea
                                cols="32" rows="2" 
                                name="short_desc"
                                id="modifyCourse_shortDesc"
                            >', htmlspecialchars($row['short_desc']), '</textarea>
                        </div><div>
                            <label for="modifyCourse_desc">Description:</label>
                            <textarea
                                cols="32" rows="2" 
                                name="c_desc"
                                id="myTextarea"
                            >', htmlspecialchars($row['c_desc']), '</textarea>
                        </div><div>
                            <label for="modifyCourse_duration">Duration:</label>
                            <input
                                type="text"
                                name="duration"
                                id="modifyCourse_duration"
                                value="', htmlspecialchars($row['duration']), '"
                            >
                        </div><div>
                            <label for="modifyCourse_code">Course Code:</label>
                            <input
                                type="text"
                                name="code"
                                id="modifyCourse_code"
                                value="', htmlspecialchars($row['code']), '"
                            >
                        </div><div>
                            <label for="modifyCourse_fees">Total Fees: ₦</label>
                            <input
                                type="text"
                                name="fees"
                                id="modifyCourse_fees"
                                value="', number_format($row["fees"], 2), '"
                            >
                        </div><div>
                            <img
                                src="../images/courses/', $row['image'], '"
                                alt="', htmlspecialchars($row['c_title']), '"
                            >
                            <!-- remember, ALT is NOT optional! -->
                        </div><div>
                            <label for="modifyCourse_photo">Course Photo:</label>
                            <input
                                type="file"
                                name="image"
                                id="modifyCourse_photo"
                            >

                        </div>
                    </fieldset>
                    <div class="submitsAndHiddens">
                        <input type="hidden" name="cid" value="', $row['cid'], '">
                        <button>Modify Course</button>
                    </div>
                </form>';

        } else
            echo '
            <h2>Database Error</h2>
            <p>No matches found for requested course ID.</p>';

    } else echo '
        <h2>Input Error</h2>
        <p>You failed to provide a course ID</p>';

?>

I need your help to find out why the table row is not updating, as expected, and of course, fix the issue. Thanks in advance.

In case no one sees what's going on my web master does the following when that happens. They log the SQL submission string for the change then try it by hand if they don't see a syntax error. Also, they check the SQL database manually to see if the update took but the result isn't displayed.

Thanks for your insight. No error message is displayed. I opened php.ini to ascertain that error_reporting is set to E_ALL, set display_errors to ON, and output_buffering is set to OFF, and those are the settings I found there. I've checked the database table row manuanally and the update didn't take place.

Sorry, I don't understand what you meant by saying:

They log the SQL submission string for the change then try it by hand if they don't see a syntax error.

I don't know what you didn't understand in that sentence. But in short you try the SQL change manually. It's best to use the SQL submission string you created in your code. So you use a debugger or just echo that string to a file for you to pick up later.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.