Conditional content from 2 tables

[nfhopmike]

I have an SQL database with 2 tables: audio and notes. I have a PHP page which allows you to select the audio from a list which takes you to a detail page with a flash player which plays the audio. Some of the audio files have associated notes with them, some do not. The notes are contained in the notes table. I have created a foreign key (audio_id) in the notes table which contains the id from the audio table (if there are associated notes, otherwise the id = 0).

 

What I would like to do is firstly to determine if there are notes associated with the audio. If there are, then on the audio detail page I want to display the title of the notes and a link to the notes to be downloaded.

 

I need some assistance on how to achieve this....

 

 

[trq]

There's a great tutorial on sql JOINS on our main site (here), that ought to get you started.

[gevans]

Have a look at this query,

 

SELECT `n`.`title`
FROM `audio` AS a
JOIN `notes` AS n
ON `a`.`id` = `n`.`audio_id`
WHERE `a`.`id`=$id

 

This will select the title from notes

Where the id and audio id match (in both tables)

 

$id should be replaced with the required id.

 

This will either return a mysql resource with the title or no rows.

 

If you have an id field in notes, you can also get that to reference in the link

[nfhopmike]

Thanks that helps - just one thing I'd like clarification on - when you say $id should be replaced with the required id I'm not sure what you mean. The id for the audio detail page was selected on the previous (selection) page as a url parameter. For instance say I selected audio with an audio_id of 5, this will bring up record 5 on the detail page. How do I then get the corresponding notes page (with an audio_id of 5) to be automatically selected so that the title can be displayed?

[gevans]

Use the same variable from your original query, in the second one.