I have a problem where im not sure what to change to the MySQL query to make it work, i do get the expected results in Workbench. Is it something that php manages different than workbench regarding the queries?
My main problem is that in my knowledge if a query works on Workbench, it should work on PHP, but apparently im wrong about that.
i leave ths sqlfiddle if you want to try the query. Fiddle
chrome console output(debug): ChromePhp prints to console.
mysqli Object {affected_rows: null, client_info: null, client_version: null, connect_errno: null, connect_error: null…}
log.js:137 SELECT
Ordenes.Id, Numero, FechaRegistro, m.P1, a.P2, b.P3, c.P4
FROM
Ordenes
LEFT JOIN
(SELECT
Ordenes.id,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, Ordenes.FechaRegistro, MIN(Actividad.TiempoInicio))) as P1
FROM
Ordenes
LEFT JOIN Actividad ON Ordenes.id = Actividad.Orden_id
GROUP BY Actividad.Orden_id) AS m ON Ordenes.id = m.id
LEFT JOIN
(SELECT
Orden_Id,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(TiempoInicio), MAX(TiempoInicio))) as P2
FROM
Actividad
WHERE
Proceso_Id IN ('1' , '2')
GROUP BY Orden_id) AS a ON Ordenes.id = a.Orden_id
LEFT JOIN
(SELECT
Orden_Id,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(TiempoInicio), MAX(TiempoInicio))) as P3
FROM
Actividad
WHERE
Proceso_Id IN ('2' , '3')
GROUP BY Orden_id) AS b ON Ordenes.id = b.Orden_id
LEFT JOIN
(SELECT
Orden_Id,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(TiempoInicio), MAX(TiempoInicio))) as P4
FROM
Actividad
WHERE
Proceso_Id IN ('3' , '4')
GROUP BY Orden_id) AS c ON Ordenes.id = c.Orden_id
ORDER BY id;
log.js:137 mysqli_result Object {current_field: null, field_count: null, lengths: null, num_rows: null, type: null}
Code:
<?php
include "ChromePhp.php";
error_reporting(E_ALL ^ E_DEPRECATED);
include "includes/db_config.php";
$conn = mysqli_connect($host,$user,$password,$db);
if ($conn->connect_error) {
trigger_error('Database connection failed: ' . $conn->connect_error, E_USER_ERROR);
}
ChromePhp::log($conn);
$sql =
"SELECT
Ordenes.Id, Numero, FechaRegistro, m.P1, a.P2, b.P3, c.P4
FROM
Ordenes
LEFT JOIN
(SELECT
Ordenes.id,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, Ordenes.FechaRegistro, MIN(Actividad.TiempoInicio))) as P1
FROM
Ordenes
LEFT JOIN Actividad ON Ordenes.id = Actividad.Orden_id
GROUP BY Actividad.Orden_id) AS m ON Ordenes.id = m.id
LEFT JOIN
(SELECT
Orden_Id,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(TiempoInicio), MAX(TiempoInicio))) as P2
FROM
Actividad
WHERE
Proceso_Id IN ('1' , '2')
GROUP BY Orden_id) AS a ON Ordenes.id = a.Orden_id
LEFT JOIN
(SELECT
Orden_Id,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(TiempoInicio), MAX(TiempoInicio))) as P3
FROM
Actividad
WHERE
Proceso_Id IN ('2' , '3')
GROUP BY Orden_id) AS b ON Ordenes.id = b.Orden_id
LEFT JOIN
(SELECT
Orden_Id,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(TiempoInicio), MAX(TiempoInicio))) as P4
FROM
Actividad
WHERE
Proceso_Id IN ('3' , '4')
GROUP BY Orden_id) AS c ON Ordenes.id = c.Orden_id
ORDER BY id;";
ChromePhp::log($sql);
$res=$conn->query($sql);
ChromePhp::log($res);
if($res === false) {
trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
} else {
$arr = $res->fetch_all(MYSQLI_ASSOC);
}
ChromePhp::log($arr);
echo json_encode($arr);
?>