Issue Updating A Single Row From A MySQL Database Table

[mexabet]

I'm trying to upgrade my script from PHP 5.5 to PHP 7.2. And that came with the script not able to modify a selected table row as before. The records don't even populate whenever I try to edit a course in the browser. Please, note that I'm connecting to the database via PDO.

The following is my script:

 <?php

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

    // query admin table to retrieve current admin's profile
    $admin = query("SELECT * FROM admin WHERE admin_id = ?", $_SESSION["admin_id"]);

    if (!$admin)
    {
        redirect("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");         

    $course_id = isset($_POST["course_id"]) ? $_POST["course_id"] : ''; 

    //$course_id = $_GET["course_id"];
    $courses = query("SELECT * FROM courses WHERE course_id = '$course_id'");

    // if form was submitted, modify user
    if ($_SERVER["REQUEST_METHOD"] == "POST")
    {

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

        //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;

        $my_upload->the_temp_file = $_FILES['courseimage']['tmp_name'];
        $my_upload->the_file = $_FILES['courseimage']['name'];
        $my_upload->http_error = $_FILES['courseimage']['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["coursename"]))
        { 
            $coursename = $_POST['coursename'];
            $course_title = $_POST["course_title"];
            $meta_keywords = $_POST["meta_keywords"];
            $meta_description = $_POST["meta_description"];
            $short_desc = $_POST["short_desc"];
            $coursedesc = $_POST["coursedesc"];
            $courseduration = $_POST['courseduration'];    
            $coursecode = $_POST['coursecode'];
            $fees = $_POST['fees'];
            $courseimage = $my_upload->file_copy;

            // validate coursename
            $coursename = ($_POST["coursename"]);
            //if (!preg_match("/^[a-zA-Z0-9]*$/", $coursename))
           // {
             //   adminapologize("A course name must contain only letters and numbers.");
           // }
            if (strlen($coursename) < 20 || strlen($coursename) > 50)
            {
                adminapologize("A course name must be from 20 to 50 characters.");
            }
            // validate course duration
            $courseduration = ($_POST["courseduration"]);
            //if (!preg_match("/^[a-zA-Z0-9]*$/", $courseduration))
            //{
            //    adminapologize("Invalid course duration.");
            //}
            // validate courseid
            //$coursecode = ($_POST["coursecode"]);
            //if (!preg_match("/^[a-zA-Z0-9]*$/", $coursecode))
            //{
             //   adminapologize("A course ID can only contain letters and numbers.");
            //}
            if (strlen($coursecode) < 3 || strlen($coursecode) > 10)
            {
                adminapologize("A course code must be from 3 to 10 characters.");
            }
            if ($_POST["coursecode"] === false)
            {
                adminapologize("The course code has already been taken.");
            }
            // insert form input into database
            $result = query("UPDATE courses SET coursename = '$coursename', course_title = '$course_title', meta_keywords = '$meta_keywords', meta_description = '$meta_description', short_desc = '$short_desc', coursedesc = '$coursedesc', courseduration = '$courseduration', coursecode = '$coursecode', fees = '$fees', courseimage = '$courseimage' WHERE course_id = '$course_id'");

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

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

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

            // redirect to portfolio
            redirect("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");

?> 

Below is the "modify-course_template.php" file:

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

    $course_id = isset($_GET["course_id"]) ? $_GET["course_id"] : '';

    $courses = query("SELECT * FROM courses WHERE course_id = '$course_id'");

    $rows = $courses[0];

    printf('<div class="form-group">');
    printf("Course ID#: $course_id");
    printf('</div>');

?>    
    <form enctype="multipart/form-data" action="modify-course.php?id=<?php echo $course_id; ?>" method="post">
 <?php

        printf('<fieldset>');
        printf('<div class="form-group">');
        printf('Course Name: <textarea autofocus class="form-control" placeholder="Course Name" cols="32" rows="2" name="coursename">'.$rows[0]["coursename"].'</textarea>');
        printf('</div>');
        printf('<div class="form-group">');
        printf('Course Title: <textarea autofocus class="form-control" placeholder="Course Title" cols="32" rows="2" name="course_title">'.$rows["course_title"].'</textarea>');
        printf('</div>');
        printf('<div class="form-group">');
        printf('Meta Keywords: <textarea autofocus class="form-control" placeholder="Meta Keywords" cols="32" rows="2" name="meta_keywords">'.$rows[0]["meta_keywords"].'</textarea>');
        printf('</div>');
        printf('<div class="form-group">');
        printf('Meta Description: <textarea autofocus class="form-control" placeholder="Meta Description" cols="32" rows="2" name="meta_description">'.$rows[0]["meta_description"].'</textarea>');
        printf('</div>');
        printf('<div class="form-group">');
        printf('Short Description: <textarea autofocus class="form-control" placeholder="Short Description" cols="32" rows="2" name="short_desc">'.$rows[0]["short_desc"].'</textarea>');
        printf('</div>');
        printf('<div class="form-group">');
        printf('Description: <textarea autofocus class="form-control" id="myTextarea" placeholder="Course Description" cols="32" rows="2" name="coursedesc">'.$rows[0]["coursedesc"].'</textarea>');
        printf('</div>');
        printf('<div class="form-group">');
        printf('Duration: <input autofocus class="form-control" name="courseduration" . value="' . $rows[0]["courseduration"] .'" placeholder="Course Duration" type="text"/>');
        printf('</div>');
        printf('<div class="form-group">');
        printf('Course Code: <input autofocus class="form-control" name="coursecode" . value="' . $rows[0]["coursecode"] .'" placeholder="Course Code" type="text"/>');
        printf('<input type="hidden" name="id" . value="' . $rows[0]["id"] .'" id="id"/>');
        printf('</div>');
        printf('<div class="form-group">Total Fees: ₦ ');
        //printf('<input autofocus class="form-control" name="fees" value="' . $users[0]["fees"]="₦" . number_format($users[0]["fees"], 2).'" placeholder="User Funds" type="text"/>');
        printf('<input autofocus class="form-control" name="fees" value="' . $rows[0]["fees"].'" placeholder="Fees" type="text"/>');        
        printf('</div>');
        printf('<div class="form-group">');
        printf("<img src='../images/courses/" . $rows[0]['courseimage']."'/>");
        printf('</div>');
        printf('<div class="form-group">');
        printf('Course Photo: <input autofocus class="form-control" name="courseimage" . value="' . $rows[0]["courseimage"] .'" id="fileimage" placeholder="Course Photo" type="file"/>');
        printf('</div>');
        printf('<div class="form-group">');
        printf('<button type="submit" class="btn btn-default" name="Modify Course" value="Modify Course">Modify Course</button>');
        printf('</div>');
    printf('</fieldset>');
    printf('</form>');

?>

<div>
</div>
<br/>

The erroer message I get whenever I preview the page in the browser is as follows:

Notice: Undefined offset: 0 in C:\wamp64\www\computer-school\admin\templates\modify-course_template.php on line 8

I need help to fix the issue. Thanks in advance.

[alan.davies]

There are many UDFs here and I'm assuming that they are held in the config.php file. We can't comment on these as we cannot see them. Your query() UDF, I'm assuming runs some kind of prepared statement, judging from the code on line 7:

$admin = query("SELECT * FROM admin WHERE admin_id = ?", $_SESSION["admin_id"]);

That's all well and good, but what does it return an array (single record as in a PDO fetch as opposed to a fetchAll)?
Some of your code is open to SQL injection:

$course_id = isset($_POST["course_id"]) ? $_POST["course_id"] : ''; 
$courses = query("SELECT * FROM courses WHERE course_id = '$course_id'");

So it's expecting a form post (or Ajax post request) with the course id. You need to sanitize or use a prepared statement as in your first statement.
However, all seems to work from what you say until the template page:

$course_id = isset($_GET["course_id"]) ? $_GET["course_id"] : '';
$courses = query("SELECT * FROM courses WHERE course_id = '$course_id'");
$rows = $courses[0];

Again, open to SQL injection. But you seem to be in admin mode, so let's get to the nub of it...

$course_id = isset($_GET["course_id"]) ? $_GET["course_id"] : '';

Suggests you have this in the url querystring of the form action (or the Ajax get params or url querystring). If this is missing, then the following...

$courses = query("SELECT * FROM courses WHERE course_id = '$course_id'");

will give you FALSE and therefore the $courses[0] will not exist as $courses is boolean and not an array.

var_dump your variables as a quick check as to what's going on. I'm not sure this is a PHP7 issue.

[mexabet]

@alan.davies,
Thanks for your time and insight.

Yes, "query()" is a custom function located in "functions.php". That's my attempt on using PDO for database connection and queries with prepared statements.

<?php

    /**
     * functions.php
     *
     * FlamyTech Computer School
     *
     * Helper functions.
     */

    require_once("constants.php");

    /**
     * Apologizes to user with message.
     */
    function apologize($message)
    {
        render("apology.php", ["message" => $message]);
        exit;
    }

    /**
     * Apologizes to admin with message.
     */
    function adminapologize($message)
    {
        adminrender("apology.php", ["message" => $message]);
        exit;
    }

    /**
     * Facilitates debugging by dumping contents of variable
     * to browser.
     */
    function dump($variable)
    {
        require("../templates/dump.php");
        exit;
    }

    /**
     * Logs out current user, if any.  Based on Example #1 at
     * http://us.php.net/manual/en/function.session-destroy.php.
     */
    function logout()
    {
        // unset any session variables
        $_SESSION = [];

        // expire cookie
        if (!empty($_COOKIE[session_name()]))
        {
            setcookie(session_name(), "", time() - 42000);
        }

        // destroy session
        session_destroy();
    }

    /**
     * Executes SQL statement, possibly with parameters, returning
     * an array of all rows in result set or false on (non-fatal) error.
     */
    function query(/* $sql [, ... ] */)
    {
        // SQL statement
        $sql = func_get_arg(0);

        // parameters, if any
        $parameters = array_slice(func_get_args(), 1);

        // try to connect to database
        static $handle;
        if (!isset($handle))
        {
            try
            {
                // connect to database
                $handle = new PDO("mysql:dbname=" . DB_NAME . ";host=" . DB_SERVER, DB_USERNAME, DB_PASSWORD);

                // ensure that PDO::prepare returns false when passed invalid SQL
                $handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 
            }
            catch (Exception $e)
            {
                // trigger (big, orange) error
                trigger_error($e->getMessage(), E_USER_ERROR);
                exit;
            }
        }

        // prepare SQL statement
        $statement = $handle->prepare($sql);
        if ($statement === false)
        {
            // trigger (big, orange) error
            trigger_error($handle->errorInfo()[2], E_USER_ERROR);
            exit;
        }

        // execute SQL statement
        $results = $statement->execute($parameters);

        // return result set's rows, if any
        if ($results !== false)
        {
            return $statement->fetchAll(PDO::FETCH_ASSOC);
        }
        else
        {
            return false;
        }
    }

    /**
     * Redirects user to destination, which can be
     * a URL or a relative path on the local host.
     *
     * Because this function outputs an HTTP header, it
     * must be called before caller outputs any HTML.
     */
    function redirect($destination)
    {
        // handle URL
        if (preg_match("/^https?:\/\//", $destination))
        {
            header("Location: " . $destination);
        }

        // handle absolute path
        else if (preg_match("/^\//", $destination))
        {
            $protocol = (isset($_SERVER["HTTPS"])) ? "https" : "http";
            $host = $_SERVER["HTTP_HOST"];
            header("Location: $protocol://$host$destination");
        }

        // handle relative path
        else
        {
            // adapted from http://www.php.net/header
            $protocol = (isset($_SERVER["HTTPS"])) ? "https" : "http";
            $host = $_SERVER["HTTP_HOST"];
            $path = rtrim(dirname($_SERVER["PHP_SELF"]), "/\\");
            header("Location: $protocol://$host$path/$destination");
        }

        // exit immediately since we're redirecting anyway
        exit;
    }

?>

Yes, I'm in the admin mode. Since you said the my query is also open to SQL injection, I attempted to use the following PDO prepared statement, and I got errors:

    $course_id = isset($_GET["course_id"]) ? $_GET["course_id"] : '';

    $pdo->prepare("SELECT * FROM courses WHERE course_id = '$course_id'");# question marks is used as a place holder
    $row = $pdo->execute($course_id);# $course_id no fills the placeholder
    $result = $row->fetch();# and here is how you get your data
    // if form was submitted, modify user
    var_dump($row);

Notice: Undefined variable: pdo in C:\wamp64\www\computer-school\admin\modify-course.php on line 28

Fatal error: Uncaught Error: Call to a member function prepare() on null in C:\wamp64\www\computer-school\admin\modify-course.php on line 28

 Error: Call to a member function prepare() on null in C:\wamp64\www\computer-school\admin\modify-course.php on line 28

Yes, I have the URL querystring of the form action with method set to POST:

<form enctype="multipart/form-data" action="modify-course.php?id=<?php echo $course_id; ?>" method="post">

Your time is highly appreciated as always. Thanks once again.

[alan.davies]

Line 28 error: suggests that the PDO object if not being created in the first place (null) - so consequently 'prepare' method cannot exist for something that is null. Check to see that you have included the file that holds the "create pdo code" in your list of include files.

//Edit
Sorry - I am away for in self-imposed exile for a month and can't help further until then. Good luck!