Raw query with IN and multiple where condition?

Question

I need to write a query with a IN condition and multiple WHERE condition. I have tried following SQL statement but this one is not getting me result. Maybe I did something wrong.

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

$columns = "DISTINCT model, manufacturer, logo, year, update_status";

$modelList = array('Version', 'Facelift', 'OptionsÃ¤nderung');


$orderBy = "manufacturer, model";

// Prepare the sql statement.
$sql = "select $columns from tbl_manufacture where model IN (:modelList)  year > ? AND update_status = ?";
$sql.="order by $orderBy";

$em = $this->getEntityManager();
$query = $em->getConnection()
            ->prepare($sql);
$query->bindValue("modelList", $modelList, Connection::PARAM_STR_ARRAY);

$params = array(
    "year" => $year,
    "update_status" => $status
);

// Execute the statement
$query->execute($params);

score 2 · Answer 1 · answered Dec 23 '19 at 06:56

2

You are missing "AND" after IN condition:

$sql = "select $columns from tbl_manufacture where model IN (:modelList) AND year > ? AND update_status = ?";
$sql.="order by $orderBy";

answered Dec 23 '19 at 06:56

Suresh Kamrushi

15,627
13
75
90

I am getting error SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters – nas Dec 23 '19 at 06:59
Refer to this for the above error https://stackoverflow.com/questions/15888198/pdo-invalid-parameter-number-mixed-named-and-positional-parameters – Prabhjot Singh Kainth Dec 23 '19 at 07:06
@PrabhjotSinghKainth SQLSTATE[HY093]: Invalid parameter number: parameter was not defined – nas Dec 23 '19 at 07:14

score 1 · Answer 2 · answered Dec 23 '19 at 06:57

1

You need to convert your array in comma separated string to use it IN query like this:

$modelList = array('Version', 'Facelift', 'Model');
$modelList = "'" . implode ( "', '", $modelList ) . "'";

Result: 'Version', 'Facelift', 'Model'

Also you are missing AND after IN (:modelList)

answered Dec 23 '19 at 06:57

Prabhjot Singh Kainth

1,831
2
18
26

PARAM_INT_ARRAY converts array to string but in my case I have "OptionsÃ¤nderung" in my array as well. so When I apply PARAM_INT_ARRAY, it is giving error. – nas Dec 23 '19 at 07:04
It should be PARAM_STR_ARRAY instead of PARAM_INT_ARRAY – Prabhjot Singh Kainth Dec 23 '19 at 07:09
Yes just a typo mistake while commenting you. Its PARAM_STR_ARRAY as in my question. – nas Dec 23 '19 at 07:13

Raw query with IN and multiple where condition?

2 Answers2