I'm not a mysql expert so excuse the simplicity of my question. I have a table that is like that :
ID - NODE - ORIGIN - VALUE - DATETIME
1 - 11 - P1 - 2031 - 2017-01-01 20:15:17
2 - 11 - P2 - 2531 - 2017-01-01 20:15:17
3 - 11 - P3 - 2731 - 2017-01-01 20:15:17
4 - 11 - P4 - 2031 - 2017-01-01 20:15:17
5 - 11 - P1 - 2831 - 2017-01-01 20:30:17
6 - 11 - P2 - 2917 - 2017-01-01 20:30:17
7 - 11 - P3 - 2631 - 2017-01-01 20:30:17
8 - 11 - P4 - 2031 - 2017-01-01 20:30:17
And I'm request look like this:
SELECT *
FROM data
WHERE NODE = '$nodeid'
AND ORIGIN = 'P1'
AND DATETIME BETWEEN '$start' and '$end'
ORDER BY DATETIME
LIMIT 1000
And I receive a table with all result between $start and $end that have the origin P1
The problem is that I'm trying to draw a HTML table that will look like this
Date /time - P1 - P2 - P3 - P4
2017-01-01 20:15:17 - 2031 - 2531 - 2731 2031
2017-01-01 20:30:17 - 2831 - 2917 - 2631 2031
I dont know if I'm clear enough, but I dont even know if what I want is possible. Any help will be appreciated
You already mentioned the codeword JOIN in your question. Queries like the one below are not at all hard to write once you get the hang of it.
In your case, you want to join the table to itself. This is called a self join, but it doesn't work any different than any other join. You just need to make sure you have different aliases for your table:
SELECT d1.`DATETIME`, d1.value P1, d2.value P2, d3.value P3, d4.value P4
FROM data d1
LEFT JOIN data d2 ON d2.`DATETIME` = d1.`DATETIME` AND d2.ORIGIN = 'P2'
LEFT JOIN data d3 ON d3.`DATETIME` = d1.`DATETIME` AND d3.ORIGIN = 'P3'
LEFT JOIN data d4 ON d4.`DATETIME` = d1.`DATETIME` AND d4.ORIGIN = 'P4'
WHERE
d1.ORIGIN = 'P1' AND
NODE = '$nodeid' AND
DATETIME BETWEEN '$start' and '$end'
ORDER BY DATETIME
LIMIT 1000
I escaped the field name DATETIME to make sure it works because that's a name of both a function and a datatype. It's not a keyword, so it might work just fine (I haven't tested it), but with ticks it's sure it works.
It's best not to use SQL specific words in your schema so you don't have to worry about things like that.
One final note: I hope you at least escape your variables before you create that query.
If not, your code is vulnerable to SQL injections.
Read this answer to read how you should pass values from PHP to SQL.