Jump to content

Can anyone spot why is this code not working?


layman

Recommended Posts

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

Link to comment
Share on other sites

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  :D

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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."

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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...!

Link to comment
Share on other sites

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>";
             
                ?>

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 

 

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.