List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:October 17 2002 3:29am
Subject:Two queries, can I make it one?
View as plain text  
Lo everyone,

I have a bit of a dilemma.  I use MySQL for all my authentication (user
accounts etc, via ftp / mail / radius / http / etc), and currently am using
PureFTPD as my FTPD to allow my clients to upload web sites.

I use the following to authenticate users for their personal web space...

SELECT Password FROM UserAccounts RIGHT JOIN DNSZones ON
UserAccounts.ZoneID=DNSZones.ZoneID WHERE UserAccounts.Username=LEFT('\L',
(LOCATE('@', '\L') -1)) AND UserAccounts.StatusID < 10 AND
DNSZones.ZoneName='' LIMIT 1;

\L is replaced by the username, where the usernames looks at

Now, this is all very nice and simple, but it is limited in the sense that I
can only allow that query on the one web site.  In other words, I'll need to
run at least two different FTP servers (one for user web sites, and one for
other web sites).

In the other web sites, I'll be using something like this:

WebAuth.ZoneID=DNSZones.ZoneID WHERE WebAuth.UserName=LEFT('\L',
(LOCATE('@', '\L') -1)) AND WebAuth.StatusID < 10 LIMIT 1;

In this instance, the user account will be something like

Now, is there a way that I can do both those queries in one go?  Basically,
I want to provide PureFTP with one SQL query, that will either return the
account details on the user account (username@stripped), OR, for a
virtual web site (username@stripped)

If I can write this quick of what I have in mind, I'm looking for something

if (\L LIKE ' {
  the user is authenticating for their personal web space
} else {
  the user is authenticating for a dedicated virtual host

Is this possible??? :/



Two queries, can I make it one?Chris Knipe17 Oct