0

I have a MySQL database and a query. It does not work as expected. If I write two queries, they will work just fine. I would like to modify my code and avoid using several queries.

$sql = $this->yhteys->prepare("SELECT ki_ka_ot.a_p1id
FROM ki_ka_ot, ki_il, ka_il, ka, ka_ki, ka_ki_ot, ki_ka
WHERE
(
ka_ki_ot.match = ? AND
ki_ka_ot.a_points = 5 AND
ki_ka_ot.a_points > ki_ka_ot.b_points AND
ki_ka_ot.endtime > '0000-00-00 00:00:00'
)
OR
(
ka_ki_ot.match = ? AND
ki_ka_ot.a_points < 5 AND
ki_ka_ot.a_points < ki_ka_ot.b_points AND
ki_ka_ot.endtime > '0000-00-00 00:00:00'
)
AND ki_ka_ot.a_tid = ki_il.id
AND ki_ka_ot.a_p1id = ka_il.id
AND ki_ka.id = ki_ka_ot.chart_id
AND ki_ka.charttype_id = ka_ki_ot.charttype_id
AND ka_ki_ot.charttype_id = ka.id 
AND ka_ki_ot.round_id = ka_ki.id
AND ka_ki_ot.id = ki_ka_ot.place_id
LIMIT 1");

$sql->bindValue(1, 20, PDO::PARAM_INT);
$sql->bindValue(2, 19, PDO::PARAM_INT);
$sql->execute();

$res = $sql->fetch(PDO::FETCH_ASSOC);
xms
  • 429
  • 5
  • 24
  • 2
    Is it just me or did anyone else stop reading at the table names? What language is this? On topic: What is the expected output? What's the actual (eg. wrong) output? – ccKep May 02 '17 at 23:28
  • 3
    Possibly related [SO: Mysql or/and precedence?](http://stackoverflow.com/questions/12345569/mysql-or-and-precedence). Long story short: Your query: `A or B and C and D and ...` is basically the same as `A or (B and C and D)` - what you seem to want is `(A or B) and C and D and ...` - so, use parenthesis to group your ORs. – ccKep May 02 '17 at 23:29
  • 1
    Maybe it's just me, but you have terrible naming conventions. – Mikey May 02 '17 at 23:31
  • @ccKep Thanks! Solved. – xms May 02 '17 at 23:37
  • @ccKep you should post that as an answer so this question can be complete. – Bill Karwin May 03 '17 at 00:38
  • It's also odd you're comparing to a time `>` zero. Why not `IS NOT NULL`? – tadman May 03 '17 at 00:42

0 Answers0