CODEWITHSUNDEEP
phpmysqlsqlpdo
hellomello
hellomello

Reputation: 8587

Using 'AND' and 'OR' in same mysql statement with PDO

Is it possible to do this?

Lets say I have a table : data

$id_1 = "checking";
$id_2 = "box";
$id_users = 1;

id    id_1        id_2         id_users
1     checking    box             1
2     checking    circle          1
3     box         checking        1
4     box         checking        1

$sql = $db->prepare("SELECT COUNT(*) FROM data WHERE ((id_1 = ? AND id_2= ?) OR (id_1 = ? AND id_2 = ?)) AND id_users = ?");
$sql -> execute(array($id_1, $id_2, $id_2, $id_1, $id_users));
echo count($sql);

With this, I'm getting an output of 1 only. Technically I should be getting an output of 3, correct? Because there are 3 possibilities with checking and box.

The SQL is supposed to check either table for the two combinations of checking and box.

Can someone tell me what I'm doing wrong here?

Thanks

Upvotes: 0

Views: 849

Answers (3)

Matt Ball
Matt Ball

Reputation: 359836

It looks like you're count()ing the already-counted SQL COUNTed number of rows.
How about just echo $sql?

$sql = $db->prepare("SELECT COUNT(*) FROM data WHERE ((id_1 = ? AND id_2= ?) OR (id_1 = ? AND id_2)) AND id_users = ?");
$sql->execute(array($id_1, $id_2, $id_2, $id_1, $id_users));
echo $sql->fetch();

Upvotes: 3

Milan Halada
Milan Halada

Reputation: 1934

What MДΓΓ БДLL said is ok, but you could also use named parameters:

$sql = $db->prepare("SELECT COUNT(*) FROM data WHERE ((id_1 = :id1 AND id_2= :id2) OR (id_1 = :id2 AND id_2 = :id1)) AND id_users = :idusers");
$sql -> execute(array(':id1' => $id_1, ':id2' => $id_2, ':idusers' => $id_users));

And you need to fetch result

$result = $sql->fetch();
echo $result[0];

Upvotes: 2

Poonam
Poonam

Reputation: 4631

In your query it seems that you are passing id_1 as parameter to id_2 and id_2 parameter as id_1 so as per your query there is only one combination of id_1 = checking and id_2 = box , so you are getting output count as 1 instead of this to avoid parameter confusion use

$sql -> execute(array(':id1' => $id_1, ':id2' => $id_2, ':idusers' => $id_users));

Upvotes: 1

Related Questions