How do I order by three columns (with similar info) via PHP?

Question

I have a table ("presentations") with these columns:

PresentationTitle | Speaker1 | Speaker2 | Speaker3

This works great for listing all of the presentation titles like this on my "Presentations" page:

Presentation Number One
  by:
  John Smith
  Pocahontas Smith
  John Rolfe

Presentation Number Two
  by:
  Grandmother Willow

Presentation Number Three
  by:
  Chief Powhatan
  Kocoum Derpyderpderp

But now, for my "Speakers" page, I need to list all of the speakers--independent of each other--in alphabetical order. Like this:

Chief Powhatan
Grandmother Willow
John Rolfe
John Smith
Kocoum Derpyderpderp
Pocahontas Smith

What's the best way to do this?

Another note--In my real-life table, the speaker1 column has lots more columns attached to it-- "middlename", "lastname", "headshot", "age"... and speaker2 has "middlename2", "lastname2", "headshot2," etc... I need to keep these pieces of info attached to those speakers.

Thanks in advance for helping a big noob like me :)

Answer 1

You should rethink your database design. A better table layout would be something more like:

CREATE TABLE presentations (
    presentationID INT,
    presentationTitle VARCHAR(200),
    PRIMARY KEY (presentationID)
);

CREATE TABLE speakers (
    speakerID INT,
    firstName VARCHAR(32),
    middleName VARCHAR(32),
    lastName VARCHAR(32),
    headshot BLOB,
    age TINYINT UNSIGNED,
    PRIMARY KEY (speakerID)
);

CREATE TABLE presentationSpeakers (
    presentationID INT,
    speakerID INT
);

Then you can do a query like this to list all speakers from a particular presentation in alphabetical order by first name:

SELECT
    s.firstName,
    s.lastName
FROM
    presentationSpeakers ps
    INNER JOIN presentations p ON ps.presentationID = p.presentationID
    INNER JOIN speakers s ON ps.speakerID = s.speakerID
WHERE
    p.presentationTitle = 'Presentation Number One'
ORDER BY
    s.firstName, s.lastName

Or a query as simple as this for all speakers in alphabetical order by first name:

SELECT firstName, lastName FROM speakers ORDER BY firstName, s.lastName

This is better for many reasons. Having a bunch of extra columns for every speaker will be difficult to deal with (which is the trouble you're having now), plus you'll have a ton of columns which may not be used (depending on the number of speakers), which may waste space, and another big reason...what if you get a presentation in which there are more speakers than you have columns?

My way is much more flexible and much easier to perform simple operations (like sorting). You can have any number of speakers for each presentation, and there is no wasted space taken up with unused columns.

Answer 2

You need to restructure your tables. You're almost there but you need some normalization. Instead of speaker1, speaker2 ... etc, put your speakers in a separate table:

speaker:
speaker_id | firstname | lastname | headshot ... (etc)

Then your presentation table should basically just have columns associated with the presentation:

presentation:
presentation_id | title | location ...(etc)

Then to join it all together you'd need a join table that references the IDs

presentation_speaker:
presentation_id | speaker_id 

So with a structure like that you can get all speakers in order by doing:

SELECT * FROM speaker ORDER BY lastname, firstname

Edit If you want speakers for a particular group of presentations the query is

SELECT * FROM presentation_speaker ps 
  JOIN speaker s ON s.speaker_id=ps.speaker_id 
  WHERE ps.presentation_id IN ($pres1, $pres2, ... etc) 
  ORDER BY s.lastname, s.firstname 

Your original output is a bit harder but not too bad. If you don't have tons then doing an extra query in a loop isn't so bad:

$query1 = "SELECT * FROM presentation";
$res = mysql_query($query1);

if( mysql_error($res) ) { 
   die( mysql_error($res) );
}

$presentations = array();

while( $row = mysql_fetch_row($res) ) {
     $row['speakers'] = array();
     $query2 = "SELECT * FROM presentation_speaker ps 
       JOIN speaker s ON ps.speaker_id = s.speaker_id
       WHERE sp.presentation_id=" . mysql_real_escape_string($row['presentation_id']);

     $res2 = mysql_query($query2);
     if( mysql_error($res2) ) {
         die(mysql_error($res2));
     }

     while( $speaker = mysql_fetch_row($res2) ) {
         $row['speakers'][] = $speaker;
     }

     $presentations[] = $row;
}

With that you'd end up with a data structure that looks something like:

Array(
    'title' => 'Presentation title',
    'location' => 'Some place',
    'speakers' => 
       Array(
          'firstname' => 'Quazi',
          'lastname'  => 'Moto',
          'headshot'  => 'quazi.jpg'
       ),
       Array(
          'firstname' => 'Slarty',
          'lastname'  => 'Bartfast',
          'headshot'  => 'dontpanic.jpg'
       )    
)

Answer 3

Your question is:

What's the best way to do this?

Well, the main problem is in your table design.

If you have this table: PresentationTitle, Speaker1, Speaker2, Speaker3

Where not all presentations have 3 speakers then, you have a bad design :)

You should have a Presentation table like this: PresentationId, PresentationTitle

And another table called Speakers like this: SpeakerId, SpeakerName

And then, as you can have a speaker that goes to more than one presentation and a presentation with more than one speaker you have a many-to-many relationship and, as a rule of thumb, that always generates a new table.

In this case, it would be this table Presentation_Speaker: PresentationId, SpeakerId (Both fields are the PK)

So, now that you're doing things in "the best way" :) How can you retrieve all speakers? Just running this query:

select * from speakers

Now, if you don't want to do the previous steps to do things the right way, I'm just answering your question to get all speakers from those 3 speakers columns:

(select speaker1 Speaker from presentations)
union
(select speaker2 from presentations)
union
(select speaker3 from presentations)
order by Speaker

Duplicate speakers will be removed.

PS: I didn't get your "real-life example":

the speaker1 column has lots more columns attached to it

What did you mean?

Answer 4

What might be a fairly easy approach for you is to list whatever data is applicable to "Speakers" in an HTML table and then make that table sortable.

I've used the JavaScript from here to make my HTML tables sortable and it works very nice. There's other implementations of a sortable HTML table out there as well.

That way you don't have to mess around with making a MySQL view and/or procedure and you don't have to do complex processing in your PHP script...