Mysql output to a table

[JackNETUK]

Right ive got a database that i need to output to a table in html like this. I need to get the data from a few databases as its a relational database model.

Heres my track database

as you can see the following rows are the same as the table datetime, subject_id, notes, youdo

Now to get the skills they are stored in a different table

As you can see with the track2skills db there is a track_id and skill_id the track_id will be the same as the track_id in the database track so thats where the skills are stored.

 

What i would like to know is how would i get all this information into the table using php

I can use this query to get the results i want from the db but i cant really use it because it will give me lots of uneeded results

 

Thanks for your help

[Psycho]

Well, you left out some information. Where is the query that you used for that last table. Also, you apparently have another table with the subject name.

 

If I am understanding your problem it is that you want the multiple skills for a track record to be reported in a single field. If that is the case, the query you are using is the right one. You just need to create your PHP code to process it to your liking. Assuming you have run the query and save the results to the variable $results, the following will output the results according to your specs.

 

<?php

echo "<table>\n";
echo "  <tr>\n";
echo "    <th>Date & time</th>\n":
echo "    <th>Subject</th>\n":
echo "    <th>What you did</th>\n":
echo "    <th>Notes</th>\n":
echo "    <th>Skills</th>\n":
echo "  </tr>\n";

$current_track_id = '';

while ($record = mysql_fetch_assoc()) {

  //Determine if this is a new track record
  if ($current_track_id != $record['track_id']) {

    //Close preceeding track ID row
    if ($current_track_id != '') {
      echo "    </td>\n";
      echo "  </tr>\n";
    }

    $current_track_id = $record['track_id'];

    //Create new track record row
    echo "  <tr>\n";
    echo "    <td>".$record['datetime']."</td>\n":
    echo "    <td>".$record['subjectname']."</td>\n":
    echo "    <td>".$record['youdo']."</td>\n":
    echo "    <td>".$record['notes']."</td>\n":
    //Show first skill for the current record
    echo "    <td>".$record['skillname'];

  } else { //Additional record for current track id

    //Show additional skill for current track record
    echo ", " . $record['skillname'];
  }

}

//Close the last record & the table
echo "    </td>\n";
echo "  </tr>\n";
echo "</table>\n";

?>

 

EDIT: fixed a couple minor errors