PHP Question

Discussion in 'OT Technology' started by CruD, May 13, 2003.

  1. CruD

    CruD Bah, I'll just hack it

    Oct 25, 2001
    Likes Received:
    Lakeway, TX
    Last edited: May 13, 2003
  2. Astro

    Astro Code Monkey

    Mar 18, 2000
    Likes Received:
    Cleveland Ohio
    I'll recap for those just tuning in:

    SQL query issue when trying to compare data from two tables. DBMS is MySQL. Query in question:

    $sql "SELECT reg.last,reg.regID,reg.first,coreg.last,coreg.first,coreg.regID,reg.state,coreg.state
       FROM Registrant as reg, CoRegistrant as coreg
       WHERE ( (reg.regID = coreg.regID && reg.last LIKE \"%
    $lastname%\" && reg.state=\"$state\" )
       || (reg.regID = coreg.regID && coreg.last LIKE \"%
    $lastname%\" && reg.state=\"$state\" ) )";
    The problem is your query. PHP looks ok from where I sit (although I didn't look it all over).

    The root of your problem is you're trying to use a logical comparison with '&&' and '||'. This works in PHP, C/C++, Perl, etc but has a different meaning in SQL (not just MySQL, but the language SQL).

    '&&' and '||' are very handy for checking the bits and doing bit masking (which is really handy if you're doing stuff with user rights).

    What I think you really want is the use of "AND" and "OR" (actually spelled out.

    Lets relook at your query:

    SELECT reg.last,reg.regID,reg.first,coreg.last,coreg.first,coreg.regID,reg.state,coreg.state FROM Registrant as reg, CoRegistrant as coreg WHERE ( (reg.regID = coreg.regID && reg.last LIKE 'Astro' && reg.state='OH' ) || (reg.regID = coreg.regID && coreg.last LIKE 'Astro' && reg.state='OH'

    MySQL doesn't allow for subselects, but you don't need any here. I would recommend rewriting the query using INNER JOINS. You don't have to in this case, but you'll appreciate them later in life :)

    If it was me, I'd do something like this:

    SELECT reg.last,reg.regID,reg.first,coreg.last,coreg.first,coreg.regID,reg.state,coreg.state

    FROM Registrant INNER JOIN CoRegistrant AS CoRegistration.regID = Registrant.regID

    WHERE Registrant.last LIKE 'Astro' AND Registrant.state = 'OH' OR CoRegistrant.last LIKE 'Astro' AND CoRegistrant.state = 'OH'

    (I split the lines up in an attempt to make it a little easier to read)

    I think this is what you're trying to do. You can do this without using INNER JOINs. It would force you to do a 1 to 1 join which in this case works, but you'll find JOINs allow for some other tricks.

    Looking at only your query, it would appear you have some database design issues (need to do some normalization). But if you're just learning, thats cool - one step (and one problem) at a time.

Share This Page