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.
> SELECT DISTINCT
> , Package
> , FeatureToPackage AS ftp1
> , Feature AS f1
> , FeatureToPackage AS ftp2
> , Feature AS f2
> , FeatureToPackage AS ftp3
> , Feature AS f3
> 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'
A little more PHP'is (and general) solution
might look like this (hope you're using plenty
of well-placed indexes):
// database handle from prior mysql_connect
// Use PHP string from array function
$inClause = implode("','",$names);
$result = mysql_query($dbh,"
, count(*) as PrCount
Provider As Pr
, Package As Pa
, FeatureToPackage As FP
, Feature As F
AND Pa.ID = FP.PackageId
AND F.Name in ('$inClause')
PrCount >= $minMatch");
$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))