issue in my sql query

[Muhammad_92]

-1 down vote favorite Here I'm facing problem with course registration. I have 3 tables named as dept(dept_id,dept_name) where dept_id is a primary key 2nd table is semester (semester_id, semester_name,dept_id) semester_id is a primary key and dept_id is a foreign key .3rd table is courses(c_id,dept_id,sem_id,c_name) where c_id is primary key and dept_id and sem_id is a foreign key. I have 3 combo boxes: 1st regarding department (select* from dept) <option value="<?php echo $sql_res1["dept_id"]; ?>" <?php if($sql_res1["dept_id"]==$_REQUEST["dept_id"]) { echo "Selected"; } ?>><?php echo $sql_res1["dept_name"]; ?></option> <?php } ?> 2nd combo box is semester : select * from semester where dept_id='$_REQUEST[dept_id]'"; 3rd combo box is courses. I don't know how to query in it. I want that if department is equal to computer science and semester is equal to 1st then 3rd combo box will show their relevant courses same if department is law and semester is equal to 2nd then 3rd combo box shows their relevant courses. <script language="javascript" type="text/javascript"> function showCom(cid) { document.frm.submit(); }</script> <select name="c_id" id="c_id" onChange="showComp(this.value);"> <option value="">--Select--</option> <?php $sql1="SELECT c.* FROM courses c WHERE c.cat_id='$_REQUEST[cat_id]'"; $sql_row1=mysql_query($sql1); while($sql_res1=mysql_fetch_assoc($sql_row1)){ ?> <option value="<?php echo $sql_res1["c_id"]; ?>" <?php if($sql_res1["c_id"]==$_REQUEST["c_id"]) { echo "Selected"; } ?>><?php echo $sql_res1["c_name"]; ?></option> <?php}?> </select>

[pritaeas]

You seriously need to reply and repost this in a readable format.

[tanzeel0071]

I am doing my final year project on university management system i have been stuck 2 weeks.

I don't have idea to populate related combo boxes i have 3 combo boxes 1st regarding department.


# CODE #

<select name="dept_id" id="dept_id" onChange="showCompany(this.value);">
<option value="">--Select--</option>
<?php
$sql1="select * from dept";
$sql_row1=mysql_query($sql1);
while($sql_res1=mysql_fetch_assoc($sql_row1))
{
?>
<option value="<?php echo $sql_res1["dept_id"]; ?>" <?php      
if($sql_res1["dept_id"]==$_REQUEST["dept_id"]) { echo "Selected"; } ?>><?php   
echo $sql_res1["dept_name"]; ?></option>
<?php
}
?>
</select>
<option value="">--Select--</option>
<?php
$sql="select * from semester where dept_id='$_REQUEST[dept_id]'";
$sql_row=mysql_query($sql);
while($sql_res=mysql_fetch_assoc($sql_row))
{
?>
<option value="<?php echo $sql_res["semester_id"]; ?>"><?php echo  
$sql_res["semester_name"]; ?></option>
<?php
}
?>
</select>
<option value="">--Select--</option>
<?php
$sql1="SELECT * FROM courses WHERE dept__id = '$_REQUEST[dept_id]'' AND   
sem_id ='$_REQUEST[semester_name]''";
$sql_row1=mysql_query($sql1);
while($sql_res1=mysql_fetch_assoc($sql_row1))
{
?>
<option value="<?php echo $sql_res1["dept_id"]; ?>" <?php   
if($sql_res1["dept_id"]==$_REQUEST["dept_id"]) { echo "Selected"; } ?>><?php   
echo $sql_res1["c_name"]; ?></option>
<?php
}
?>
</select>

But this code doesn't meet my requirement when i select department(computer science) from 1st
combo box it shows all courses of selected department.

I want to show only the selected semester courses for example i select computer science department it enables 3rd combo box which display all courses of computer science regardless of semester kindly help me out.

[Lau_1]

u mean u want like 1st combobox selected with (computer science),
then second combobox will display all semester for computer science,
and last combobox display all the course for selected semester?

can i know how is ur table designed? some example with value for your table

for ur last part of ur code, it got some problem based in ur table listed above, 3rd table is courses(c_id,dept_id,sem_id,c_name) , it should be something like

<?php $sql1="SELECT * FROM courses WHERE dept_id = '$_REQUEST[dept_id]' AND sem_id ='$_REQUEST[sem_id]'" ; 
        $sql_row1=mysql_query($sql1);        
        while($sql_res1=mysql_fetch_assoc($sql_row1)) { ?>
    <option value="<?php echo $sql_res1[" c_id "]; ?>"> <?php echo $sql_res1[" c_name "]; ?>

    </option>
    <?php } ?>

U properly need to check ur code again

[lps]

Hehe, this is a bit tricky that needs some help from jquery/ajax.
Here is a few solution:
1. Ajax
Your existing code:

<select name="dept_id" id="dept_id" onChange="showCompany(this.value);">
    <option value="">--Select--</option>
    <?php
    $sql1 = "select * from dept";
    $sql_row1 = mysql_query($sql1);
    while ($sql_res1 = mysql_fetch_assoc($sql_row1)) {
        ?>
        <option value="<?php echo $sql_res1["dept_id"]; ?>" <?php if ($sql_res1["dept_id"] == $_REQUEST["dept_id"]) {
        echo "Selected";
    } ?>><?php echo $sql_res1["dept_name"]; ?></option>
            <?php
    }
    ?>
</select>

then in the showCompany(), add in ajax() to retrieve the relevant info to be populated into other <select> box

1. jquery:
   populate all data into second and third select box like what you doing currently done and play with a little jquery and attributes. Eg:
   add the class into <option value="<?php echo $sql_res["semester_id"]; ?>"><?php echo $sql_res["semester_name"]; ?></option> become <option value="<?php echo $sql_res["semester_id"]; ?>" class="<?php echo $sql_res["dept_id"]; ?>"><?php echo $sql_res["semester_name"]; ?></option>
   Then in the showCompany(), hide the <option> that are not having the class of selected dept_id, either with css display: none; or jquery .hide()