...

View Full Version : Resolved Query two fields for match with php code



sonny
01-24-2012, 04:29 PM
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.



$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

mlseim
01-24-2012, 04:40 PM
You have a double quote on year ... replace with single quote:

'$year"

Fou-Lu
01-24-2012, 04:46 PM
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:


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.

sonny
01-24-2012, 05:15 PM
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:


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

tangoforce
01-24-2012, 05:33 PM
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? :D

Fou-Lu
01-24-2012, 05:38 PM
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.

sonny
01-24-2012, 05:45 PM
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? :D

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

sonny
01-24-2012, 05:58 PM
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

Fou-Lu
01-24-2012, 06:00 PM
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.


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.

djm0219
01-24-2012, 06:05 PM
I can't see how it is working as posted but you may want to try:



$results = mysql_query("SELECT * FROM calendar WHERE (subject LIKE '$keyword' || desc LIKE '$keyword') AND year LIKE '$year' ORDER BY year DESC LIMIT $page, $limit");

Fou-Lu
01-24-2012, 06:08 PM
I can't see how it is working as posted but you may want to try:



$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)?

sonny
01-24-2012, 06:09 PM
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.


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

sonny
01-25-2012, 05:38 PM
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


if (!($year)){
$year = '%';} // cleaned of course


Sonny

Fou-Lu
01-25-2012, 05:48 PM
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:


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.

sonny
01-25-2012, 05:57 PM
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

Fou-Lu
01-25-2012, 06:03 PM
It appears it doesn't matter. I would recommend OR since I typically use || exclusively for the processing language. Since SQL appears to have no precedence order between || and OR, I'd use the phrase instead of the pipes.
What's important is the positioning of the brackets. You need to bracket the OR clauses together since you want to return where the YEAR is explicit for EITHER of the or clauses. Without the braces the year will only apply to the command on the immediate left of it, which is for the description check and not the subject. So you'd end up with any subject and any description match, but the description will only return if it was done in 2012 (for example).

sonny
01-25-2012, 06:17 PM
It appears it doesn't matter. I would recommend OR since I typically use || exclusively for the processing language. Since SQL appears to have no precedence order between || and OR, I'd use the phrase instead of the pipes.
What's important is the positioning of the brackets. You need to bracket the OR clauses together since you want to return where the YEAR is explicit for EITHER of the or clauses. Without the braces the year will only apply to the command on the immediate left of it, which is for the description check and not the subject. So you'd end up with any subject and any description match, but the description will only return if it was done in 2012 (for example).

I see now, that's very important to know, It appears I did use the brackets last time around, when I saw them from the previous page, but I did not know exactly what they did. hope this tread helps some others it certainly helped me.

Thanks
Sonny



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum