Query by catagories - not working - HELP!!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

Query by catagories - not working - HELP!!

Post 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??
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

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

Post 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
melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

Post 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
melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

Post 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!!
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

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

Post by qads »

i posted a bit late :lol:
Post Reply