Can anyone spot why is this code not working?

[layman]

Hello Everyone

 

Can anyone spot why is this code not working? Your help would be very much appreciated!

 

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

        $sql = "SELECT SID FROM STUDENTS";
        $result = $db1->getResult($sql);

        $sql = "SELECT CID FROM COURSES";
        $result = $db1->getResult($sql);

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

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

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


        </form>

        <?php
            } else {
                $sid = $_POST['SID'];
                $cid = $_POST['CID'];
                $grade = $_POST['grade'];
                $comments = $_POST['comments'];
                $db1 = new student_course();
                $db1->openDB();
                $numofrows = $db1->insert_student_course($SID, $CID, $grade, $comments);
                echo "Success. Number of rows affected: <strong>{$numofrows}<strong>";
                $db1->closeDB();
            }
        ?>

 

This is the function I`m trying to use:

 

function insert_student_course($sid, $cid, $grade, $comments) {
        $esc_grade = mysql_real_escape_string($grade, $this->conn);             
        $esc_comments = mysql_real_escape_string($comments, $this->conn);       
        $sql = "INSERT INTO student_course (sid, cid, grade, comments)          
        VALUES ('{$sid}', '{$cid}', '{$esc_grade}', '{$esc_comments}')";
        $result = mysql_query($sql, $this->conn);
        if (!$result) {
            die("SQL Insertion error: " . mysql_error());
        } else {
            $numofrows = mysql_affected_rows($this->conn);
            return $numofrows;
        }
    }

    function getResult($sql) {
       $getResult = mysql_query($sql, $this->conn);
       if (!getResult) {
           die("SQL Insertion error: " . mysql_error());
        } else {
            $numofrows = mysql_affected_rows($this->conn);
            return $numofrows;
       }
    }

 

The drop down menu is not working for some reason and I can`t insert data to my table. Could anyone help me with this?

 

I am using mysql server version 5.1.41.

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

All the best,

layman

[mikosiko]

without look your whole code (maybe is more errors there).. but this lines contain a obvious error 

        $sql = "SELECT SID FROM STUDENTS";
        $result = $db1->getResult($sql);

        $sql = "SELECT CID FROM COURSES";
        $result = $db1->getResult($sql);

 

Hint: I will not name my two dogs "dummer".... they will be really confused if I call them 

[layman]

Thanks! I re-named my "dogs" Each of them got a different name

Do you know why is the drop down not working?

[cyberRobot]

What kind of error are you getting? Is the drop-down menu not showing up?

[layman]

The drop down menu show, but I can not see the options, to choose them.

And if I type the options manually, they are there.

Any idea, what am I doing wrong?

[cyberRobot]

I think the issue is that your function "getResult()" is returning the number of results found instead of the actual result set. Does it work if you change the function to:

 

function getResult($sql) {
     $getResult = mysql_query($sql, $this->conn);
     if (!getResult) {
          die("SQL Insertion error: " . mysql_error());
     } else {
          return $getResult;
     }
}

[layman]

OK, I changed it!

Thank you

Now it shows the drop down list. But the list is empty!

Do you know why is not extracting the value from the table?

[cyberRobot]

The other thing that might be throwing things off is the field name. In the MySQL table is the field name "SID" or "sid"? In the original post you use both upper and lower case fieldnames.

 

$sql = "SELECT SID FROM STUDENTS";

...

$sql = "INSERT INTO student_course (sid, cid, grade, comments)                  VALUES ('{$sid}', '{$cid}', '{$esc_grade}', '{$esc_comments}')";

 

 

Also, I'm a little confused by what you mean by the drop-down list is now showing. What is the difference between these two statements?

Most recent statement: "Now it shows the drop down list. But the list is empty!"

Earlier statement: "The drop down menu show, but I can not see the options, to choose them."

 

 

[cyberRobot]

Sorry, I just noticed that the above code refers to two seperate tables. So that probably isn't the problem.

 

Do you know if the query is returning results? What do you get if you do something like this:

 

$sql = "SELECT SID FROM STUDENTS";
$result = $db1->getResult($sql);
print mysql_num_rows($result);

[layman]

Sorry, if I did not express myself clearly!

In the 1st statement, I meant that I could not even open down the drop down arrow. It was just showing that there is a drop down list.

In the 2nd statement, I meant that I was able to open down the "down" arrow, and I was able to see the empty list.

Slight difference, I know, but there is a difference.

 

Does that matter that is lower or upper case??? In my Students table I used all lower case. And in the student_course table I used all upper case. Can this be a problem??

Should I change either of them? Would it effect the PK and FK-s?

 

This returns 34. The number of rows from the students table.

$sql = "SELECT SID FROM STUDENTS";
$result = $db1->getResult($sql);
print mysql_num_rows($result);

[cyberRobot]

Sorry, if I did not express myself clearly!

 

No problem

 

 

Does that matter that is lower or upper case??? In my Students table I used all lower case. And in the student_course table I used all upper case. Can this be a problem??

Should I change either of them? Would it effect the PK and FK-s?

 

As far as I'm aware, it doesn't matter how you use case in your tables. As long as your code refers to the fields as they are in the table, you should be fine. Note that I don't know what "PK" and "FK" means.

 

 

This returns 34. The number of rows from the students table.

 

So you're getting data...

 

Speaking of case, does this do the trick:

echo "<option value='{$row['SID']}'>{$row['SID']}</option>";

 

If that doesn't work, maybe you need to remove the single quote:

echo "<option value='$row[sid]'>$row[sid]</option>";

echo "<option value='$row[sID]'>$row[sID]</option>";

[layman]

PK is Primary Key, and FK is Foreign Key. In view of this, does the case matter?

 

The 1st one is exactly the same I have been trying. No luck with it.

The 2nd one still gives me the empty list.

 

I just can`t see why is not working...!

[cyberRobot]

Maybe change to:

while ($row = mysql_fetch_array($result))

 

With mysql_fetch_row() you would need to refer to the array as $row[0]

[layman]

No luck still...

All I see is still the empty drop down.

 

Do you have any more idea?

 

[cyberRobot]

Did you try both suggestions together?

while ($row = mysql_fetch_array($result))
     echo "<option value='{$row['SID']}'>{$row['SID']}</option>";

[layman]

Yes. I tried just either of them first. Then both of them together.

The drop down is still empty...

[cyberRobot]

Could you post your updated code (drop-down code and getResult function)?

 

Also, when you say you have an empty drop-down menu. Does it look like there are 34 blank spots where the data should be?

[layman]

Yes exactly, it looks like I have 33 empty rows/spots there. 1 less, than I have in the table. And I can highlight the empty space.

 

getResult()

function getResult($sql) {
     $getResult = mysql_query($sql, $this->conn);
     if (!getResult) {
          die("SQL Insertion error: " . mysql_error());
     } else {
          return $getResult;
     }
}

 

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

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

[mikosiko]

if this is your current code:

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

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

 

then you have there another clear error.... result1 <> result

 

[cyberRobot]

Since the result set is being saved to $result1, you need to change the while loop

while ($row = mysql_fetch_array($result1))

 

 

And I think the field name case needs to match here:

$sql = "SELECT sid FROM STUDENTS";

 

and here:

echo "<option value='{$row['SID']}'>{$row['SID']}</option>";

[layman]

Yeah, sorry, posted the old/wrong version with result and not with result1.

I`m getting confused and excited at the same time (I`m nearly there...).

 

And I fixed the field name case  to match.

So the DROP DOWN IS WORKING!!                           THANK YOU!!!

 

 

But when I choose the student from the drop down, and try to insert data to the table  I get this error:

 

SQL Insertion 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 '// insert new record into table VALUES ('3', '4', 'A', 'Some Comment')' at line 1

 

 

[layman]

Here is the insert function()

 

function insert_student_course($sid, $cid, $grade, $comments) {
        $esc_grade = mysql_real_escape_string($grade, $this->conn);             
        $esc_comments = mysql_real_escape_string($comments, $this->conn);        
        $sql = "INSERT INTO student_course (SID, CID, GRADE, COMMENTS)          
        VALUES ('{$sid}', '{$cid}', '{$esc_grade}', '{$esc_comments}')";
        $result = mysql_query($sql, $this->conn);
        if (!$result) {
            die("SQL Insertion error: " . mysql_error());
        } else {
            $numofrows = mysql_affected_rows($this->conn);
            return $numofrows;
        }
    }

 

In the student_course table I have used upper case.

[cyberRobot]

If you try this, what does it display:

$sql = "INSERT INTO student_course (SID, CID, GRADE, COMMENTS) VALUES ('{$sid}', '{$cid}', '{$esc_grade}', '{$esc_comments}')";
echo $sql;
$result = mysql_query($sql, $this->conn);

[layman]

INSERT INTO student_course (SID, CID, GRADE, COMMENTS) VALUES ('10', '10', '', '')

Fatal error: Using $this when not in object context in C:\xampp\htdocs\cwk\Student_Course_Insert_Tan.php on line 62

 

$sql = "INSERT INTO student_course (SID, CID, GRADE, COMMENTS) VALUES ('{$sid}', '{$cid}', '{$esc_grade}', '{$esc_comments}')";
                echo $sql;
                $result = mysql_query($sql, $this->conn);  //line 62 

 

 

 

[cyberRobot]

Did you insert the echo statement into your function?

 

function insert_student_course($sid, $cid, $grade, $comments) {
        $esc_grade = mysql_real_escape_string($grade, $this->conn);             
        $esc_comments = mysql_real_escape_string($comments, $this->conn);        
        $sql = "INSERT INTO student_course (SID, CID, GRADE, COMMENTS)          
        VALUES ('{$sid}', '{$cid}', '{$esc_grade}', '{$esc_comments}')";

        echo $sql;

        $result = mysql_query($sql, $this->conn);
        if (!$result) {
            die("SQL Insertion error: " . mysql_error());
        } else {
            $numofrows = mysql_affected_rows($this->conn);
            return $numofrows;
        }
    }