Re: find (matching) person in other table - Page 2

**RE: [PHP] Re: find (matching) person in other table -** 06-02-2007, 08:56 PM

Jumping in late so forgive if I'm mistaken, but can't you just use
"HAVING" in place of "WHERE"

> > select member_id, first_name, last_name, email, ...,
> > (5*(first_name='$first_name) +
> 2*(first_name='$first_name')) as score
> > from members
HAVING score > 0

> -----Original Message-----
> From: Richard Lynch [private.php?do=newpm&u=]
> Sent: Thursday, May 31, 2007 3:42 PM
> To: Afan Pasalic
> Cc: Jared Farrish; php-EMAIL REMOVED
> Subject: Re: [PHP] Re: find (matching) person in other table
>
> MySQL doesn't let you use the calculated values (score) in the where
> clause.
>
> PostgreSQL does, as I recall.
>
> Sorry.
>
> You may be able to get around that with:
>
> Do a GROUP BY on something unique, so the GROUP BY is pointless, but
> then you can use HAVING score > 0
>
> Use a sub-query in MySQL 4.mumble or higher, and the outer query can
> use 'score' to get rid of the 0-point non-matches
>
> Build up the expression for $score and $where at the same time like:
>
> $where = ' 0 ';
> $score = ' 0 ';
> $where .= " OR lastname = '$lastname';
> $score .= " + 5 * (lastname = '$lastame' ) ";
> $where .= " OR firstname = '$firstname' ";
> $score .= " + 2 * (firstname = '$firstname' ) ";
>
> This gets tiresome to type, but is least confusing to
> non-programmers...
>
> On Thu, May 31, 2007 9:36 am, Afan Pasalic wrote:
> >
> >
> > Jared Farrish wrote:
> >> On 5/30/07, Afan Pasalic <EMAIL REMOVED> wrote:
> >> email has to match "in total". EMAIL REMOVED and
> >> EMAIL REMOVED
> >>> are NOT the same in my case.
> >>>
> >>> thanks jared,
> >>
> >> If you can match a person by their email, why not just SELECT by
> >> email
> >> only
> >> (and return the persons information)?
> > 'cause some members can be added to database by administrator and
> > maybe
> > they don't have email address at all. or several memebers
> can use the
> > same email address (EMAIL REMOVED) and then macthing
> last name
> > is
> > kind of "required". that's how it works now and can't change it.
> >
> >> Consider, as well, that each time you're calling a database, you're
> >> slowing
> >> down the response of the page. So, while making a bunch of small
> >> calls
> >> might
> >> not seem like that much, consider:
> >>
> >> ||||||| x |||||||
> >> ||||||| a |||||||
> >> ||||||| b |||||||
> >>
> >> Versus
> >>
> >> ||||||| x, a, b |||||||
> >>
> >> The letters represent the request/response data (what you're giving
> >> to
> >> get,
> >> then get back), and the pipes (|) are the overhead to
> process, send,
> >> receive
> >> (on DB), process (on DB), send (on DB), receive, process, return to
> >> code.
> >>
> >> The overhead and latency used to complete one request makes it a
> >> quicker,
> >> less "heavy" operation. If you did the first a couple hundred or
> >> thousand
> >> times, I would bet your page would drag to a halt while it loads...
> > agree. now, I have to figure it out HOW? :-)
> >
> > I was looking at levenshtein, though, I think the richard's solution
> > is
> > just enough:
> >
> > select member_id, first_name, last_name, email, ...,
> > (5*(first_name='$first_name) +
> 2*(first_name='$first_name')) as score
> > from members
> > where score > 0
> >
> > though, I'm getting error: "Unknown column 'score' in where
> clause"?!?
> >
> > thanks jared.
> >
> > --
> > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
>
> --
> Some people have a "gift" link here.
> Know what I want?
> I want you to buy a CD from some indie artist.
> http://cdbaby.com/browse/from/lynch
> Yeah, I get a buck. So?
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>