CODEWITHSUNDEEP
phpmysqlcodeigniterlogic
Gihan Lasita
Gihan Lasita

Reputation: 3055

how to write sql query correctly with "WHERE" and "IN"

i want to select records contain given ID and given Branch so i came up with this query and i want to know whether this is the correct way to write this

im using codeigniter

$orderids = array('2', '3');
$branch = array('branch1', 'branch2');

$this->db->where_in('order_id', $orderids);
$this->db->where_in('branch', $branch);
$query = $this->db->get('tbl_order_data');

this makes the query

SELECT *
FROM (`tbl_m_order_collection_data`)
WHERE `order_id` IN ('2', '3')
AND `branch` IN ('branch1', 'branch2')

my table looks like

+----+----------+-------------+-----------+
| ID | order_id | branch      | item_code |
+----+----------+-------------+-----------+
|  1 |        1 | branch1     | 4R1       |
|  2 |        1 | branch2     | 4R11      |
|  3 |        1 | branch2     | ACS20x20  |
|  4 |       *2 | branch1     | ACS20x27  |
|  5 |       *2 | branch1     | ACS20x20  |
|  6 |        1 | branch1     | ACS20x20  |
|  7 |        2 | branch2     | ACS20x27  |
|  8 |       *3 | branch2     | ACS20x20  |
+----+----------+-------------+-----------+

what im trying is to get star marked records from table. so the above query is valid for use?

Upvotes: 0

Views: 471

Answers (3)

Chris Schmitz
Chris Schmitz

Reputation: 8247

Yes, this is the correct way to do it. You can make this a lot more succinct by chaining the methods together though:

$query = $this->db
    ->where_in('order_id', $orderids)
    ->where_in('branch', $branch)
    ->get('tbl_order_data');

Upvotes: 0

Artog
Artog

Reputation: 1142

From the comments it seems you want to know how IN works.

The simple answer is that IN looks for the values stated before the word in the array after the word

Example one:

'banana' IN ('apple','orange','banana')

This yields true.

Example two:

1234 IN ('hello','world!')

This yields false.

You can read more about IN here

AND is a logical operator and only yields true if bothe the statements on either side is true. Like this:

true  AND true = true

false AND true = true AND false = false

If we combine Example one and two we get:

'banana' IN ('apple','orange','banana')
AND
1234 IN ('hello','world!')

As Example one was true, but exampe two was false, this will come back as false.

Upvotes: 1

Jordan Running
Jordan Running

Reputation: 106027

Your WHERE clause..

WHERE `order_id` IN ('2', '3') AND `branch` IN ('branch1', 'branch2')

..could be read, in English, as "The records whose order_id is '2' or '3' and whose branch is 'branch1' or 'branch2.'"

In other words, it's roughly equivalent to:

WHERE (`order_id` = '2' OR `order_id` = '3')
  AND (`branch` = 'branch1' OR `branch` = 'branch2')

The advantage of WHERE ... IN is that you could specify lots of different values, e.g. order_id IN ('2', '3', '4', '5'), and you can even put a SELECT (subquery) after IN to see if the value is "in" the set returned by the subquery.

Upvotes: 0

Related Questions