MySQL query with joins works in Phpmyadmin but not in php

Question

I have a Mysql (mariaDB) query with some joins that works fine in phpmyadmin terminal. But if I try to execute in php (pdo) the database get huge workload till I get a bad gateway on nginx.

If I remove the joins from query, it works in my php script also.

So the issue must be with the joins but what is not ok here?

public function exportCSVById($id){
$this->db->query('
SELECT DISTINCT
positions_list.id,
positions_list.order_id,
positions_list.position,
positions_list.comment,
positions_list.result,
positions_list.created,
positions_list.modified,
positions_list.releaseSuS1,
positions_list.releaseSuS2,
positions_list.releaseCuS1,
positions_list.releaseCuS2,
us1.lastname as SuS1,
us2.lastname as SuS2,
orderstates.keynr,
orderstates.content,
companies.id,
companies.company,
users.id,
users.lastname,
ordertype_positions.keynr,
ordertype_positions.p_company,
ordertype_positions.content as orderstate,
repkey.keynr,
repkey.p_company,
repkey.content as repkey,
rk2.keynr,
rk2.p_company,
rk2.content as repkey2

FROM positions_list

JOIN ordertype_positions
ON positions_list.optionindex=ordertype_positions.keynr
AND ordertype_positions.p_company = positions_list.comp_id

JOIN repkey
ON positions_list.keyindex=repkey.keynr
AND repkey.p_company = positions_list.comp_id

JOIN repkey as rk2
ON positions_list.keyindex2=rk2.keynr
AND rk2.p_company = positions_list.comp_id

JOIN companies
ON positions_list.comp_id=companies.id

JOIN users
ON positions_list.uid=users.id

JOIN users as us1
ON positions_list.releaseSuS1=us1.id

JOIN users as us2
ON positions_list.releaseSuS2=us2.id

JOIN orderstates
ON positions_list.repstate=orderstates.keynr

WHERE 
positions_list.order_id =:id

ORDER BY
positions_list.created ASC
');

$this->db->bind(':id', $id);

$results = $this->db->resultSet();
return $results;
}

public function resultSet(){
$this->execute();
    return $this->stmt->fetchAll(PDO::FETCH_OBJ);
}

@Gulshan: Why? The poster knows the database schema, you and I do not. JOIN is not the same as LEFT JOIN. A Normal JOIN means INNER JOIN. LEFT JOIN means LEFT OUTER JOIN. — Erwin Moller, Sep 18 '19 at 12:02

score -1 · Answer 1 · answered Sep 18 '19 at 10:33

-1

PHPMYADMIN puts a limit on the results.

You simply have a HUGE resultset. PHP tries to load it all in memory:

return $this->stmt->fetchAll(PDO::FETCH_OBJ);

Ans apparently that is more than your current memory limit for PHP.

Solution?

Work in batches, or start using cursors:

What is PDO scrollable cursor?

answered Sep 18 '19 at 10:33

Erwin Moller

2,375
14
22

There are only few results of the query – Marco Sep 18 '19 at 11:11
In that case your problem is somewhere else in the code. Maybe an endless loop? I would suggest isolating this query and run in standalone in a test file (no framework) to see if it returns as expected. It is just basic PDO. – Erwin Moller Sep 18 '19 at 11:32
I did it, just one single file with pdo. Same Result. Maybe server fault? – Marco Sep 18 '19 at 11:49
1

I don't know nginx, but if I were on Apache, I would start by looking at the latest error messages in error.log for this website. Also look in (again try to find the nginx equivalent) of Apache2's OWN error.log. Often you can find a hint in there. If that leads to nothing, start adding checkpoints (log to the error file). Just to make sure it hangs on the query itself. – Erwin Moller Sep 18 '19 at 11:57
It is a managed server. In my website log is no related error. I ask my hoster to check the core logs .... thank you – Marco Sep 18 '19 at 12:01

MySQL query with joins works in Phpmyadmin but not in php

1 Answers1