Why does my query loop through so many results is it because i’m joining based on date? The query works fine if i limit it to 1 result.
Example query:
SELECT d.RH,b.AT,c.WSAV,d.date FROM test d, test b, test c WHERE d.RH
IN (SELECT RH FROM test WHERE sn = 5165654) AND b.AT
IN (SELECT AT FROM test WHERE sn = 5165655) AND c.WSAV
IN (SELECT WSAV FROM test WHERE sn = 5165656) AND d.date = b.date AND d.date = c.date ORDER BY d.date;
[quote=“hockletrain96, post:1, topic:284399, full:true”]is it because i’m joining based on date?
[/quote]
something like that
why are you joining the table to itself in the first place?
here’s an example why i’m confused – your SELECT clause includes d.RH, but that’s going to be equal to any of the RH values of all the rows where sn=5165654
why just those three sn values? how many more are there, or will there be? what would your query look like if are more of them?
I will attempt to summarize what I’m doing ahah… . A User can create a calculation which is stored inside of a calculation table, which has the unit they want the data from and the sensors they want to use. The calculation table also stores a chosen formula as a string. In the data table each units data is inserted via an auto import script. The ID or SN(serialNumber) of each unit is repeated each time it is inserted which is how we ID each unit, but of course this makes the serial non-unique. So the only way apart from the SN we can ID data is by using the date. In PHP the calculation table is queried, which gives the units , sensors and formulas to work with. In the above example i’m using the calculation tables data to build the query to get the data, which i then process through a series of calculations.The task is super dynamic lmfao. This is what the real Query looks like:
$sql = “SELECT d.$sensor,b.$sensor1,c.$sensor2,d.date FROM test d, test b, test c WHERE d.$sensor IN (SELECT $sensor FROM test WHERE sn = $rtu) AND b.$sensor1 IN (SELECT $sensor1 FROM test WHERE sn = $rtu1) AND c.$sensor2 IN (SELECT $sensor2 FROM test WHERE sn = $rtu2) AND d.date = b.date AND d.date = c.date ORDER BY d.date LIMIT 1”;
I hope that gives you a rough idea? In the end it does work which I’m ecstatic about
EDIT: I might add that there can be anything from 3-5 units and 3-5 sensors being used for calculations, so there will be more than three sn values in some calculations. These calculations are automated and fixed to a script (which is why the user builds what they want in the database), the example we are looking at only allows the user to include three units and three sensors in their calculation.
So what your telling us is that you apparently have user supplied data and have variables in your query instead of using prepared statements opening the application up to an SQL Injection Attack?
. A User can create a calculation which is stored inside of a calculation table, which has the unit they want the data from and the sensors they want to use. The calculation table also stores a chosen formula as a string. In the data table each units data is inserted via an auto import script. The ID or SN(serialNumber) of each unit is repeated each time it is inserted which is how we ID each unit, but of course this makes the serial non-unique. So the only way apart from the SN we can ID data is by using the date. In PHP the calculation table is queried, which gives the units , sensors and formulas to work with. In the above example i’m using the calculation tables data to build the query to get the data, which i then process through a series of calculations.The task is super dynamic lmfao. This is what the real Query looks like:
