Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Sep 2008
    Posts
    148
    Thanks
    30
    Thanked 0 Times in 0 Posts

    PHP MySQL PDO UPDATE query doesn't work

    I have a PHP file that populates a web form from a given MySQL database table row for editing. I'm performing MySQL SELECT and UPDATE queries using PDO positional (anonymous) placeholders. After editing a particular table row and clicking on the submit button, the table row doesn't update, as expected.

    The following is my "modify-course.php":
    PHP Code:
    <?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"]) < || 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":
    PHP Code:
    <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.

  2. #2
    Master Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    5,803
    Thanks
    26
    Thanked 602 Times in 595 Posts
    I need your help to find out why the table row is not updating
    do:
    PHP Code:
    var_dump($c_name$c_title$meta_keywords$meta_description$short_desc$c_desc$duration$code$fees$image); 
    before the line where you execute the update and the issue becomes self-explanatory.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  3. #3
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    4,257
    Thanks
    3
    Thanked 553 Times in 538 Posts
    I doubt any of the form processing code is running because the logic is expecting an admin's user id to be supplied via a get parameter, which doesn't exist. You should be detecting if the current user is login in by testing for the existence of a session variable with a user id in it, then query for and retrieve the permissions for the logged in user to determine if it is an admin.

    You apparently don't have php's error settings set up to report and display all errors and/or php's output_buffering is ON so any php errors and output from your code is being discarded at the header() redirects. Find the php.ini that php is using and set error_reporting to E_ALL, set display_errors to ON, and set output_buffering to OFF. Stop and start your web server to get any changes made to the php.ini to take affect.

    Your header() redirects all need exit; statements after them to stop code execution. You may want to temporarily comment out the header() redirects while you are debugging problems with the code.

    Edit: and here's another problem with the logic. When you initially visit this page, you want to query for and retrieve the existing data in order to populate the form field values. However, after the form has been submitted, if there are validation errors, you want to populate the form field values with the submitted form data, not retrieve the existing data again. The way to do this is to use an internal 'working' array variable to hold the data. Near the top of your code define an empty array variable, such as $post. Inside the form processing code, copy the $_POST data to $post and reference elements in $post in the rest of the form processing code and in the form values. At the point where you are querying for and retrieving the existing data, you would only do so if $post is empty(). When you do retrieve the existing data, store it in $post.

    You need to store validation error messages in an array, then test that array to determine if there are validation errors. If the array is empty() there are no errors. If the array is not empty() there are errors. After all the validation logic, if the array is empty(), you would use the submitted form data and execute the query. You would also display the validation errors in the appropriate place in your html document by outputting the contents of the error array.
    Last edited by CFMaBiSmAd; Jan 14th, 2019 at 06:08 PM.
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  4. #4
    Regular Coder
    Join Date
    Sep 2008
    Posts
    148
    Thanks
    30
    Thanked 0 Times in 0 Posts
    @Dormilich and @CFMaBiSmAd, thanks for your insight. I'm looking at how to use your suggestions to fix the issues. I'll get back to you soon.

  5. #5
    Regular Coder
    Join Date
    Sep 2008
    Posts
    148
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Dormilich View Post
    do:
    PHP Code:
    var_dump($c_name$c_title$meta_keywords$meta_description$short_desc$c_desc$duration$code$fees$image); 
    before the line where you execute the update and the issue becomes self-explanatory.
    I modified that section of code as you suggested like the following, but there was still no error message:
    PHP Code:
    if (!empty($_POST["c_name"]))
            {
                
    $c_name htmlspecialchars($row['c_name']);
                
    $c_title htmlspecialchars($row['c_title']);
                
    $meta_keywords htmlspecialchars($row['meta_keywords']);
                
    $meta_description htmlspecialchars($row['meta_description']);
                
    $short_desc htmlspecialchars($row['short_desc']);
                
    $duration htmlspecialchars($row['duration']);
                
    $code htmlspecialchars($row['code']);
                
    $fees htmlspecialchars($row['fees']);
                
    $image htmlspecialchars($row['image']);
                
    var_dump($c_name$c_title$meta_keywords$meta_description$short_desc$c_desc$duration$code$fees$image);
                
    $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]); 

  6. #6
    Regular Coder
    Join Date
    Sep 2008
    Posts
    148
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    I doubt any of the form processing code is running because the logic is expecting an admin's user id to be supplied via a get parameter, which doesn't exist. You should be detecting if the current user is login in by testing for the existence of a session variable with a user id in it, then query for and retrieve the permissions for the logged in user to determine if it is an admin.
    I'm having issues understanding how to implement your suggestion. The following is what I did to test the existence of admin session before displaying the page's content:
    PHP Code:
    // query admin's 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=?"$_SESSION["aid"]);
        
    $stmt->execute([$_GET["aid"]]); 
        
    $admin $stmt->fetch(); # get admin data
        
        
    if (!$admin)
        {
           
    header("Location: login.php");
           exit;
        }
        else {
        
    // logic here
       

    Can you please write out a better replacement?

    You apparently don't have php's error settings set up to report and display all errors and/or php's output_buffering is ON so any php errors and output from your code is being discarded at the header() redirects. Find the php.ini that php is using and set error_reporting to E_ALL, set display_errors to ON, and set output_buffering to OFF. Stop and start your web server to get any changes made to the php.ini to take affect.
    My php.ini has all the settings as you suggested, so I didn't have to modify anything.

    Your header() redirects all need exit; statements after them to stop code execution. You may want to temporarily comment out the header() redirects while you are debugging problems with the code.
    I've done that.

    Edit: and here's another problem with the logic. When you initially visit this page, you want to query for and retrieve the existing data in order to populate the form field values. However, after the form has been submitted, if there are validation errors, you want to populate the form field values with the submitted form data, not retrieve the existing data again. The way to do this is to use an internal 'working' array variable to hold the data. Near the top of your code define an empty array variable, such as $post. Inside the form processing code, copy the $_POST data to $post and reference elements in $post in the rest of the form processing code and in the form values. At the point where you are querying for and retrieving the existing data, you would only do so if $post is empty(). When you do retrieve the existing data, store it in $post.
    I would love to do as you suggested, but I need some sample code than pseudocode alone.

    You need to store validation error messages in an array, then test that array to determine if there are validation errors. If the array is empty() there are no errors. If the array is not empty() there are errors. After all the validation logic, if the array is empty(), you would use the submitted form data and execute the query. You would also display the validation errors in the appropriate place in your html document by outputting the contents of the error array.
    Again, I need sample code, if that would be possible.

    Thanks.
    Last edited by mexabet; Jan 17th, 2019 at 01:00 PM.


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •