SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP sql query help.

    I cant seem to get this to work correctly.

    I am writing a little search query for my script but i want it to check multiple columns not just one.

    This is the code for the query

    Code PHP:
    $sql = "SELECT a.*, u.user_id, u.username, u.user_colour from ".CLASSIFIEDS_TABLE." a, ". USERS_TABLE ." u 
    				WHERE a.ad_description OR a.ad_title OR u.username LIKE '%$search%' and u.user_id = a.ad_poster_id ORDER BY a.ad_date DESC";

    Rite now it is only looking in the u.username column not the other

    Any ideas on a fix for this?

  2. #2
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think i figured out i have to do it like :

    Code:
    $sql = "SELECT a.*, u.user_id, u.username, u.user_colour from ".CLASSIFIEDS_TABLE." a, ". USERS_TABLE ." u 
    WHERE a.ad_description LIKE '%$search%' and u.user_id = a.ad_poster_id 
    OR a.ad_title LIKE '%$search%' and u.user_id = a.ad_poster_id 
    OR u.username LIKE '%$search%' and u.user_id = a.ad_poster_id 
    ORDER BY a.ad_date DESC";
    but maybe there is a better way?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by iansteeze View Post
    but maybe there is a better way?
    there sure is
    Code:
    SELECT a.*
         , u.user_id
         , u.username
         , u.user_colour 
      FROM ".CLASSIFIEDS_TABLE." a
    INNER
      JOIN ". USERS_TABLE ." u 
        ON u.user_id = a.ad_poster_id 
     WHERE a.ad_description LIKE '%$search%' 
        OR a.ad_title LIKE '%$search%' 
        OR u.username LIKE '%$search%' 
    ORDER 
        BY a.ad_date DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks that works great also


    Another question i have is can i make it so the query is not case sensitive?

    For example when i search "Welcome" i get a result but when i search "welcome" i get nothing.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    The default character set and collation are latin1 and latin1_swedish_ci, so non-binary string comparisons are case insensitive by default.
    -- http://dev.mysql.com/doc/refman/5.0/...nsitivity.html
    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by iansteeze View Post
    Thanks that works great also


    Another question i have is can i make it so the query is not case sensitive?

    For example when i search "Welcome" i get a result but when i search "welcome" i get nothing.
    Normally it should already be non case sensitive
    You probably have a case-sensitive column types like BINARY, VARBINARY, or BLOB
    so if you change these to CHAR, VARCHAR, or TEXT, the case-insensitive searches will also work

  7. #7
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    helps?

    Thanks that helped a but but i think i have no choice but to use UTF8_bin so i might be out of luck on that

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sharedlog.com View Post
    Normally it should already be non case sensitive
    You probably have a case-sensitive column types like BINARY, VARBINARY, or BLOB
    so if you change these to CHAR, VARCHAR, or TEXT, the case-insensitive searches will also work
    The column type is LONGTEXT would that be case sensitive over just TEXT ?

  9. #9
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I solved it by using strtolower() and LOWER()

  10. #10
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Two points: you should never select * from a table, as you run the risk of the table changing in the future, and your code not being prepared to handle it.

    Also: You should never concatenate variables directly into your SQL. You should Parameterize them (http://us.php.net/mysqli) in order to avoid anyone maliciously or unintentionally corrupting or deleting your database.

  11. #11
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SituationSoap View Post
    Two points: you should never select * from a table, as you run the risk of the table changing in the future, and your code not being prepared to handle it.

    Also: You should never concatenate variables directly into your SQL. You should Parameterize them (http://us.php.net/mysqli) in order to avoid anyone maliciously or unintentionally corrupting or deleting your database.
    I just had * for testing and will be changed.

    Most of the variables are taken care of before used int he SQL but some are escaped with $db->sql_escape()


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
  •