MySql: Problems wtih a select statement

Hei

I'm a newbie to mysql (and PHP) so hopfully this is the right place to post my question.

I'm trying to create a site about travelling, with different travelletters and infofmation about a country.
I have one table that stores information about the the different articles, one which stores the information about countries and the last one which stores information about the different regions (Europe, Asia, Africa....).

So this is the fields in the different tables:

article -> id, artname, url, countryid
country -> id, cname, regionid
region -> id rname
(The id's, countryid and regionid are all integers)

An article can only be assigned to one country, but a country can be assigned to many different articles.
A country can only be assigned to one region, but a region can be assigned to many different countries.

The problem is:
I'm trying to make the navigation system for my site. So when the user choose a region all the countries in the region are going to be displayed. So far I'm able to help myself. I use this code to that:
(the variable $rid is being passed from the link that the useres clicks on when they choose a region)

This code displays all the countries wich are in the same region.

But I also want to do is this:
I only want to display the countries which have an article assigned to itself (This means that there can be countries in the table which are not assigned to an article). And I only want to display the country ones. So even if there's three articles about one country I only want the country to be displayed ones.
Could anyone help out whith a select statement that take care of this?

Hopefully I've made my explenation good enough.

-Helge

PS! I'm soory that my english aren't to good.

Hi

I sort of understand what you are wanting..........


But to me I would do things a little different.......


you say you have 3 tables......

article

country

regions


The key to all of this seems (REGIONS).......


I would use only one table...........


regions

key_id = region id


Example....

region [1] = North America

region [2] = South America

region [3] = Europe

region [4] = Asia

region [5] = Africa



So a table could look like this......




$key_id', '$key_country', '$region_country', '$article', '$date_added', '$last_update'



your page (html) could then call a region...........


and send the first result................

Like..............

This......

user selects region[1]........


the database is called and outputs a list of "countries" that region contains...........

$sql = "SELECT key_id FROM regions WHERE (key_id = '$what_region_the_user_selected')";

From there the user selects a country, that was pulled from the last query.....


$sql = "SELECT key_id, key_country FROM regions WHERE (key_id = '$first form selection') AND (key_country = '$country_selected')";


From there the country will be displayed, and you can then display genral country info based on the $region_country or on the date it was add or updated........


To me I would rethink your database structure.......


Just my thoughts....

F!

Thank you Fataqui for your reply.

I kind of understanding your suggestion, but I don't think I would like to redisgn my database. I do have a lot more coulms in each table than I wrote in the last post.

But I found a solution. It's not a good soultion cause I do some while-loops to many times, but I think it will work for now.

Here's my solution;

Thanks again, Fataqui, for your reply.

-Helge

Regardless of the previous solution I think you've done your database correctly, its the way I got taught to do it at Uni. Anyway to answer your first query, your query isn't working because you haven't referenced the region table in the from part, i.e.

SELECT country.id, country.cnavn FROM country, region WHERE region.id='$rid'

If I remember my PHP this should now work. If you have anymore problems I'd suggest using the actual PHP forum on Dev Shed as you'll probably get a better response.

Thank you. You're right.

Actually I see that I should not have referenced to the table region at all. This is what the statement should be:

I probaly move this topic to the PHP or MySql forums to get help with only displaying the countries which have an article assigned to itself.

-Helge

Here's how I'd do the SQL. And your table design is correct, you've normalized your data, which is the proper way to do this, to avoid repetitious data, among other reasons.



or, to get the same and integrate the region select,



or, if you can't pass the regionid, you can use the region name



These would get the country name once for every country that has a country id in the article table. This is known as a join, the mySQL manual has an OK description of them.

Let the SQL engine do the work for you. As long as your column types and sizes are the same for the column you compare and your columns are indexed, this should be much faster, given that you can easily do this with one SQL statement.

Thank you, Hero Zzyzzx!!

This was exactly what I was looking for. It works great!

-Helge

No problem! Quite a bit easier and faster than all those loops you had, huh?

A I mentioned above, mySQL has a serious limitation with joins in that each column being compared has to be indexed and of the same type and size (e.g. all varchar(20) if that's how your doing them) otherwise mySQL won't use indeces, making your selects far slower.

You can "alter table" to fix them without worrying about losing data, one advantage that mySQL has over postgreSQL.

Good luck! Joins rock.