Can anyone spot why is this code not working?

layman · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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

Do you know why is the drop down not working?

cyberRobot · January 5, 2011

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

layman · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

No luck still...

All I see is still the empty drop down.

Do you have any more idea?

cyberRobot · January 5, 2011

Did you try both suggestions together?

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

layman · January 5, 2011

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

The drop down is still empty...

cyberRobot · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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 · January 5, 2011

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 · January 6, 2011

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 · January 6, 2011

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 · January 6, 2011

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

Sign In

Can anyone spot why is this code not working?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information