SQL query help please

I do a query to MySQL using PHP. Well actually I do too many of them
which is the problem, it's too slow. I think maybe an inner join or
something would be better but I can't figure this out.

The web page on which the data is displayed has a list of countries.
Each country has a label heading under which websites are listed that
have information on that country. Any given website may appear many
times (under different countries) if it has information on that country.

I've got three tables. They are:
country - A list of countries and their id key.
data_pairs - Country and website id pairs
data - A list of websites and their id key.

I do a query to get all the countries, then a while loop to print a
heading for each country. Inside this while loop is another query and
while loop to get all the website id's that have information on that
country. Inside this while loop is another query and while loop to
get the websites and print them.

So to produce a medium size web page I'm doing like fifty queries!
It takes a few seconds, I'm surprised it works at all. Is there a
way to do all of this in one fell swoop, one query?

Thanks for your help.

The PHP code is listed below:
-------------------------------------------------------------

function GetData()
{
$link = $this->MakeConn();

mysql_select_db("gdk") or die("Could not select database");

// Get all the countries (id, country FROM country table)
$query = "SELECT * FROM country ORDER BY country";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

// Iterate thru the countries and print their heading label
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
$typeNum = $row[0]; // the country id
printf ("<div class=\"Box\">%s</div>", $row[1]); // print the heading

// get the website id's for this one country
// (site_id, country_id, alpha FROM data_pairs table)
$query2 = "SELECT * FROM data_pairs WHERE country_id = $typeNum ORDER BY alpha";
$result2 = mysql_query($query2) or die("Query failed : " . mysql_error());

/* I now have all the site id's for this one country.
Do query to get the websites. Do one query for each
country because the sites have nothing in common.
*/

// Iterate thru the site id's and print them.
while ($row2 = mysql_fetch_array($result2, MYSQL_NUM))
{
// Get a link (for this country)
$num = $row2[0];
// Get the site row (id, site_name, site_URL FROM data table)
$query3 = "SELECT * FROM data WHERE id = $num";
$result3 = mysql_query($query3) or die("Query failed : " . mysql_error());
$site = mysql_fetch_array($result3, MYSQL_NUM);
printf("<a href=\"$site[2]\" target=\"_blank\">$site[1]</a></br>");
}
printf("<br>");

} //while

// Free resultset
mysql_free_result($result);
// Close connection
mysql_close($link);

} //function

re: SQL query help please

Bruce W...1 wrote:
[color=blue]
> I do a query to MySQL using PHP. Well actually I do too many of them
> which is the problem, it's too slow. I think maybe an inner join or
> something would be better but I can't figure this out.
>
> The web page on which the data is displayed has a list of countries.
> Each country has a label heading under which websites are listed that
> have information on that country. Any given website may appear many
> times (under different countries) if it has information on that country.
>
> I've got three tables. They are:
> country - A list of countries and their id key.
> data_pairs - Country and website id pairs
> data - A list of websites and their id key.[/color]

I'm not sure, but I hope I understood your question correctly. It would
have been nice if you would have provided more information about your
tables or in more readable format (See the example I provided).

# Create tables
create table country(id int, name varchar(255));
create table data(id int, address varchar(255));
create table data_pairs( country_id int,data_id int );

# Insert test material
insert into country values(1,'Finland');
insert into country values(2,'Sweden');

insert into data values( 1, 'www.google.com');
insert into data values( 2, 'www.google.fi');
insert into data values( 3, 'www.google.se');
insert into data values( 4, 'www.google.de');

insert into data_pairs values(1,1);
insert into data_pairs values(1,2);
insert into data_pairs values(2,1);
insert into data_pairs values(2,3);

# Query
select country.id as country_id,name,address
from country,data,data_pairs where
country.id = data_pairs.country_id and
data.id = data_pairs.data_id order by country_id;

# Result
+------------+---------+----------------+
| country_id | name | address |
+------------+---------+----------------+
| 1 | Finland | www.google.com |
| 1 | Finland | www.google.fi |
| 2 | Sweden | www.google.com |
| 2 | Sweden | www.google.se |
+------------+---------+----------------+

Hope this is what you wanted?

re: SQL query help please

Bruce W...1 wrote:
[color=blue]
> I do a query to MySQL using PHP. Well actually I do too many of them
> which is the problem, it's too slow. I think maybe an inner join or
> something would be better but I can't figure this out.
>
> The web page on which the data is displayed has a list of countries.
> Each country has a label heading under which websites are listed that
> have information on that country. Any given website may appear many
> times (under different countries) if it has information on that country.
>
> I've got three tables. They are:
> country - A list of countries and their id key.
> data_pairs - Country and website id pairs
> data - A list of websites and their id key.[/color]

I'm not sure, but I hope I understood your question correctly. It would
have been nice if you would have provided more information about your
tables or in more readable format (See the example I provided).

# Create tables
create table country(id int, name varchar(255));
create table data(id int, address varchar(255));
create table data_pairs( country_id int,data_id int );

# Insert test material
insert into country values(1,'Finland');
insert into country values(2,'Sweden');

insert into data values( 1, 'www.google.com');
insert into data values( 2, 'www.google.fi');
insert into data values( 3, 'www.google.se');
insert into data values( 4, 'www.google.de');

insert into data_pairs values(1,1);
insert into data_pairs values(1,2);
insert into data_pairs values(2,1);
insert into data_pairs values(2,3);

# Query
select country.id as country_id,name,address
from country,data,data_pairs where
country.id = data_pairs.country_id and
data.id = data_pairs.data_id order by country_id;

# Result
+------------+---------+----------------+
| country_id | name | address |
+------------+---------+----------------+
| 1 | Finland | www.google.com |
| 1 | Finland | www.google.fi |
| 2 | Sweden | www.google.com |
| 2 | Sweden | www.google.se |
+------------+---------+----------------+

Hope this is what you wanted?

re: SQL query help please

Bruce W...1 wrote:
[color=blue]
> I've got three tables. They are:
> country - A list of countries and their id key.
> data_pairs - Country and website id pairs
> data - A list of websites and their id key.[/color]

One other thing. If you have a table where you have websites, why do you
call that table as "data" why not "website"?

re: SQL query help please

Bruce W...1 wrote:
[color=blue]
> I've got three tables. They are:
> country - A list of countries and their id key.
> data_pairs - Country and website id pairs
> data - A list of websites and their id key.[/color]

One other thing. If you have a table where you have websites, why do you
call that table as "data" why not "website"?

re: SQL query help please

Aggro wrote:[color=blue]
>
> Bruce W...1 wrote:
>[color=green]
> > I've got three tables. They are:
> > country - A list of countries and their id key.
> > data_pairs - Country and website id pairs
> > data - A list of websites and their id key.[/color]
>
> One other thing. If you have a table where you have websites, why do you
> call that table as "data" why not "website"?[/color]
================================================== ====

Your point about the table name 'data' is well taken, but there's a
reason for this. Originally I was going to put all the websites into
one table (for two different web pages) but it got too big and hairy so
I broke them out into two tables. One table has websites that have data
on a country, another has websites that have news on a country.

Back to the matter at hand, guess my PHP code will need to detect each
new country in the dataset and print a country group heading when
appropriate.

On the web page the list of countries is ordered alphabetically. And
the list of websites under each country is also ordered alphabetically.
So there are two or more SORT's that need to be done, first for the
country names, then for the websites under each country. How does this
fit in your query?

The tables were created with MySQL Control Center so I don't have the
create statements but here are the table definitions.

Table 'data'
------------
id int
site_name varchar
site_url varchar

Table 'data_pairs'
------------------
site_id int
country_id int
alpha tinytext - First two characters of website name.
Just for sorting purposes.
May not be necessary.
Table 'country'
---------------
id int
country varchar

Thanks again.

re: SQL query help please

Aggro wrote:[color=blue]
>
> Bruce W...1 wrote:
>[color=green]
> > I've got three tables. They are:
> > country - A list of countries and their id key.
> > data_pairs - Country and website id pairs
> > data - A list of websites and their id key.[/color]
>
> One other thing. If you have a table where you have websites, why do you
> call that table as "data" why not "website"?[/color]
================================================== ====

Your point about the table name 'data' is well taken, but there's a
reason for this. Originally I was going to put all the websites into
one table (for two different web pages) but it got too big and hairy so
I broke them out into two tables. One table has websites that have data
on a country, another has websites that have news on a country.

Back to the matter at hand, guess my PHP code will need to detect each
new country in the dataset and print a country group heading when
appropriate.

On the web page the list of countries is ordered alphabetically. And
the list of websites under each country is also ordered alphabetically.
So there are two or more SORT's that need to be done, first for the
country names, then for the websites under each country. How does this
fit in your query?

The tables were created with MySQL Control Center so I don't have the
create statements but here are the table definitions.

Table 'data'
------------
id int
site_name varchar
site_url varchar

Table 'data_pairs'
------------------
site_id int
country_id int
alpha tinytext - First two characters of website name.
Just for sorting purposes.
May not be necessary.
Table 'country'
---------------
id int
country varchar

Thanks again.

re: SQL query help please

Bruce W...1 wrote:
[color=blue]
> I do a query to MySQL using PHP. Well actually I do too many of them
> which is the problem, it's too slow. I think maybe an inner join or
> something would be better but I can't figure this out.
>[/color]

[snip]
[color=blue]
> I've got three tables. They are:
> country - A list of countries and their id key.
> data_pairs - Country and website id pairs
> data - A list of websites and their id key.[/color]
[snip]
[color=blue]
> So to produce a medium size web page I'm doing like fifty queries!
> It takes a few seconds, I'm surprised it works at all. Is there a
> way to do all of this in one fell swoop, one query?[/color]

[snip]

I'll leave the PHP re-write to you. However, you're correct, there is a
way to do this in one fell swoop:

SELECT country.name
FROM data_pairs
LEFT JOIN country ON country.id = data_pairs.countryid

will give you all the countries that have websites - one row per website
in that country. Note that any countries without web sites will be
skipped. Now add in the websites:

LEFT JOIN data ON data.id = data_pairs.websiteid

and change the SELECT to:
SELECT country.name, data.website

In order to simplify the PHP, you may want to add:
ORDER BY country.name

HTH,
- Mark

re: SQL query help please

Bruce W...1 wrote:
[color=blue]
> I do a query to MySQL using PHP. Well actually I do too many of them
> which is the problem, it's too slow. I think maybe an inner join or
> something would be better but I can't figure this out.
>[/color]

[snip]
[color=blue]
> I've got three tables. They are:
> country - A list of countries and their id key.
> data_pairs - Country and website id pairs
> data - A list of websites and their id key.[/color]
[snip]
[color=blue]
> So to produce a medium size web page I'm doing like fifty queries!
> It takes a few seconds, I'm surprised it works at all. Is there a
> way to do all of this in one fell swoop, one query?[/color]

[snip]

I'll leave the PHP re-write to you. However, you're correct, there is a
way to do this in one fell swoop:

SELECT country.name
FROM data_pairs
LEFT JOIN country ON country.id = data_pairs.countryid

will give you all the countries that have websites - one row per website
in that country. Note that any countries without web sites will be
skipped. Now add in the websites:

LEFT JOIN data ON data.id = data_pairs.websiteid

and change the SELECT to:
SELECT country.name, data.website

In order to simplify the PHP, you may want to add:
ORDER BY country.name

HTH,
- Mark

re: SQL query help please

Bruce W...1 wrote:
[color=blue]
> On the web page the list of countries is ordered alphabetically. And
> the list of websites under each country is also ordered alphabetically.
> So there are two or more SORT's that need to be done, first for the
> country names, then for the websites under each country. How does this
> fit in your query?[/color]

.... order by country.country, data.site_name;

I also should remind you that when using the query I gave you, you will
NOT get list of countries that have no websites attached to them.

re: SQL query help please

Bruce W...1 wrote:
[color=blue]
> On the web page the list of countries is ordered alphabetically. And
> the list of websites under each country is also ordered alphabetically.
> So there are two or more SORT's that need to be done, first for the
> country names, then for the websites under each country. How does this
> fit in your query?[/color]

.... order by country.country, data.site_name;

I also should remind you that when using the query I gave you, you will
NOT get list of countries that have no websites attached to them.

re: SQL query help please

Mark Sizer wrote:[color=blue]
>
> Bruce W...1 wrote:
>[color=green]
> > I do a query to MySQL using PHP. Well actually I do too many of them
> > which is the problem, it's too slow. I think maybe an inner join or
> > something would be better but I can't figure this out.
> >[/color]
>
> [snip]
>[color=green]
> > I've got three tables. They are:
> > country - A list of countries and their id key.
> > data_pairs - Country and website id pairs
> > data - A list of websites and their id key.[/color]
> [snip]
>[color=green]
> > So to produce a medium size web page I'm doing like fifty queries!
> > It takes a few seconds, I'm surprised it works at all. Is there a
> > way to do all of this in one fell swoop, one query?[/color]
>
> [snip]
>
> I'll leave the PHP re-write to you. However, you're correct, there is a
> way to do this in one fell swoop:
>
> SELECT country.name
> FROM data_pairs
> LEFT JOIN country ON country.id = data_pairs.countryid
>
> will give you all the countries that have websites - one row per website
> in that country. Note that any countries without web sites will be
> skipped. Now add in the websites:
>
> LEFT JOIN data ON data.id = data_pairs.websiteid
>
> and change the SELECT to:
> SELECT country.name, data.website
>
> In order to simplify the PHP, you may want to add:
> ORDER BY country.name
>
> HTH,
> - Mark[/color]

================================================== ==

Thanks guys. I got it to work with this:

$query = "SELECT country.country, data.site_name, data_pairs.alpha
FROM country, data, data_pairs
WHERE country.id = data_pairs.country_id
AND data.id = data_pairs.site_id
ORDER BY country.country, data.site_name";

And I was able to get the same results using a JOIN statement too. This
leaves me confounded. Why have a JOIN statement at all when a WHERE
does the job?

re: SQL query help please

Mark Sizer wrote:[color=blue]
>
> Bruce W...1 wrote:
>[color=green]
> > I do a query to MySQL using PHP. Well actually I do too many of them
> > which is the problem, it's too slow. I think maybe an inner join or
> > something would be better but I can't figure this out.
> >[/color]
>
> [snip]
>[color=green]
> > I've got three tables. They are:
> > country - A list of countries and their id key.
> > data_pairs - Country and website id pairs
> > data - A list of websites and their id key.[/color]
> [snip]
>[color=green]
> > So to produce a medium size web page I'm doing like fifty queries!
> > It takes a few seconds, I'm surprised it works at all. Is there a
> > way to do all of this in one fell swoop, one query?[/color]
>
> [snip]
>
> I'll leave the PHP re-write to you. However, you're correct, there is a
> way to do this in one fell swoop:
>
> SELECT country.name
> FROM data_pairs
> LEFT JOIN country ON country.id = data_pairs.countryid
>
> will give you all the countries that have websites - one row per website
> in that country. Note that any countries without web sites will be
> skipped. Now add in the websites:
>
> LEFT JOIN data ON data.id = data_pairs.websiteid
>
> and change the SELECT to:
> SELECT country.name, data.website
>
> In order to simplify the PHP, you may want to add:
> ORDER BY country.name
>
> HTH,
> - Mark[/color]

================================================== ==

Thanks guys. I got it to work with this:

$query = "SELECT country.country, data.site_name, data_pairs.alpha
FROM country, data, data_pairs
WHERE country.id = data_pairs.country_id
AND data.id = data_pairs.site_id
ORDER BY country.country, data.site_name";

And I was able to get the same results using a JOIN statement too. This
leaves me confounded. Why have a JOIN statement at all when a WHERE
does the job?

re: SQL query help please

I should add that the JOIN version seems to be faster, so I think I'll
use this instead:

$query = "SELECT country.country, data.site_name
FROM data_pairs
INNER JOIN country ON country.id = data_pairs.country_id
INNER JOIN data ON data.id = data_pairs.site_id
ORDER BY country.country, data.site_name";

And I can appreciate the benefits of left and right joins but that's not
a problem right now.

re: SQL query help please

I should add that the JOIN version seems to be faster, so I think I'll
use this instead:

$query = "SELECT country.country, data.site_name
FROM data_pairs
INNER JOIN country ON country.id = data_pairs.country_id
INNER JOIN data ON data.id = data_pairs.site_id
ORDER BY country.country, data.site_name";

And I can appreciate the benefits of left and right joins but that's not
a problem right now.