Search for an exact word in the database (case insensitive)
I have this query that I am using to get the position of a specified record.
$sq = "SELECT COUNT(*)
FROM items LEFT JOIN info ON items.refID = info.refID
WHERE info.description = :status AND items.itmName < :itm
ORDER BY items.itmName";
$stmt = $connect->prepare($sq);
$stmt->execute(array(':status'=>"inStock", ':itm'=>$itm));
$rslt = $stmt->fetch(PDO::FETCH_NUM);
$pos = $rslt[0]+1;
Problem (with example):
$itm = "Mango"
I have an item called Mango
in my database and then when I do a search it returns the correct position Mango
as 321 // works fine
$itm = "Mangox"
if I type Mangox
and search (Mangox doesn't exist) it still returns position as 321.
How do I make the search word match an exact item in the database (but not case sensitive)?
source to share
What result do you want to return when Mangox
not found in the list? Do you want the query to not return a string?
There is no need for an expression ORDER BY
as the query returns a single row. "Zest" is LEFT JOIN
negated by the predicate in the sentence WHERE
. (If info.description = something
, it means that info.description IS NOT NULL
, which means it will exclude any lines from items
that did not have a matching line from info
. So this is effectively an inner join.)
To avoid returning a row if the specified :itm
does not exist in the table items
, you can perform a join operation on a query that returns zero rows.
SELECT p.pos
FROM ( SELECT COUNT(*) AS pos
FROM items
JOIN info
ON items.refID = info.refID
WHERE info.description = :status
AND items.itmName < :itm
) p
CROSS
JOIN (SELECT 1
FROM items r
WHERE r.itmName = :itm2 -- additional bind placeholder
LIMIT 1
) q
You will need to pass the value $itm
a second time for the added link placeholder.
$stmt->execute(array(':status'=>"inStock", ':itm'=>$itm, ':itm2'=>$itm));
// ^^^^^^^^^^^^^^^
If there is no string returned, the fetch will return FALSE, so you will need to do additional validation (you actually got the string) before referencing $rslt[0]
.
$rslt = $stmt->fetch(PDO::FETCH_NUM);
if (!$rslt) {
// row returned
$pos = $rslt[0]+1;
} else {
// fetch failed, no row returned
// ??? do whatever you want to do when the exact word is not matched
$pos = 0; // ???
}
EDIT
The reason for the separate anchor placeholder name :itm2
, even when you pass the same value, is that using the same reference placeholder name more than once in a statement (in PDO) does not work as we do for other base interfaces data.
source to share
The problem is that to get "position" you count all names that are less than the one you are looking for.
So, no matter what you are looking for, it will return a number greater than 0 if there are names that are less than it.
For example, $itm = "ZZZ"
you will probably get the last position in the table.
Another problem is this:
$pos = $rslt[0]+1;
Thus, even if the request returned 0
, you still consider it as position # 1.
One possibility is taken from this other question (I didn't run it, so it might have a syntax problem, but the general idea might work):
SELECT rank
FROM (
SELECT @rn:=@rn+1 AS rank, itmName
FROM (
SELECT items.itmName
FROM items LEFT JOIN info ON items.refID = info.refID
WHERE info.description = :status AND items.itmName <= :itm
ORDER BY items.itmName
) t1, (SELECT @rn:=0) t2
)
WHERE itmName = :itm
In any case, you will need to deal with the fact that now the request will not return a number if the name does not exist.
source to share