How to UNPIVOT an aliased query result using PHP and MySql

Question

I would be grateful for your assistance in helping me get solution for my project weather query problem. I have arrived at the query below and is working:

"); $first_row = true; while ($row = mysqli_fetch_assoc($result)) { if ($first_row) { $first_row = false; // Output header row from keys. echo ''; foreach($row as $key => $field) { echo '' . htmlspecialchars($key) . ''; } echo ''; } echo ''; foreach($row as $key => $field) { echo '' . htmlspecialchars($field) . ''; } echo ''; } echo(""); ?>

The output has a long row of heading columns and I want to UNPIVOT the result of above query:

R.weatherID R.Days R.locationID R.windDirection R1.weatherID R1.Days R1.locationID R1.windDirection R2.weatherID R2.Days R1.locationID R2.windDirection

and get the short output format below:

R.weatherID R.Days R.locationID R.windDirection R1.weatherID R1.Days R1.locationID R1.windDirection R2.weatherID R2.Days R1.locationID R2.windDirection

Thank you very much for your time.

EDIT: -- Changed R2.Days = (R2.Days + 1) - 5 to -- R1.Days = (R2.Days + 1) - 5 -- implies is there a similar pattern five days from this record

-- Table structure for table R

CREATE TABLE IF NOT EXISTS R ( W_id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT, weatherID VARCHAR(6) NOT NULL, Days INT(6) NOT NULL, locationID VARCHAR(8) NOT NULL, windDirection VARCHAR(6) NOT NULL, PRIMARY KEY (W_id) );

-- Index for table R

ALTER TABLE R ADD UNIQUE KEY uDW (Days,W_id);

Answer 1

I think you are looking for UNION instead of JOIN:

SELECT  R.weatherID, R.Days, R.locationID, R.timeRecorded, R.windDirection,
        R1.weatherID, R1.Days, R1.locationID, R1.timeRecorded,
        R1.windDirection, R2.weatherID, R2.Days, R2.locationID,
        R2.timeRecorded, R2.windDirection
    FROM  R
    WHERE  R.windDirection IN ("NW'', "SW", "SE", "N", "W" )
 UNION 
 SELECT  R.weatherID, R.Days, R.locationID, R.timeRecorded, R.windDirection,
        R1.weatherID, R1.Days, R1.locationID, R1.timeRecorded,
        R1.windDirection, R2.weatherID, R2.Days, R2.locationID,
        R2.timeRecorded, R2.windDirection
    FROM  R AS R1
    WHERE  R1.windDirection IN ("NW'', "SW", "SE", "N", "W" ) 

Since I don't understand the intent of ON R2.Days = (R2.Days + 1) - 5, I don't know how to add R2 on.