Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts

    Query two fields for match with php code

    Hi, I am trying to do a keyword search, and display only results found based on a year
    selected I thought I can just use "AND" but it does not work for some reason.

    PHP Code:
    $keyword ="test";
    $year "2012";
    $results mysql_query("SELECT * FROM calendar WHERE || subject LIKE '$keyword' || desc LIKE '$keyword' AND year LIKE '$year" ORDER BY year DESC LIMIT $page$limit"); 
    Thanks
    Sonny
    Last edited by sonny; 01-25-2012 at 05:39 PM.

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,502
    Thanks
    8
    Thanked 1,089 Times in 1,080 Posts
    You have a double quote on year ... replace with single quote:

    '$year"

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    This is plagued with both PHP and MySQL syntax errors.
    First and foremost, I don't believe that MySQL will accept pipebar or's, although I cannot verify that at this time. I can say that it will not like having a where to an immediate OR regardless of if || is accepted or not.
    You have an incorrect closing quotation on the $year terminating the SQL string in PHP. This should be ', not ".
    Using a LIKE is irrelevant in this case as you have not provided a wildcard syntax to work on. This means that the year MUST be 2012 and either subject or description MUST be "test". Wildcards would require the use of % within the search criteria. Try to avoid using %keyword when you can as wildcarding the first part of a search cannot make use of index.
    AND has a higher priority than OR. Writing:
    Code:
    SELECT * FROM calendar WHERE subject LIKE '%test%' OR desc LIKE '%test%' AND year LIKE '%2012%'
    Says that (desc has to be like test AND year like 2012) OR subject like test. That should likely use WHERE (subject LIKE '%test%' OR desc LIKE '%test%') AND year LIKE '%2012%' to force a specific year regardless of if its in the subject or description.

  • #4
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    This is plagued with both PHP and MySQL syntax errors.
    First and foremost, I don't believe that MySQL will accept pipebar or's, although I cannot verify that at this time. I can say that it will not like having a where to an immediate OR regardless of if || is accepted or not.
    You have an incorrect closing quotation on the $year terminating the SQL string in PHP. This should be ', not ".
    Using a LIKE is irrelevant in this case as you have not provided a wildcard syntax to work on. This means that the year MUST be 2012 and either subject or description MUST be "test". Wildcards would require the use of % within the search criteria. Try to avoid using %keyword when you can as wildcarding the first part of a search cannot make use of index.
    AND has a higher priority than OR. Writing:
    Code:
    SELECT * FROM calendar WHERE subject LIKE '%test%' OR desc LIKE '%test%' AND year LIKE '%2012%'
    Says that (desc has to be like test AND year like 2012) OR subject like test. That should likely use WHERE (subject LIKE '%test%' OR desc LIKE '%test%') AND year LIKE '%2012%' to force a specific year regardless of if its in the subject or description.
    It works fine the way I have it, and searches perfect, but when I add the AND to match
    the year column that does nothing for displaying just the year selected result, I also added
    single quotes as mentioned, but that does not help either.

    Sonny

  • #5
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,339
    Thanks
    60
    Thanked 527 Times in 514 Posts
    Blog Entries
    4
    Quote Originally Posted by sonny View Post
    It works fine the way I have it ... when I add the AND to match ... does nothing for displaying just the year selected result
    Did I just see you say it works fine the way you have it? That being the case why have you started this thread if it works fine? - because it doesn't work perhaps?
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You need to read my description of the priority in the clauses. AND has higher priority than OR, so the only way to make use of the year is in a match with the description AND year, and not that of the subject. Also note without the use of wildcarding its possible that you do not match appropriately - using just 'test' as the criteria requires an identical match to test, and not something that contains test.
    You need to group the logical comparisons together so that they work as appropriate. Unless of course the intent is to find any subject with the value of 'test' OR any record with the description of 'test' and its year 2012. There will be no force on the subject and year in this scenario.

  • #7
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by tangoforce View Post
    Did I just see you say it works fine the way you have it? That being the case why have you started this thread if it works fine? - because it doesn't work perhaps?
    Hi, I mean it works fine searching with just a keyword, I have used this for years

    my post for help was related to implementing a year match option to further filter
    results. I'm just trying to search the way I have it, and display only those matching a
    particular year as well.

    Sonny

    Did you think my search by keyword code did not work? because that's not why I asked
    for help, that part works fine the way it is. maybe you miss understood my post

  • #8
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    You need to read my description of the priority in the clauses. AND has higher priority than OR, so the only way to make use of the year is in a match with the description AND year, and not that of the subject. Also note without the use of wildcarding its possible that you do not match appropriately - using just 'test' as the criteria requires an identical match to test, and not something that contains test.
    You need to group the logical comparisons together so that they work as appropriate. Unless of course the intent is to find any subject with the value of 'test' OR any record with the description of 'test' and its year 2012. There will be no force on the subject and year in this scenario.
    alright I will take a break with this and try again later tonight

    I'm just not sure I explained things all that clear, my goal is to search on a keyword
    and just display any matches for a selected year only?, what does the desc column have
    to do with the year column? I'm not matching any keyword, in that year column?

    Boy and I thought it would just take a simple "AND" in the query or something.
    -might have to leave as is, and live with mult year results.

    Sonny

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I can't see this working even before the addition of the $year. This looks like a SQL syntax error to me: SELECT * FROM calendar WHERE ||. No condition followed by an OR I wouldn't expect would work. 1=1 would work, but not nothing.

    Edit:
    You posted between.
    There is no relationship between desc and year. This is something you have enforced with your clause. AND has a higher priority than OR, so desc and year are logically grouped together while subject is left to fend for itself. These have to be grouped logically so that the year is applied to results of either the subject or the desc.

  • #10
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,302
    Thanks
    4
    Thanked 204 Times in 201 Posts
    I can't see how it is working as posted but you may want to try:

    PHP Code:
    $results mysql_query("SELECT * FROM calendar WHERE (subject LIKE '$keyword' || desc LIKE '$keyword') AND year LIKE '$year' ORDER BY year DESC LIMIT $page, $limit"); 
    Dave .... HostMonster for all of your hosting needs

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by djm0219 View Post
    I can't see how it is working as posted but you may want to try:

    PHP Code:
    $results mysql_query("SELECT * FROM calendar WHERE (subject LIKE '$keyword' || desc LIKE '$keyword') AND year LIKE '$year' ORDER BY year DESC LIMIT $page, $limit"); 
    Right, which is pretty much exactly what I posted earlier.
    Can you verify that MySQL likes using || as well as OR (and verify that it gives priority of || over that of AND)?

  • #12
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    I can't see this working even before the addition of the $year. This looks like a SQL syntax error to me: SELECT * FROM calendar WHERE ||. No condition followed by an OR I wouldn't expect would work. 1=1 would work, but not nothing.

    Edit:
    You posted between.
    There is no relationship between desc and year. This is something you have enforced with your clause. AND has a higher priority than OR, so desc and year are logically grouped together while subject is left to fend for itself. These have to be grouped logically so that the year is applied to results of either the subject or the desc.
    Foo it does, I search entry's with keyword back to 2005 with no problems and pretty
    accurate as well, not only that I have about 10 more columns I left out to simplify my
    post. I am just trying to filter results down to a year instead of getting all matches in the
    database. I have always used "||" I also use that in another function to match email or
    number field for password retrieval.

    Sonny

  • #13
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Got it, had it the first time, think I was test searching on something that did not exist
    and also needed a default value.

    it was a simple AND and LIKE after all, also added a simple "if" to have things work
    like before by default, thing that had me confused was if you use AND you must supply
    something so I just used %, if no year was passed or just wanted everything returned
    PHP Code:
        if (!($year)){
        
    $year '%';} // cleaned of course 
    Sonny

  • #14
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You still need to "fix" the current logic you are using here. I have verified that || is accepted in SQL, but it appears that || still has a lower priority than AND, so unlike PHP, SQL precedence AND = && and OR = ||. I have also verified that WHERE || condition generates a SQL syntax error. For example:
    Code:
    select * from class where || clsid="1b40a6da-452e-11e1-979a-406d62a30b4e" || clsid="f8555321-4530-11e1-979a-406d62a30b4e" AND clspath="Core\\Object" \G
    // ^ fatal error
    
    select * from class where clsid="1b40a6da-452e-11e1-979a-406d62a30b4e" || clsid="f8555321-4530-11e1-979a-406d62a30b4e" AND clspath="Core\\Object" \G
    // One result (incorrect) as clspath doesn't match either of these two clsids, so only the first clsid is returned.  Also how I verified that || is lower than AND
    
    select * from class where (clsid="1b40a6da-452e-11e1-979a-406d62a30b4e" || clsid="f8555321-4530-11e1-979a-406d62a30b4e") AND clspath="Core\\Object" \G
    // AND
    select * from class where (clsid="1b40a6da-452e-11e1-979a-406d62a30b4e" OR clsid="f8555321-4530-11e1-979a-406d62a30b4e") AND clspath="Core\\Object" \G
    // No results, which is correct since AND clspath will not match either of the two above clsid's
    In regards to PHP, a simple query builder will work just fine. Simply append AND year=' . $providedYear to the SQL where clause if the $providedYear is valid.

  • #15
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Ok I will try, thanks, should I just use "OR" or something else instead?

    what does this mean? Simply append AND year to where
    can you give example from any of my query's above

    Side note, I do want year to have a more priority then any other
    field, when I don't, that's when the % comes in by default.

    Sonny
    Last edited by sonny; 01-25-2012 at 06:07 PM.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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