Oscar C. Oscar C. - 1 year ago 41
MySQL Question

multiple tables with multiple conditions

I have 3 tables (multiple tables) in MySql

1) wp_pressure -- Id, presion, idObject, Insertado
2) wp_altitude -- Id, altitud, idObject, Insertado
3) wp_temperatura -- Id, temperatura, idObject, Insertado

I need to get all these 3 parameters of each table for those rows which have a specific "idObject""not need Id" of each and only specific those ones with a specific "idObject" and not duplicate registers in output. This is becasue in code, later I have to delivery an unqique JSon file formed by the response from the 3 tables.
At the moment I am geting three different and separated replies because I am using 3 URIS to three different php files, one for temperature, one for pressure, another one for altitude, but from loopj ansytask Api in Andorid I only can do one per one request, so I need to join the respose from three tables in one Json, so I am making an unique PHP file to get all data from three tables in one respose and then use only one URI addressed to only one php file.

1) Json from table temperatura:

{"result_temperatura":{"temperature":{"min":[{"Id":"3","temperatura":"0","Insertado":"2016-08-16 18:13:55"}],"max":[{"Id":"4","temperatura":"50","Insertado":"2016-08-16 18:17:33"}],"avg":[{"tempmedia":23}]},"last_entry":{"Id":"4","temperatura":"50","Insertado":"2016-08-16 18:17:33"}}}

2) Json from table pressure

{"result_altitud":{"altitude":{"min":[{"Id":"2","altitud":"10","Insertado":"2016-08-16 18:40:59"}],"max":[{"Id":"4","altitud":"51","Insertado":"2016-08-16 19:12:15"}],"avg":[{"altmedia":20}]},"last_entry":{"Id":"4","altitud":"51","Insertado":"2016-08-16 19:12:15"}}


{"result_altitud":{"altitude":{"min":[{"Id":"2","altitud":"10","Insertado":"2016-08-16 18:40:59"}],"max":[{"Id":"4","altitud":"51","Insertado":"2016-08-16 19:12:15"}],"avg":[{"altmedia":20}]},"last_entry":{"Id":"4","altitud":"51","Insertado":"2016-08-16 19:12:15"}}}

he first step in my php is to make the query to the tables:

In my php I made this to get from APP Android the IdObject:

if(isset($_POST['idObjeto']) && !empty($_POST['idObjeto'])){
$idObjeto = $_POST['idObjeto'];

Then I make this in order to build the query "sql", but it does not work. it give me back repeat parameters as I show in JPG attached (mixedtable). If I take this reply and I use it, I will get not usable data, because with this reply I must to calculate averages, last entries, maximum and minimum for each parameter, "temperature, pressure and altitude.


$sql = SELECT wp_temperatura.temperatura, wp_temperatura.Insertado, wp_pressure.presion,
wp_pressure.Insertado, wp_altitude.altitud, wp_altitude.Insertado FROM
wp_temperatura, wp_pressure, wp_altitude WHERE wp_temperatura.idObjeto =
wp_pressure.idObjeto & wp_temperatura.idObjeto = wp_altitude.idObjeto &

Separate Tables photo for reference here
enter image description here

Answer Source

Based on your updated question, take a closer look at the fields you are selecting and it might explain the duplication in your picture:

SELECT wp_temperatura.temperatura, wp_temperatura.Insertado, wp_pressure.presion,

wp_pressure.Insertado, wp_altitude.altitud, wp_altitude.Insertado

Note the repetition of the various tables' Insertado field. If you truly want all of those timestamps in your response, you can rename the fields so PHP doesn't collapse the duplicates when fetching results:

SELECT wp_temperatura.temperatura,
    wp_temperatura.Insertado AS temperatura_insertado,
    wp_pressure.Insertado AS pressure_insertado,
    wp_altitude.Insertado AS altitude_insertado