drop down list using php and phpmyadmin

[stresshead]

Hi,

 

I am trying to create a drop down list in php and I want the data to come from a table that I have created in phpmyadmin. The code that I have created allows me to select values from the drop down list and insert the rest of the data. However when I check the the table the SID and Cid are set to 0 and the grade field is empty and the comments field contains the grade. The SID and Cid are both composite keys.

 

<?php
        $sql = "SELECT Cid FROM course";
        $db1 = new DBStudent_Course();
        $db1->openDB();
        $result = $db1->getResult($sql);

        echo"<select name = Cid>";
        while ($row = mysql_fetch_object($result)) {
            echo "<option value = '" . $row->Cid . "'>$row->Cid</option>";
        }

        echo"</select>";
        echo "</p>";
        ?>

        <?php
        $sql = "SELECT SID FROM student";
        $db1 = new DBStudent_Course();
        $db1->openDB();
        $result = $db1->getResult($sql);

        echo"<select name = SID>";
        while ($row = mysql_fetch_object($result)) {
            echo "<option value = '" . $row->SID . "'>$row->SID</option>";
        }

        echo"</select>";
        echo "</p>";
        ?>
        <?php
        if (!$_POST) { //page loads for the first time
        ?>
            <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
                grade:<input type="text" name="grade"/><br/>
                comments:<input type="text" name="comments" /><br />
                <input type="submit" value="Save" />
            </form>
<?php
        } else {
            $Cid = $_POST["Cid"];
            $SID = $_POST["SID"];
            $grade = $_POST["grade"];
            $comments = $_POST["comments"];
            $db1 = new DBStudent_Course();
            $db1->openDB();
            $numofrows = $db1->insert_student_course("", $SID, $Cid, $grade, $comments);
            echo "Success. Number of rows affected:
<strong>{$numofrows}<strong>";
            $db1->closeDB();
        }
?>

 

 

[trq]

We need to see the DBStudent_Course class and in particular, the insert_student_course method.

[stresshead]

Hi,

 

Here is the DBStudent_Course

<?php

//Save as DBStudent_Course.php
require("db_config.php");

class DBStudent_Course {
    /* DB connection handle */

    private $conn;

    function insert_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 = "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 openDB() {
        $this->conn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
        if (!$this->conn) {
            die("SQL Connection error: " . mysql_error());
        }
        $db_selected = mysql_select_db(DB_NAME, $this->conn);
        if (!$db_selected) {
            die("SQL Selection error: " . mysql_error());
        }
    }
    function getResult($sql)
{
$result = mysql_query($sql, $this->conn);
if($result)
{
return $result;
}
else
{
die("SQL Retrieve Error: " . mysql_error());
}
}

  
    function closeDB() {
        mysql_close($this->conn);
    }
    function update_student_course($Sid,$Cid, $grade, $comment)
{
$esc_SID = mysql_real_escape_string($id, $this->conn);
$esc_grade = mysql_real_escape_string($grade, $this->conn);
$esc_comment = mysql_real_escape_string($comment, $this->conn);
$sql="update student_course set name ='{$esc_grade}' where id={$esc_SID}";
$result = mysql_query($sql, $this->conn);
if(!$result)
die("SQL Error: " . mysql_error());
else
{
$numofrows = mysql_affected_rows($this->conn);
return $numofrows;
}
}

}

?>

[trq]

Try echo'ing out your sql.

 

$sql = "INSERT INTO Student_Course(SID, Cid, grade, comments) VALUES ('{$SID}','{$Cid}','{$esc_grade}', '{$esc_comments}')";
die($sql);
$result = mysql_query($sql, $this->conn);

 

What does the statement look like? Your while loops that create the drop downs are outside of your form, hence that data is never sent.

[ot]

Your passing the arguments to insert_student_course() through mysql_real_escape_string (like you should be) but then your going ahead and using the raw $SID and $Cid variables.

[/ot]

[stresshead]

hi,

 

Thank you for your help, I've got it working. 

 

Thank you again

[sugarplum_19]

im am getting the same error, what do you mean by echoing the sql like this,

 

function insert_studentcourse($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);

      echo "INSERT INTO studentcourses(sid, cid, grade, comments)

        VALUES ('{$sid}', '{$cid}', '{$grade}', '{$comments}')";

        $result = mysql_query($sql, $this->conn);

        if (!$result) {

            die("SQL Insertion error: " . mysql_error());

        } else {

            $numofrows = mysql_affected_rows($this->conn);

            return $numofrows;

        }

       

    }