0

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

Imanuel
  • 3,596
  • 4
  • 24
  • 46
Jean-Gabriel Maurice
  • 19
  • 2

1 Answers1

0

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.

Graham
  • 7,431
  • 18
  • 59
  • 84
Imanuel
  • 3,596
  • 4
  • 24
  • 46
  • Ok, I'm not sure I understand the "escape" you are talking about ( I know nothing about php and mysql, seriously its completely new for me ). I'll have to search for that – Jean-Gabriel Maurice Apr 28 '17 at 15:59
  • but lets say, my table is composed of device_ts ( device time stamp ) instead of DATETIME – Jean-Gabriel Maurice Apr 28 '17 at 15:59
  • Since the request in your question seems to work, you can leave the ticks `\`` around `DATETIME` out. Regarding the first comment: The exact way to escape depends on your setup. I hope you don't use the `mysql_` functions like `mysql_connect` and `mysql_query`. These are too old and don't even exist in modern versions of PHP. Do read the question and the answer in the link I posted. – Imanuel Apr 28 '17 at 16:01
  • @Pharaoh, it may be beneficial for the OP if you add aliases to the returned columns (eg `d1.value P1, d2.value P2`) in case he is using an assoc array in PHP. – RToyo Apr 28 '17 at 19:30
  • Good idea. I wouldn't have taken offence if you had edited the answer ;) – Imanuel Apr 28 '17 at 19:36