#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0

    Universial Search


    I Am trying to make and SQL query for my sites database that does an universal search within all of the different fields. Help.

    Right now it just searches column one but I have 69 columns that need to return a search product.

    I have been building a database for the past few months teaching myself from scratch and the SQLs are the only thing that do not work right.

    Code:
    $sql ="SELECT * FROM `Projects` WHERE '1' LIKE '%{$allfields}%'"
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    Fairly certain your database is not correct if there's 69 columns in a table. Do you have columns which are numbered, like category_1 and category_2?

    Either way, you need:
    WHERE colOne LIKE '%{$term}%'
    AND colTwo LIKE '%{$term}%'
    AND colThree LIKE '%{$term}%'

    etc.

    You should also look into the MATCH...AGAINST syntax, but it only works on MyISAM tables and you need a special index against all these columns. It would be better for you in the long run though.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by ManiacDan
    Fairly certain your database is not correct if there's 69 columns in a table. Do you have columns which are numbered, like category_1 and category_2?

    Either way, you need:
    WHERE colOne LIKE '%{$term}%'
    AND colTwo LIKE '%{$term}%'
    AND colThree LIKE '%{$term}%'

    etc.

    You should also look into the MATCH...AGAINST syntax, but it only works on MyISAM tables and you need a special index against all these columns. It would be better for you in the long run though.


    Well I actually have them labelled 1 - 69 and it's not inaccurate. I did have 5 or so built into an SQL using UNION but any more than that it doesn't run.

    It was like so, and worked very well.

    Code:
    $query = "SELECT * FROM `Projects` WHERE `1` LIKE '%{$allfields}%' UNION SELECT * FROM `Projects` WHERE `2` LIKE '%{$allfields}%' UNION SELECT * FROM `Projects` WHERE `3` LIKE '%{$allfields}%' UNION SELECT * FROM `Projects` WHERE `4` LIKE '%{$allfields}%' UNION SELECT * FROM `Projects` WHERE `5` LIKE '%{$allfields}%'";
  6. #4
  7. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    ...no, this is wrong. How the hell are you going to know what field 16 is next month? What can possibly be the use of an anonymous database with dozens and dozens of anonymous columns?
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by ManiacDan
    ...no, this is wrong. How the hell are you going to know what field 16 is next month? What can possibly be the use of an anonymous database with dozens and dozens of anonymous columns?
    A Lead Resource it's going to store data for construction projects in design all it needs to do is browse and find the data.

    The columns will never change.
  10. #6
  11. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,112
    Rep Power
    487
    You can do a for loop to dynamically create the search fields; say you have 69 columns and they're called

    "Col_1"
    "Col_2"
    ...
    "Col_68"
    "Col_69"

    Then use the code/loop:-

    PHP Code:
    for ($x=1$x 69$x++) {
     
    $sql_array[] = "`Col_{$x}` LIKE '%{$terms}%'";

    Next, implode $sql_array with " AND " being the glue ...

    PHP Code:
    $sql_string implode(" AND "$sql_array); 
    You should get something like (presume $terms = "badger"):

    "`Col_1` LIKE '%badger%' AND `Col_2` LIKE '%badger%' .... `Col_69` LIKE '%badger%'";

    You can then write the rest of the SQL like so:-

    PHP Code:
    $sql "SELECT * FROM `projects` WHERE {$sql_string};"
    Ta dar ...
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  12. #7
  13. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    Pretty sure you would want to use OR in this case rather than AND.

    There is no syntax that would allow you to tell the database to search all columns because your design violates standard database design principles.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by E-Oreo
    Pretty sure you would want to use OR in this case rather than AND.

    There is no syntax that would allow you to tell the database to search all columns because your design violates standard database design principles.
    Well that's what happens when a company hires a high school student to create a database, who has no prior experience with PHP or SQL.

    Comments on this post

    • E-Oreo agrees
  16. #9
  17. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    If you're working with an unchangeable database designed by a 15 year old, lead with that. You'll get a lot better help.

    Badger's solution is what I'd do when confronted with such an atrocity.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    18 but close enough....

IMN logo majestic logo threadwatch logo seochat tools logo