Removing Search Results from drop down

Kay1021 · 05-07-2009, 04:32 PM

Hi i had a little question I was wondering someone might be able to answer. Right now i have a code that searches through the database and outputs results based on the search...then below I have a drop down menu that grabs data from the database to fill it. It works perfectly I was just wondering if there was a way that I could eliminate the first results from showing up in the drop down menu. I hope that made sense...i'll post the code that I have....any help is greatly appreciated.

PHP Code:


			
        /* Connect to database */

        $con=mysql_connect(localhost, "root", "root");

        mysql_select_db("db");

        

        if (!$con)

                  {

             die('Could not connect: ' . mysql_error());

              }

    

            $sql = "SELECT DISTINCT cat_name, postcode

                        FROM postalcode

            WHERE postcode = '$postcode'

            AND status='sold'";



            $qry = mysql_query($sql);

        

            if (mysql_num_rows($qry) > 0) {

                while ($rs = mysql_fetch_assoc($qry)) {

                         $cat_name = $rs['cat_name'];

                         $postcode = $rs['postcode'];

                         echo $rs['cat_name'] . '<br/>';

                }

            } else {

                print 'no result found';

            }

            

            print '<form method="POST" action="index.php?ID=15">';

        

            $query="SELECT DISTINCT cat_name 

                        FROM category 

                        GROUP BY cat_name";

                                

            $result = mysql_query ($query);

            echo "<select name=cat_name value=' '>";

            echo "<option value=''>View Available               

                        Categories</option>";

            while($nt=mysql_fetch_array($result))

                            {

                       echo "<option value=''>$nt[cat_name]</option>";

                }

            echo "</select><br/>";

                    

    

            print ' <input type="submit" value="Contact Us for More 

                                  Information">

            </form>';

Thanks so much

allworknoplay · 05-07-2009, 04:49 PM

I always like to use for loops, I always feel like I have better control over the data...here's what I would do...but I'm sure others could provide a simpler answer using WHILE...

PHP Code:


			
$result = mysql_query ($query);

$count = mysql_num_rows($result);

            

echo "<select name=cat_name value=' '>";

echo "<option value=''>View Available               Categories</option>";



for($i=0;$i<$count;$i++) {



$rows = mysql_fetch_array($result);

$table_index = $rows['table_index']; //whatever your index column is

$cat_name = $rows['cat_name'];



if($i != 0) {

echo "<option value=\"$table_index\">$cat_name</option>";

}



}

Wildhoney · 05-07-2009, 04:51 PM

Perhaps you could use mysql_data_seek before you begin to output the results. Normally it will begin at row 0, however by using that function, it will place the pointer to anywhere, so it could start at 5, if you wanted it that way.

php Code:

mysql_data_seek($pResult, 5); /* Specifies the row number. */

Kay1021 · 05-07-2009, 06:30 PM

Thanks for the responses but i don't think they are going to work...maybe i didn't explain myself good...

I have two tables

TABLE 1: postalcode (based on info added by user)

postcode (user entered)
cat_name (chosen from the list in the category table)
status (once user completed the above information this is changed to used)

TABLE 2: category (contains a complete list of pre made categories)

cat_id (just basic numbers)
cat_name (the categories that were pre populated there is like 50)

Now the first sql statement uses the postalcode table and outputs all the cat_name that are in a specific postal code

Example:
CAT
DOG
PIG

Below that I wanted a drop down menu with the complete list of categories from the category table but eliminating the ones already used (ie CAT DOG PIG would not be in the drop down)

My thought was that maybe it could be done by maybe an inner join and comparing the the cat_names and if they are the same not to output...but i can't get it to work...

Or maybe store the results of the first sql statement into an array and then compare the results of the second sql statement against the array and output accordingly.

i'm not that amazing at php i'm still learning...just by code online and playing around so I may totally be off base. But hopefully this gives you a better understanding and more information on what I am looking for.

Thanks again

allworknoplay · 05-07-2009, 07:01 PM

Quote:

Originally Posted by Kay1021

Thanks for the responses but i don't think they are going to work...maybe i didn't explain myself good...

I have two tables

TABLE 1: postalcode (based on info added by user)

postcode (user entered)
cat_name (chosen from the list in the category table)
status (once user completed the above information this is changed to used)

TABLE 2: category (contains a complete list of pre made categories)

cat_id (just basic numbers)
cat_name (the categories that were pre populated there is like 50)

Now the first sql statement uses the postalcode table and outputs all the cat_name that are in a specific postal code

Example:
CAT
DOG
PIG

Below that I wanted a drop down menu with the complete list of categories from the category table but eliminating the ones already used (ie CAT DOG PIG would not be in the drop down)

My thought was that maybe it could be done by maybe an inner join and comparing the the cat_names and if they are the same not to output...but i can't get it to work...

Or maybe store the results of the first sql statement into an array and then compare the results of the second sql statement against the array and output accordingly.

i'm not that amazing at php i'm still learning...just by code online and playing around so I may totally be off base. But hopefully this gives you a better understanding and more information on what I am looking for.

Thanks again

No this can definitely be done quite easily. Let me gather my thoughts after I re-read what you wrote...I might need some examples just to make sure I understand completely..

allworknoplay · 05-07-2009, 07:52 PM

Since I don't know exactly how your DB is structure, you could go with your second idea of storing the output into an array. Then compare what's in the array with your second output and don't show if it's in the array.

So create an array like so:

$first_list = array();

Then in your first query/output.

$cat_name = $rs['cat_name'];

Dump the names into that array.

$first_list[] = $cat_name;

Then in your second query, you can compare the value in the $first_list array and just not output anything that matches...

allworknoplay · 05-07-2009, 08:17 PM

Just to note, you can probably save yourself a query by pulling all the data you need at once from the first query.

Then you can store the data that you need for the second output in an array which already has the duplicates taken out.

Then for the dropdown, you simply loop through the array instead of calling another call to the DB....

Kay1021 · 05-07-2009, 08:28 PM

Hey I really appreciate your help...

Your idea sounds good... unfortunately I haven't really worked with arrays too much...so this is kinda my first time and I'm not sure how to go about what your talking about. I tried the first idea but then i was having trouble trying to write the second query.

Sorry to be a pain

allworknoplay · 05-07-2009, 09:00 PM

Quote:

Originally Posted by Kay1021

Hey I really appreciate your help...

Your idea sounds good... unfortunately I haven't really worked with arrays too much...so this is kinda my first time and I'm not sure how to go about what your talking about. I tried the first idea but then i was having trouble trying to write the second query.

Sorry to be a pain

Pain? No, no pain, you should see what I do to the others around here with all the questions I ask....haha....

We will solve your issue. It's really not hard I know what you're trying to do...

In your first query, within your WHILE loop. You have this:

PHP Code:


			
while ($rs = mysql_fetch_assoc($qry)) {

                         $cat_name = $rs['cat_name'];

                         $postcode = $rs['postcode'];

                         echo $rs['cat_name'] . '<br/>';

                }

Just add my extra line so it takes your cat_names and puts them into the array.

PHP Code:


			
while ($rs = mysql_fetch_assoc($qry)) {

$cat_name = $rs['cat_name'];

$postcode = $rs['postcode'];



$first_list[] = $cat_name;

                         

echo $rs['cat_name'] . '<br/>';

                }

Don't forget to create your array somewhere on the top of your code
with:

$first_list = array();

Then in your second query/output and your WHILE loop just check against the name and don't output if it's listed..

PHP Code:


			
while($nt=mysql_fetch_array($result))

{

if(!in_array($nt[cat_name],$first_list)) {

echo "<option value=''>$nt[cat_name]</option>";

}

}

This should work for you....

Kay1021 · 05-08-2009, 03:56 AM

Thanks

I did exactly what you were saying...i didn't change anything else...except now i get an error

at this line

PHP Code:


			
if(!in_array($nt[cat_name],$first_list)) {

it says

Quote:

Parse error: syntax error, unexpected '{' in

I've double checked all my { and everything....

I'm losing my mind...lol

allworknoplay · 05-08-2009, 01:48 PM

Quote:

Originally Posted by Kay1021

Thanks

I did exactly what you were saying...i didn't change anything else...except now i get an error

at this line

PHP Code:


			
if(!in_array($nt[cat_name],$first_list)) {

it says

I've double checked all my { and everything....

I'm losing my mind...lol

Ohh...hmm, I wonder if it's because of the array operator..

Let's try this instead...

PHP Code:


			


while($nt=mysql_fetch_array($result))

{



$kitty = $nt['cat_name'];



if(!in_array($kitty,$first_list)) {

echo "<option value=''>$kitty</option>";

}

}

Kay1021 · 05-08-2009, 06:27 PM

Man oh man...

still giving me an error.... at the same line

I've gone over and over making sure i have all my semi colons and opening and closing {

If i remove the if statement then it works with no error...but if i put it back then i get the error... even if I change the statement completely to something very basic it still gives me the error

I guess i should just give up on this idea...didn't think it was going to be this hard...just thought it would be a good idea not to have the duplicates. But it's making me lose my mind! lol

Thanks for your help. Greatly appreciated

allworknoplay · 05-08-2009, 06:29 PM

Quote:

Originally Posted by Kay1021

Man oh man...

still giving me an error.... at the same line

I've gone over and over making sure i have all my semi colons and opening and closing {

If i remove the if statement then it works with no error...but if i put it back then i get the error... even if I change the statement completely to something very basic it still gives me the error

I guess i should just give up on this idea...didn't think it was going to be this hard...just thought it would be a good idea not to have the duplicates. But it's making me lose my mind! lol

Thanks for your help. Greatly appreciated

Could you please post your latest code?

Kay1021 · 05-08-2009, 08:12 PM

Ok so i decided i would start again just to make sure everything was good...

so this is what my code looks like...hopefully i followed what you said correctly

PHP Code:


			
<?php

    $origPC = $_POST['postcode'];
    $postcode= strtoupper($origPC);
    $first_list = array();


    $con=mysql_connect(localhost, "root", "root"); 
        mysql_select_db("db"); 
    
    
    if (!$con)
      {
          die('Could not connect: ' . mysql_error());
      }

            $sql = "SELECT cat_name, postcode FROM postalcode
            WHERE postcode = '".$_POST['postcode']."'
            AND status='sold'";

            $qry = mysql_query($sql);
            
            if (mysql_num_rows($qry) > 0) {
                while ($rs = mysql_fetch_assoc($qry)) {
                    $cat_name = $rs['cat_name'];
                    $postcode = $rs['postcode'];
                    $first_list[] = $cat_name;
                     echo $cat_name.'<br/>';
                }
            } else {
                print '<h4>no result found</h4>';
            }
            
        print '<form method="POST" action="index.php?ID=15">';
    
        $query="SELECT DISTINCT cat_name FROM category GROUP BY cat_name";
                            
        $result = mysql_query ($query);
        
        echo "<select name='cat_name' value=''>";
        
                echo "<option value=''>View Other Categories</option>";
                while($nt=mysql_fetch_array($result))
                {
                    $kitty=$nt[cat_name];
                    
                    if(!in_array($kitty,$first_list)) { 
                        echo "<option*value=''>$nt[cat_name]</option>"; 
                    } 
                }
        echo "</select><br/>";
                

        print '<input type="submit" value="Contact Us for More Information"> </form>';
?>

Now when i run it I get a new error

Quote:

Fatal error: Cannot use [] for reading in.... on line 27

which ends up to be

PHP Code:


			
$first_list[] = $cat_name;

this line

So now i'm not sure what's wrong...i tried looking up the error but none of the answers seemed to work or relate to mine.

allworknoplay · 05-08-2009, 08:15 PM

Your code shows this:

$first_list[]*=*$cat_name;

What the heck is that?

It should be:

$first_list[] = $cat_name;

Kay1021 · 05-08-2009, 08:32 PM

It doesn't look like that in my code

for some reason when i copy and pasted it in here *'s starting showing up...i thought i had edited them all out...but i guess i missed that one sorry....but it doesn't look like that in my code.

it looks like this... $first_list[] = $cat_name;

allworknoplay · 05-08-2009, 09:03 PM

Quote:

Originally Posted by Kay1021

It doesn't look like that in my code

for some reason when i copy and pasted it in here *'s starting showing up...i thought i had edited them all out...but i guess i missed that one sorry....but it doesn't look like that in my code.

it looks like this... $first_list[] = $cat_name;

Oh I see...that is curious....

Anyways, I did a mockup of my code and it worked for me so I'm not sure why it's complaining for you...

What I can do is post an example if you'd like, it will take me a little while because I would have to replicate your DB structure, but hopefully that should help you out?

Kay1021 · 05-09-2009, 03:43 PM

Sure thanks...i'd appreciate that

allworknoplay · 05-09-2009, 08:59 PM

Kay:

I have the 2 tables created. If you can please give me some example data, I can populate them and we can put this issue to rest.

Just give me about 10 data results for the postalcode table and 10 for the category table.

That should be enough, and I can then do the dropdown and output.....

Kay1021 · 05-10-2009, 01:32 AM

hey i put together some example data...hopefully it makes sense i didn't know how else to write it out

TABLE category

cat_id | cat_name

1. 1 Bedroom
2. 2 Bathroom
3. 3 Kitchen
4. 4 Living Room
5. 5 Office
6. 6 Garage
7. 7 Family Room
8. 8 Property
9. 9 Attic
10. 10 Basement

TABLE postalcode

postcode | cat_name | status

1. B0H Bedroom used
2. X1A Bathroom used
3. X1A Kitchen used
4. R4A Living Room used
5. G2M Office used
6. B0H Garage used
7. R4A Family Room used
8. G3A Property used
9. B0H Attic used
10. X1A Basement used

thanks