Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#128 search function broken (database error)

closed
katzlbt
5
2008-08-06
2008-07-17
No

This error occurs using the current HEAD version from svn.

Try to use the search page (contact/searchlist.php) to find records be querying for addresses or phone numbers.

1) contact/searchlist.php?search=example&type=address

Error message:

Failed to do query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE like '%example%' OR city LIKE '%example%') AND (hidden = 0 OR 1) ORDER BY l' at line 1
In query: SELECT * FROM tab3_contact AS contact, tab3_address AS address WHERE contact.id=address.id AND (line1 LIKE '%example%' OR LIKE like '%example%' OR city LIKE '%example%') AND (hidden = 0 OR 1) ORDER BY lastname

===========================================

2) contact/searchlist.php?search=234&type=phone

Failed to do query: Unknown column 'phone1' in 'where clause'
In query: (SELECT contact.* FROM tab3_contact AS contact, tab3_properties AS properties WHERE contact.id=properties.id AND properties.type = 'phone' AND properties.value LIKE '%234%' AND (properties.visibility = 'visible' OR 1) AND (contact.hidden = 0 OR 1) ORDER BY lastname) UNION (SELECT contact.* FROM tab3_contact AS contact, tab3_address AS address WHERE contact.id=address.id AND (phone1 LIKE '%234%' OR phone2 like '%234%') AND (hidden = 0 OR 1) ORDER BY lastname)

===========================================

Discussion

  • Patch for PageSearchList.class.php bug in line 133

     
    Attachments
  • Logged In: YES
    user_id=591629
    Originator: YES

    Solution to 1) is simple (patch file attached):

    PageSearchList.class.php, line 133:
    old: $where = "contact.id=address.id AND (line1 LIKE $p OR LIKE like $p OR city LIKE $p) AND (hidden = 0 OR $admin) ORDER BY lastname";
    new: $where = "contact.id=address.id AND (line1 LIKE $p OR line2 LIKE $p OR city LIKE $p) AND (hidden = 0 OR $admin) ORDER BY lastname";

    Problem 2) is more tricky, since the columns 'phone1' and 'phone2' apparently have been moved outside the 'contact' table.
    File Added: patch.txt

     
  • katzlbt
    katzlbt
    2008-07-22

    Logged In: YES
    user_id=1527196
    Originator: NO

    pnone numbers were removed from the address table.
    Fixed in 640.

     
  • katzlbt
    katzlbt
    2008-07-22

    • assigned_to: nobody --> katzlbt
    • status: open --> closed
     
  • Logged In: YES
    user_id=591629
    Originator: YES

    Wow, thanks for the instant fix for both problems!

    Regarding the fix for the address search: wouldn't it be better, if 'line2' were also included in the where condition?

     
    • status: closed --> open
     
  • Logged In: NO

    I assumed line2 is usually a PO Box or something like that. One would not need to search for that.

     
  • Logged In: NO

    Well, since line2 is part of the 'address' it would make sense to include it in a search called 'address' IMHO. It also avoids potential user questions why the contents in this field are never found.

     
  • katzlbt
    katzlbt
    2008-08-06

    • status: open --> closed
     
  • katzlbt
    katzlbt
    2008-08-06

    Logged In: YES
    user_id=1527196
    Originator: NO

    is included now