What no results?

[steve55]

I have some code that populates a drop down menu but I have a few questions regarding it..

---

<form action="results.php" method="POST">

<?php

include "config.php";

$db = mysql_connect ($Host, $User, $Password);
mysql_select_db ($DBName) or die ("Cannot connect to database");
/ We have now connected, unless you got an error message /

function select_menu_areas( $nam )
{
$sel = '<select name="' . $nam . '">';
$sel .= '<option selected>Choose An Area</option>';
$qry = mysql_query( "SELECT DISTINCT id, areas FROM houses" );
while ( $dat = mysql_fetch_array( $qry ) ) {
$sel .= '<option value="' . $dat['id'] . '">' . $dat['areas'] . '</option>' . "\n";
}
$sel .= '</select>';
return( $sel );
}

?>

<strong><font size="2" face="Arial, Helvetica, sans-serif">Select from the menu</font></strong><br>
<br>

<?php
echo select_menu_areas( 'cities' );

?>

<INPUT TYPE="HIDDEN" NAME="id">
<input type="Submit" name="Submit" value="Submit">
</form>

---

1/ The DISTINCT command isn't working - I'm getting all entries being listed in the drop down, including duplicates.

2/ I would like to create a results page but have got stuck! So would like some help producing one.

3/ Can I amend the code so I could have three drop down menus in total (one for areas, one for towns and one for zipcodes).

4/ If I have three drop downs I would need to amend the code so that if 'Choose an Area' was the selection all areas would be listed and the same with towns and zipcodes...

I have got a headache - simple explanations/replies would be much appreciated!

[press711]

DISTINCT returns unique results
The DISTINCT keyword returns only unique rows within a result set. You may need to find out, for example, which companies are represented in your Sales table, but you don’t want to see every entry. You can use DISTINCT to return one row for each unique company name:
SELECT DISTINCT company FROM Sales;

When you use DISTINCT, it applies to all requested columns. If you want a list of all the salespeople in your table and the companies they represent but not every sales entry, you can use the following statement. Note that this may return several entries from the same company, etc. DISTINCT applies to the entire requested result set.
SELECT DISTINCT company, last_name, first_name FROM Sales;

Excerpt from : http://builder.com.com/article.jhtml?id=u00320020628dol01.htm

I am not sure about the dynamic population the only way is if you dynamically generate the javascript which onchange populates the other two selects.

Cheers,
Kunal 🙂

[steve55]

I have updated my script with this - SELECT DISTINCT id, areas FROM houses ORDER BY areas

But still get the same results, which you can see here:-

http://www.stevesims.com/menutest.php

[press711]

Originally posted by steve55
I have updated my script with this - SELECT DISTINCT id, areas FROM houses ORDER BY areas

But still get the same results, which you can see here:-

http://www.stevesims.com/menutest.php

OK I havent tried this but the best way I can think of is ;
SELECT DISTINCT areas FROM houses ORDER BY areas

And just the area name in the value as well . Since each area has only one corresponding id and since the area names are unique, you can simply do your selection on the bases of the "areas" field. OR if you need the id on the next field simply do this on the next page where you are posying the select box form :

"SELECT id FROM houses WHERE 'areas' = ".$HTTP_POST_VARS["CITIES"]." LIMIT 1";

I hope that helps.

I would recommend you to check the output of "SELECT DISTINCT areas FROM houses ORDER BY areas" and hopefully that should display the desired area codes and then you can figure out a way to get corresponding ids for the area.

Cheers,
Kunal

[steve55]

Same result again I'm afraid - is there another way of doing what I want..?

i.e produce a drop down menu and then clicking a button to view a results page...

Perhaps it needs a complete rewrite..

[press711]

Originally posted by steve55
Same result again I'm afraid - is there another way of doing what I want..?

i.e produce a drop down menu and then clicking a button to view a results page...

Perhaps it needs a complete rewrite..

Well the simplest way to do it is to create a new table and store all areas in it , a new table for cities and another one for towns, then you can use id of each as a foreign key in the other table to link them up.

The other way is when you are loopuing through the results of the SELECT query, and storing the results in an array. but before putting the result into the array, check if it already exists in the array, if it does then dont put it into the array.

Cheers,
Kunal

[siwis]

Might be too simple a suggestion but what about trying this as your sql:

SELECT areas FROM houses GROUP BY areas

I do, however, have to concur with a previous post that, ideally, areas should be a seperate table, each area having an ID and the corresponding ID being marked in the individual house records.

I don't however quite see why you are selecting an id in your query. Is the id unique to the area or the house record?

[press711]

My knoledge of SQL isn't too strong .
But I looked up GROUP BY and I agree theoretically it should work.

[router]

i have no idea, but "DISTINCT" works at least in my code, so i advice you that maybe if you add a "ORDER BY " in your select it works...
I don't know, bur try it and say me , please!