SQL - SELECT WHERE column = TRUE AND :input = `string`

Question

I'm making a simple search engine in PHP (with PDO) and MySQL, its goal is to find products in a stock.
My TABLE phone has a COLUMN snowden which is a TINYINT (containing 0 or 1). I want to be able to get results if phone.snowden is true and the user's input is 'snowden'.

Here's a short version of my query: (:search_0 is the user's input. This is a prepared query for PDO)

SELECT * FROM phone WHERE phone.snowden = 1 AND :search_0 = `snowden`

Of course the real query is actually longer (joining multiple tables and searching into many columns) but everything works except this.
When I try to search 'snowden' I get no result (meaning the keyword(s) have not been found in any column and the 'snowden' case doesn't work).

Do I miss something about the syntax ?
How can I achieve this query in the way I tried ?
How can I achieve this with a comparison with the column name (if this is a better way to proceed) ?

EDIT: Full code

Here's the full code I use:

$keywords = explode(" ", $_POST['query']);
$query = "SELECT phone.id, phone.imei, phone.model, phone.color, phone.capacity, phone.grade, phone.sourcing, phone.entry, phone.canal, phone.sale, phone.state, phone.snowden FROM phone LEFT JOIN capacity ON (phone.capacity = capacity.id) LEFT JOIN color ON (capacity.color = color.id) LEFT JOIN model ON (color.model = model.id) LEFT JOIN grade ON (phone.grade = grade.id) WHERE ";
$query_array = array();
for ($i = 0; $i < count($keywords); $i += 1) {
    $query .= " ( phone.imei LIKE :search_" . $i;
    $query .= " OR phone.sourcing LIKE :search_" . $i;
    $query .= " OR phone.canal LIKE :search_" . $i;
    $query .= " OR phone.entry LIKE :search_" . $i;
    $query .= " OR phone.sale LIKE :search_" . $i;
    $query .= " OR phone.state LIKE :search_" . $i;
    $query .= " OR ( phone.snowden = 1 AND ':search_" . $i . "' = `snowden` )";
    $query .= " OR model.name LIKE :search_" . $i;
    $query .= " OR color.name LIKE :search_" . $i;
    $query .= " OR capacity.amount LIKE :search_" . $i;
    $query .= " OR grade.name LIKE :search_" . $i;
    if ($i != (count($keywords) - 1)) {
        $query .= " ) AND ";
    } else {
        $query .= " ) ";
    }
    if (strtolower($keywords[$i]) == 'snowden') {
        $query_array['search_' . $i] = $keywords[$i];
    } else {
        $query_array['search_' . $i] = "%" . $keywords[$i] . "%";
    }
}
$query .= "ORDER BY phone.id DESC";
$results = $stock->prepare($query);
$results->execute($query_array);

Possible duplicate of [Can PHP PDO Statements accept the table or column name as parameter?](https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) — Masivuye Cokile, Sep 15 '17 at 09:15
@Nidhi257 No it's the value for a prepared PDO query, this is the user input (I'll precise it) — AymDev, Sep 15 '17 at 09:15
@AymDev from where I'm seating that looks like a column name u tried to replaced with a parameter — Masivuye Cokile, Sep 15 '17 at 09:17
so your query should be `SELECT * FROM phone WHERE phone.snowden = 1 AND :search_0` where should be the column name in which you want to search a String. search_0 is input variable which contain String 'snowden' — Nidhi257, Sep 15 '17 at 09:18
@MasivuyeCokile I'm making comparison to a string, I could replace this part of the query by `AND :search_0 = 'hello'`, it would make my issue the same. I hope I am understandable :) — AymDev, Sep 15 '17 at 09:21
The problem with your query what ever that is typed by user becomes you column, eg if user typed "hi " then your query becomes `SELECT * FROM phone WHERE phone.snowden = 1 AND hi = 'snowden'` Please see above from @Nidhi257 — Masivuye Cokile, Sep 15 '17 at 09:23
so you want to check whether user has typed snowden as string ? — Nidhi257, Sep 15 '17 at 09:25
you cannot take a column to search in from parameter, I believe you can get the name of the column to search in from somewhere else, and then use it in your query — Hatik, Sep 15 '17 at 09:30
try this query `SELECT * FROM phone WHERE phone.snowden = 1 AND ':search_0' = 'snowden'` not sure but give a try — Nidhi257, Sep 15 '17 at 09:34
cool after that AND you need to enter the column then let it equal or LIke what typed by user — Masivuye Cokile, Sep 15 '17 at 09:35
i mean in programming you can write as "SELECT * FROM phone WHERE phone.snowden = 1 AND '"+:search_0+"' = 'snowden'". here :search_0 will first get parse and then single quotes will be added. i guess this will work. — Nidhi257, Sep 15 '17 at 09:37
@MasivuyeCokile In this way it should be compared to the column name. Write the column name would result in a comparison to its value. Is there a way to compare to a column name ? — AymDev, Sep 15 '17 at 09:43
@Nidhi257 I meant I did post my code. I'm still searching for solving it (maybe I found, still trying) — AymDev, Sep 15 '17 at 10:12
have you tried instead of using backquotes try using single quotes. because it give error to me in SQL. — Nidhi257, Sep 15 '17 at 10:17
try updating this: `$query .= " OR ( phone.snowden = 1 AND 'snowden'= :search_" . $i )";` — Nidhi257, Sep 15 '17 at 10:20
@Nidhi257 Yeah it works ! Feel free to write the solution as answer and I'll accept it. Thanks a lot for the time you gave ! — AymDev, Sep 15 '17 at 10:24

score 1 · Accepted Answer · answered Sep 15 '17 at 10:37

1

replace your line

$query .= " OR ( phone.snowden = 1 AND ':search_" . $i . "' = `snowden` )";

with

$query .= " OR ( phone.snowden = 1 AND 'snowden'= :search_" . $i )";

answered Sep 15 '17 at 10:37

Nidhi257

754
1
5
23

SQL - SELECT WHERE column = TRUE AND :input = `string`

EDIT: Full code

1 Answers1