From: jkraai
Date: March 25 1999 5:47am
Subject: Re: SELECT on a one to many relationship
List-Archive: http://lists.mysql.com/mysql/930
Message-Id: <36F9CDE8.41A54115@polytopic.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Christian Mack wrote:
> 
> Cyril Godefroy wrote:
> >
> > Please excuse me if this is a bit off-topic, and point me to the right list.
> >
> > I'm using MySQL on a web site I'm creating, in conjuncyion with PHP. I love
> > it. But it's my very first taste of SQL.
> >
> > One of the areas of the site is a query on ISP in France, and this is where
> > I have a problem. I created my tables like that:
> > Provider (ID, Name, Adress, Web, etc)
> > Package (ID, ProviderID, Price, MonthlyTransfer, OS,...)
> > Feature (ID, Name, Description)
> > FeatureToPackage (FeatureID, PackageID)
> >
> > This way, I know which providers use PHP as a feature, or give Database
> > Access, or Email aliases, etc. Unfortunately, despite my reading the
> > documentation, tutorials, I'm still unable to find which providers use PHP
> > AND databases AND SSL (for example).
> >
> > Got any tips or URLs to a good recipe? Is there a design error?
> >
> > Thanks in advance
> > ==================================
> > Cyril Godefroy

A little more PHP'ish (and more general) way to do 
this could look something like this:



> Hi Cyril
> 
> The design is O.K. but the query is a little bit tricky.
> Try:
> SELECT DISTINCT
>         Provider.*
> FROM
>         Provider
>         , Package
>         , FeatureToPackage AS ftp1
>         , Feature AS f1
>         , FeatureToPackage AS ftp2
>         , Feature AS f2
>         , FeatureToPackage AS ftp3
>         , Feature AS f3
> WHERE
>         Provider.ID=Package.ProviderID
>         AND Package.ID=ftp1.PackageID
>         AND ftp1.FeatureID=f1.ID
>         AND f1.Name='PHP'
>         AND Package.ID=ftp2.PackageID
>         AND ftp2.FeatureID=f2.ID
>         AND f2.Name='databases'
>         AND Package.ID=ftp3.PackageID
>         AND ftp3.FeatureID=f3.ID
>         AND f3.Name='SSL'
> 
> Tschau
> Christian

A little more PHP'is (and general) solution 
might look like this (hope you're using plenty 
of well-placed indexes):

<?php

function getProviders($names,$minMatch)
{
// database handle from prior mysql_connect
global $dbh; 

// Use PHP string from array function
$inClause = implode("','",$names);

$result = mysql_query($dbh,"
	select 
		Provider.*
		, count(*) as PrCount
	from 
		Provider As Pr
		, Package As Pa
		, FeatureToPackage As FP
		, Feature As F
	where
		Pr.ID=Pa.ID
		AND Pa.ID = FP.PackageId
		AND FP.FeatureID=F.ID
		AND F.Name in ('$inClause')
	group by 
		Pr.ID
	having 
		PrCount >= $minMatch");

return $result;

}

$names[] = 'PHP';
$names[] = 'databases';
$names[] = 'SSL';

// Match as many items as are in the array
$numToMatch = count($names);

$ProvidersResult = getProviders($names,$numToMatch);

while ($row = mysql_fetch_array($ProviderResult))
	echo $row["Name"]."<br>\n";

?>

--jim