dbGetRows from two tables

[Guest]

I have a Mysql table with many fields however the two I am battling with are called Venues and Country. Country has over 300 names in it while Venues has listing of venues and one of the fileds is Country . i.e. where that particular venue is located. Now IN my search I have code that will search for venues where there is a venue that is in the Country Drop down list which the user will slect but I only wnat that list populated with contry names where there is in fact a venue listed....At the moment the Country Drop down list is listing ALL the countries from the country table but I ONLY want the drop down list to have those countries in it that are currently in the Venues table and to o ly list that Country name on once although there may be more than one venie in the same country listed.

My code currently looks like this:

<td width="40%">Country</td>
                        <td width="60%"> <select name="country">
                            <option value="%" show="none">All</option>
                            <?php
                  $countries = dbGetRows("country", "name != ''");
                  while($country = mysql_fetch_array($countries, MYSQL_ASSOC) ) {
                    echo "<option value=\"".$country['name']."\" ".($_POST['country'] == $country['name'] ? "selected" : "")."".($country['name'] == 'South Africa' ? "show=\"provinces\"" : "show=\"none\"").">".$country['name']."</option>\n";

 

The tables are listed as Country.id and Country.Name and Venues.Country

 

Hope someone can help as been battling with this as a newbie...

[Psycho]

You don't state what Venues.Country is a foreign key reference for. It SHOULD be the Country.id field and NOT the Country.Name field. But, I have a feeling that isn't the case.

 

Anyway, you just need a simple query to get the list of countries where there are existing venues

 

SELECT Country.id, Country.name
FROM Country
RIGHT JOIN Venues ON Country.id = Venues.Country
GROUP BY Country.id

[Guest]

Hi mjdamato,

Many thanks for taking time out to help us newbies.

I can get the results that I need on a pure sql query but my problem is getting it into the code I posted earlier.  Sorry, I forgot to mention that I "inherted" this application in a half built state so the code I posted was not my own

 

How would I alter the code I posted to give me the same results into a select list?

 

Rgds

[Psycho]

I gave you the answer, I'm sorry if it doesn't fulfill your expectations. dbGetRows is not a PHP function. I can't alter what the function returns. Yes you could insert some inefficient code to keep countries from duplicating. But, you couldn't "remove" any values that don't have associated venues without running a separate query for each country. That just makes no sense.

 

Replace this

$countries = dbGetRows("country", "name != ''");

 

With something like this:

$query = "SELECT Country.id, Country.name
    FROM Country
    RIGHT JOIN Venues ON Country.id = Venues.Country
    GROUP BY Country.id";
$countries = mysql_query($query);