SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jul 2005
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    advanced mysql searches with php

    A two-part question. First, I would like to know if it's possible to search multiple fields as follows (for example): surname=Jones AND (firstname=John OR firstname=Jane) to return only 'John Smith' and 'Jane Smith'. I'm afraid my server provider currently uses MySQL 3.23.58 and I couldn't find anything in the documentation which seemed relevant.

    Secondly, if this is possible, I'd like to set up php code to take advantage of it. (Currently, the search form has an OR option, but of course does not allow for an AND with a subgroup of multiple OR's.)

    Thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by NEOC
    ...if it's possible to search multiple fields as follows (for example): surname=Jones AND (firstname=John OR firstname=Jane) to return only 'John Smith' and 'Jane Smith'.
    um, no

    because the following, which is actual SQL ...
    Code:
     where surname = 'Jones'
       and ( firstname = 'John'
          or firstname = 'Jane'
           )
    or, this, which is equivalent ...
    Code:
     where surname = 'Jones'
       and firstname in ( 'John', 'Jane' )
    ... will both return only 'John Jones' and 'Jane Jones', not 'John Smith' and 'Jane Smith' -- no database will do that

    tip ANDs take precedence over ORs, so when mixing ANDs and ORs, always code the parentheses explicitly to avoid problems

    for example, don't write --

    ... where X=1 and Y=2 and Z=3 or Z=4

    because this will be interpreted as --

    ... where ( X=1 and Y=2 and Z=3 ) or Z=4

    when in fact what you probably wanted is --

    ... where X=1 and Y=2 and ( Z=3 or Z=4 )

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2005
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ummm...yes. Apart from issues of generic name replacement, for this code:
    ...
    $where = "WHERE 1=1";
    ...
    $searchsurname = htmlentities($_POST['searchsurname'], ENT_QUOTES);
    $searchsurnameor = htmlentities($_POST['searchsurnameor'], ENT_QUOTES);
    $searchsurnameor2 = htmlentities($_POST['searchsurnameor2'], ENT_QUOTES);
    $select .= ', studentinfo.surname';
    if ($searchsurname !='') {
    $where .= " AND studentinfo.surname LIKE '$searchsurname'";}
    if ($searchsurnameor !='') {
    $where .= " OR studentinfo.surname LIKE '$searchsurnameor' AND studentinfo.id=sponsorlink.studentid
    AND sponsors.id=sponsorlink.sponsorid AND studentinfo.id=il1.studentid AND ins1.id=il1.inid AND studentinfo.id=pl1.studentid
    AND pts1.id=pl1.ptid";}
    if ($searchsurnameor2 !='') {
    $where .= " OR studentinfo.surname LIKE '$searchsurnameor2' AND studentinfo.id=sponsorlink.studentid
    AND sponsors.id=sponsorlink.sponsorid AND studentinfo.id=il1.studentid AND ins1.id=il1.inid AND studentinfo.id=pl1.studentid
    AND pts1.id=pl1.ptid";
    }

    Would I simply need to add parentheses around the OR statements for every field? OR am I missing something?

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just show your sql here and not your actual php or whatever code you are using.
    get your query working first in your database and THEN get it to work in your application language. Trying to do both at the same time is much more taxing on your brain.

    It isn't a matter of just putting parenthesis around your OR clauses, it is a matter of what you are trying to do overall.

    post just your sql and someone can help you with your results.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •