Search exact word on database (none case sensitive)

Question

I've got this query which I use 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;

The problem (with an example):

$itm = "Mango"

I have an item called Mango in my database and then when I do a search it returns the correct position of Mango as 321 //works fine

$itm = "Mangox"

if I type Mangox and do a search (Mangox does not exist), it still returns the position as 321.

How do I make the search word match the exact item on the databse (but none case sensitive)?

If I am correct ... change items.itmName < :itm to items.itmName = :itm — user1844933, Jun 30 '15 at 03:29
@user1844933 thanks. If I do that, it will not sort the records. — Becky, Jun 30 '15 at 03:30
is `:a` in your `array(':status'=>"inStock", ':a'=>$itm)` supposed to be `:itm` in your `...WHERE info.description = :status AND items.itmName < :itm...`? — Sean, Jun 30 '15 at 03:34
@Sean thanks. that was a typo. I've fixed it in my post. But that does not solve the issue. — Becky, Jun 30 '15 at 03:36
Not sure if it will work, but try wrapping in a [`IF()`](https://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if) -> `IF( SELECT items.itmName FROM items WHERE items.itmName = :itm1, SELECT COUNT(*) ...[rest of your query]... ORDER BY items.itmName , -1)`, and `$stmt->execute(array(':itm1'=>$itm, ':status'=>"inStock", ':itm'=>$itm));` — Sean, Jun 30 '15 at 03:49
Just brainstorming, but you could try adding a subselect into your `WHERE` clause -> `SELECT COUNT(*) ...[rest of your query]... WHERE info.description = :status AND items.itmName < :itm AND (SELECT items.itmName FROM items WHERE items.itmName = :itm1) IS NOT NULL ORDER BY items.itmName` and `$stmt->execute(array(':status'=>"inStock", ':itm'=>$itm, ':itm1'=>$itm));` — Sean, Jun 30 '15 at 04:21

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

The problem is that to get the "position" you are counting all names that are less than the one you are looking for.

So no matter what you search, it'll return a number greater than 0 whenever there are names that are less than it.

For example, $itm = "ZZZ" will probably get you the last position in the table.

Another problem is that later on you do:

$pos = $rslt[0]+1;

So even if the query returned 0, you'll still consider it to be position number 1.

One possibility taken from this other question (I did not run it, so it might have some syntax issue 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'll need to deal with the fact that now the query will not return any number when the name is not there.

edited May 23 '17 at 11:43

Community

1
1

answered Jun 30 '15 at 03:35

eugenioy

11,825
28
35

thanks. But `$pos = $rslt[0];` does not solve the issue. – Becky Jun 30 '15 at 03:43
No, it will not. I was not saying it would. I am editing my answer to include a possible solution, though. – eugenioy Jun 30 '15 at 03:45
It's a variable used to keep track of the row numbers. More on variables here: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html – eugenioy Jun 30 '15 at 03:50
I just tried your solution and nope, that did not work. thanks. – Becky Jun 30 '15 at 04:00

spencer7593 · Accepted Answer · 2015-07-07T22:05:35.907

What result do you want returned, when Mangox is not found in the list? Did you want the query to not return a row?

There's no need for an ORDER BY clause, since the query returns one row. The "outerness" of the LEFT JOIN is negated by the predicate in the WHERE clause. (If info.description = something, that means that info.description IS NOT NULL, which means it will exclude any rows from items that didn't have a matching row from info. So, it's effectively an inner join.)

To return no row when the specified :itm does not exist in the items table, you can perform a join operation to 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'd need to pass in the value of $itm a second time, for the added bind placeholder.

  $stmt->execute(array(':status'=>"inStock", ':itm'=>$itm, ':itm2'=>$itm));
  //                                                     ^^^^^^^^^^^^^^^

When there isn't a row returned, the fetch will return FALSE, so you'd need to to an additional check (that you actually got a row) 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 distinct bind placeholder name, :itm2, even when you're passing in the same value is that using the same bind placeholder name in a statement more than once (in PDO) doesn't work as we would expect, as it does with other database interfaces.

Search exact word on database (none case sensitive)

2 Answers2