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
