Page 1 of 1

Query by catagories - not working - HELP!!

Posted: Wed Jan 21, 2004 3:47 pm
by melindaSA
Need help, I have been looking at this for the past few weeks, and cannot get it to work....

I have a query page that allows the user to search by category, or show all.

The show all works just fine, but the search by category will not work.

Here is my code:

Code: Select all

<?php
$query = "SELECT catID, category from category ORDER by catID";
$result = mysql_query($query)
    or die ("Couldn't execute query.");
    
    //create form containing selection list


             echo '<form action="results1.php?catID='.$row['catID'].'" method="post"> <select name="category">'."\n";


                while ($row = mysql_fetch_array($result))
                {
                     extract($row);
                     echo "<option value='$category'>$category\n";
                }
                echo "</select>\n";
                echo "<input type='submit' value='SELECT'>
                      </form>\n";

?>
So the above works, pulling the categories from the database into a dropdown.

However, as soon as one of the categories are selected, I get all categories.

Here is the result1.php

Code: Select all

<?php
$query = "SELECT * FROM category ORDER BY catID";
$result = mysql_query( $query );
if ( $row = mysql_fetch_assoc( $result ) ) { // there are categories
    do {
        extract( $row );
        $subquery = "SELECT positions.posID, positions.catID, positions.title, positions.tID, departments.departments, time.time
        FROM positions, departments, time WHERE positions.depID = departments.depID AND positions.tID = time.tID AND catID=$catID";
        $subresult = mysql_query( $subquery );
        if ( $subrow = mysql_fetch_assoc( $subresult ) ) { // category has links

            echo "<table>\n";
            echo "<tr align="left"><th width="300"><font face="Verdana" size="1"><b>Position</b></font></th>
                  <th width="120"><font face="Verdana" size="1"><b>Time</b></font></th>
                  <th width="300"><font face="Verdana" size="1"><b>Department</b></font></th>
                  <th width="80"><font face="Verdana" size="1"><b>Go to Listing</b></font></th></tr>\n";
            echo "<tr bgcolor="#DAD9D8" align="left"><font face="Verdana" size="2">" .$category."</font><br></tr>\n";
            do {
                extract( $subrow );
                echo "<tr>\n";
                echo "<td width="300"><font face="Verdana" size="1">$title</font><br></td>\n";
                echo "<td width="120"><font face="Verdana" size="1">$time</font><br></td>\n";
                echo "<td width="300"><font face="Verdana" size="1">$departments</font><br></td>\n";
                echo "<td width="80"><font face="Verdana" size="1"><a href="show_positions.php?posID=$posID">view job</a></font><br></td>\n";
                echo "</tr>\n";
             }

             
            while( $subrow = mysql_fetch_assoc( $subresult ) );
        }
    }

    
    while( $row = mysql_fetch_assoc( $result ) );
}


else {
    echo "No category available";
}


?>
So where an I going wrong??

Re: Query by catagories - not working - HELP!!

Posted: Wed Jan 21, 2004 6:10 pm
by qads
. your not passing the catID to result1.php
.you need catID in <select> value
. you need to use$_POST['category'] to get the cat id in result1.php

Posted: Fri Jan 23, 2004 10:43 am
by melindaSA
Thanks for the reply, but how do I do that??

Do I have to add catID to this:

Code: Select all

<?php
 echo "<option value='catID'>$category\n";
?>
and where do I put $_POST['category']??

Sorry if I am asking stupid questions??

Thank for your help! :D

Posted: Fri Jan 23, 2004 1:00 pm
by melindaSA
OK, I got it to work....... :P

Code: Select all

<?php
$conn = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name,$conn);

function catlist($catID){
    $sql = mysql_query("SELECT category, catID FROM category");
    echo "<select name="catID">";
    while(list($category, $catID)=mysql_fetch_array($sql)){
        $category = stripslashes($category);
        echo "<option value="$catID"";
if($catID == $catID){
echo "selected";
}
echo ">$category</option>";
    }
    echo "</select>";
    mysql_free_result($sql);
}

// The Form:

echo "<form method="post" action="results1.php?catID=$catID" select name="category">
Select:";
catlist($catID);
echo "<input type="Submit" Value="Select">
</form>";
?>

Code: Select all

<?php
if ($_POST['category'] != "catID")

$query = "SELECT * FROM category WHERE catID=$catID";
$result = mysql_query( $query );
if ( $row = mysql_fetch_assoc( $result ) ) { // there are categories
    do {
        extract( $row );
        $subquery = "SELECT positions.posID, positions.catID, positions.title, positions.tID, departments.departments, time.time
        FROM positions, departments, time WHERE positions.depID = departments.depID AND positions.tID = time.tID AND catID=$catID";
        $subresult = mysql_query( $subquery );
        if ( $subrow = mysql_fetch_assoc( $subresult ) ) { // category has links

            echo "<table>\n";
            echo "<tr align="left"><th width="300"><font face="Verdana" size="1"><b>Position</b></font></th>
                  <th width="120"><font face="Verdana" size="1"><b>Time</b></font></th>
                  <th width="300"><font face="Verdana" size="1"><b>Department</b></font></th>
                  <th width="80"><font face="Verdana" size="1"><b>Go to Listing</b></font></th></tr>\n";
            echo "<tr bgcolor="#DAD9D8" align="left"><font face="Verdana" size="2">" .$category."</font><br></tr>\n";
            do {
                extract( $subrow );
                echo "<tr>\n";
                echo "<td width="300"><font face="Verdana" size="1">$title</font><br></td>\n";
                echo "<td width="120"><font face="Verdana" size="1">$time</font><br></td>\n";
                echo "<td width="300"><font face="Verdana" size="1">$departments</font><br></td>\n";
                echo "<td width="80"><font face="Verdana" size="1"><a href="show_positions.php?posID=$posID">view job</a></font><br></td>\n";
                echo "</tr>\n";
             }


            while( $subrow = mysql_fetch_assoc( $subresult ) );
        }
    }


    while( $row = mysql_fetch_assoc( $result ) );
}


else {
    echo "No category available";
}


?>
Thank you!!

Re: Query by catagories - not working - HELP!!

Posted: Fri Jan 23, 2004 1:00 pm
by qads
i posted a bit late :lol: