Can anyone spot why is this function not working?

[layman]

Hello Everyone

 

I`m new to PHP, therefore I would really appreciate any help with this code.

 

The error message I get:

SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where cid=B' at line 1

 

This is the function, I`m trying to use:

function update_student_course($sid, $cid, $grade, $comments) {
        $esc_sid = mysql_real_escape_string($sid, $this->conn);
        $esc_cid = mysql_real_escape_string($cid, $this->conn);
        $esc_grade = mysql_real_escape_string($grade, $this->conn);
        $esc_comments = mysql_real_escape_string($comments, $this->conn);
        
        $sql = "UPDATE studentcourses set grade='{$esc_grade}', comments='{$esc_comments}' where sid={$esc_sid} and cid={$esc_cid}";
        $result = mysql_query($sql, $this->conn);
        if (!$result)
            die("SQL Error: " . mysql_error());
        else {
            $numofrows = mysql_affected_rows($this->conn);
            return $numofrows;
        }
    }

 

And this would be the code:

 

<?php
        $db1 = new student_course();
        $db1->openDB();

        $sql = "SELECT sid FROM STUDENTS";
        $result1 = $db1->getResult($sql);

        $sql = "SELECT cid FROM COURSES";
        $result2 = $db1->getResult($sql);

        if (!$_POST) { //page loads for the first time
        ?>

        <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
                Choose student ID: <select name = "sid">
                <?php
                while ($row = mysql_fetch_array($result1))
                    echo "<option value='{$row['sid']}'>{$row['sid']}</option>";
                ?>
            </select><br/>
            Choose course ID: <select name = "cid">
                <?php
                while ($row = mysql_fetch_array($result2))
                    echo "<option value ='{$row['cid']}'>{$row['cid']} </option>";
                ?>
            </select><br/>

            Enter grade to update: <input type="text" name="cid" /><br />
            Enter new comment: <input type="text" name="cname" /><br />

            <input type="submit" value="Update" />

            </form>
<?php
        } //end if
        else {
            $sid = $_POST['sid'];
            $cid = $_POST['cid'];
            $grade = $_POST['grade'];
            $comment = $_POST['comment'];
            $db1 = new student_course();
            $db1->openDB();
            $numofrows = $db1->update_student_course($sid, $cid, $grade, $comment);
            echo "Success. Number of rows affected:
            <strong>{$numofrows}<strong>";
            $db1->closeDB();
        }
         
?>

 

Any idea, what am I doing wrong???

Server version: 5.1.41

 

Thank you very much in advance, for taking the time and reading it!!!

layman

 

[Pikachu2000]

String values need to be in quotes in a query string.

WHERE cid = '$esc_cid'

 

P.S. Don't use $_SERVER['PHP_SELF'] as a form action. Just use action="" to submit a form to itself.

[layman]

Thanks! I left them out... 

 

Now I got no error. However, the update is still not happening in the database.

Any idea why??

 

 

[Pikachu2000]

Echo your query string to make sure it contains the values you'd expect it to contain. If it does, you can use those values to run a SELECT query through phpMyAdmin to be sure the query is matching at least one record in the database.

[layman]

What if it does not contains the values?

 

I got this back:

UPDATE studentcourses set grade='', comments='' where sid='' and cid=''Success. Number of rows affected:

 

Could you explain me in simple terms what am I doing wrong please?

[PFMaBiSmAd]

You might want to look at all the name= attributes in your form and make sure they are what you want and that your $_POST[  ] variable names match. You have at least two form field with the same name (the last one will 'win') and at least two of your $_POST[  ] variable names don't exist.

[Pikachu2000]

EDIT: ^^^ Ditto, lol.

[layman]

I have corrected the name=attributes. Thanks!

 

at least two of your $_POST[  ] variable names don't exist.

I have changed the comment to comments. But I can`t find the other one. Could you give me some clues please?

 

You have at least two form field with the same name (the last one will 'win')

What do you mean by that, which ones are the same?

 

 

 

[BlueSkyIS]

look at the form fields. look at the name assigned to each one. there should be no form fields with duplicate names within the form.

 

one other thing: do not allow space around the equal sign in tag attributes.

 

<select name = "sid"> NO

<select name="sid"> YES

[layman]

Thanks. I just corrected them.

 

I do not understand why I am not able to update any data via this form.

 

I made all the corrections suggested here.

 

The function looks like now this:

function update_student_course($sid, $cid, $grade, $comments) {
        $esc_sid = mysql_real_escape_string($sid, $this->conn);
        $esc_cid = mysql_real_escape_string($cid, $this->conn);
        $esc_grade = mysql_real_escape_string($grade, $this->conn);
        $esc_comments = mysql_real_escape_string($comments, $this->conn);
        
        $sql = "UPDATE studentcourses set grade='{$esc_grade}', comments='{$esc_comments}' where sid='{$esc_sid}' and cid='{$esc_cid}'";
        $result = mysql_query($sql, $this->conn);
        if (!$result)
            die("SQL Error: " . mysql_error());
        else {
            $numofrows = mysql_affected_rows($this->conn);
            return $numofrows;
        }
    }

 

And the code after all the corrections looks like this:

<?php
        $db1 = new student_course();
        $db1->openDB();

        $sql = "SELECT sid FROM STUDENTS";
        $result1 = $db1->getResult($sql);

        $sql = "SELECT cid FROM COURSES";
        $result2 = $db1->getResult($sql);

        if (!$_POST) { //page loads for the first time
        ?>

        <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
                Choose student ID: <select name="sid">
                <?php
                while ($row = mysql_fetch_array($result1))
                    echo "<option value='{$row['sid']}'>{$row['sid']}</option>";
                ?>
            </select><br/>
            Choose course ID: <select name="cid">
                <?php
                while ($row = mysql_fetch_array($result2))
                    echo "<option value ='{$row['cid']}'>{$row['cid']} </option>";
                ?>
            </select><br/>

            Enter grade to update: <input type="text" name="grade" /><br />
            Enter new comment: <input type="text" name="comments" /><br />

            <input type="submit" value="Update" />

            </form>
<?php
        } //end if
        else {
            $sid = $_POST['sid'];
            $cid = $_POST['cid'];
            $grade = $_POST['grade'];
            $comments = $_POST['comments'];
            $db1 = new student_course();
            $db1->openDB();
            //$numofrows = $db1->update_student_course($sid, $cid, $grade, $comment);

            $sql = "UPDATE studentcourses set grade='{$esc_grade}', comments='{$esc_comments}' where sid='{$esc_sid}' and cid='{$esc_cid}'";
            echo $sql;
            $result = mysql_query($sql);

            echo "Success. Number of rows affected:
            <strong>{$numofrows}<strong>";
            $db1->closeDB();
        }
         
?>

 

Could anyone explain the problem in very simple terms, so I`ll understand it. Thank you.

 

[Pikachu2000]

At the very top of the script, immediately after the opening <?php tag, paste this code, and post its output when the form has been submitted.

 

echo 'Debugging output:';
echo '<pre>';
print_r($_POST);
echo '</pre>';
echo 'End debugging';

[layman]

This was the output:

 

Debugging output:

Array

(

    [sid] => 4

    [cid] => 10

    [grade] => B

    [comments] => Comment

)

End debuggingUPDATE studentcourses set grade='', comments='' where sid='' and cid=''Success. Number of rows affected:

 

Do you know what does this mean?

I have sid 4 in the student table so there is a record which should have been updated.

[PFMaBiSmAd]

I see the current reason why your query does not contain data values.

 

However, it will help you much much more if you set error_reporting to E_ALL and display_errors to ON so that all the php detected errors will be reported and displayed. You will save a TON of time.

[layman]

And where should I set this error reporting?

Is that goes straight after the opening PHP tag?

[layman]

I have looked for the syntax, but what I am trying is not working.

http://www.php.net/manual/en/errorfunc.configuration.php#ini.error-reporting

 

Is this the right syntax?

function display_errors() {
     on;
    }

 

error_reporting(E_ALL);
        display_errors(on);

[Pikachu2000]

This will do it for you on a per script basis. For development, you really should enable it site-wide in your php.ini file if possible, though.

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

[layman]

I got the line, where the error is, but I can not see what is wrong here.

 

$sql = "UPDATE studentcourses set grade='{$esc_grade}', comments='{$esc_comments}' where sid='{$esc_sid}' and cid='{$esc_cid}'";

 

And also here:

echo "Success. Number of rows affected: 
                <strong>{$numofrows}</strong>";  //in this row

 

Could anyone point the it out for me what am I doing wrong? Thank you.

[Pikachu2000]

What's the error?

[layman]

Notice: Use of undefined constant getResult - assumed 'getResult' in C:\xampp\htdocs\cwk\Class_Student_Course_Proba.php on line 29

 

Notice: Use of undefined constant getResult - assumed 'getResult' in C:\xampp\htdocs\cwk\Class_Student_Course_Proba.php on line 29

 

Notice: Undefined variable: esc_grade in C:\xampp\htdocs\cwk\Student_Course_Update.php on line 66

 

Notice: Undefined variable: esc_comments in C:\xampp\htdocs\cwk\Student_Course_Update.php on line 66

 

Notice: Undefined variable: esc_sid in C:\xampp\htdocs\cwk\Student_Course_Update.php on line 66

 

Notice: Undefined variable: esc_cid in C:\xampp\htdocs\cwk\Student_Course_Update.php on line 66

UPDATE studentcourses set grade='', comments='' where sid='' and cid=''

Notice: Undefined variable: numofrows in C:\xampp\htdocs\cwk\Student_Course_Update.php on line 71

Success. Number of rows affected:

 

I have defined the constant on line 29.

Line 66 is:

$sql = "UPDATE studentcourses set grade='{$esc_grade}', comments='{$esc_comments}' where sid='{$esc_sid}' and cid='{$esc_cid}'";

 

Line 71 is:

echo "Success. Number of rows affected: 
                <strong>{$numofrows}</strong>";  //in this row

[PFMaBiSmAd]

If you are getting four different undefined variable messages for one line of code, wouldn't it be a good idea to look where those variable names are supposed to be getting defined at in your code and try to find out why the variable names you are using don't exist?

 

And why aren't you calling your update_student_course() function? You apparently copied the query statement being used inside that function and pasted it into your main code without accounting for the different variable names you were using inside the function definition (see the problem about having four undefined variable names above.)

[layman]

I got, its WORKING!!

 

Thank you very much for everyone for the contribution.

Again, I learned a lot from you guys!

Thanks again.

 

All the very best,

layman