Linking Multiple Columns from two tables via Primary Key

I'm working on designing a system of tables that will let me create a small planner of sorts. The planner itself is an HTML table that draws on two different MySQL tables.

The tables are:

Table_1 (date_id, suggestion_1, suggestion_2, suggestion_3, ...)

Table_2 (song_id, song_title, ...)

The "date_id" column of Table 1 is filled with a calendar of, well, dates, each of which has its own group of suggestions. In each row of Table 1, the cells of the suggestion columns are each individually filled with a single identification number ("1293", "1185", "0984") drawn from the "song_id" column in Table 2.

I'm trying to figure out how to generate an HTML table that lists the date and the suggestions, but instead of just numbers, displays the song titles from the second table. (There's other information about the songs that I want to display, too, which is why I've got two tables instead of just 1).

The HTML table should ultimately look like this:

HTML_Table (calendar date, song title a, song title b, song title c)

I'm relatively new to PHP/MySQL, (and self-taught, at that - well, I have a book) so it's taken me most of the day to figure out how I should've defined my issue - I understand now that I need to reference the Primary Key in the second table. My problem is how do I do that? I'm trying to figure out if I should use a JOIN of some kind, but I'm no good at them whatsoever, new as I am.

The book I'm using gave an example function for a specific instance of this, which I modified for my personal use:

//convert the number of the 1st suggestion into a title
function get_suggest1($suggest_a) {
global $db;
$query = 'SELECT 
    song_title
    FROM
    draftsongref
    WHERE
    song_id = ' . $suggest_a;
    $result = mysql_query($query, $db) or die(mysql_error($db));
$row = mysql_fetch_assoc($result);
extract($row);
return $song_title;
}

Below that, I also included:

//retrieve the suggestions for the mass
$query = 'SELECT
    date_id, suggestion_1
FROM
    draft_suggestions
ORDER BY
    date_id ASC';
$result = mysql_query($query, $db) or die (mysql_error($db));
...
// loop through the results 
while ($row = mysql_fetch_assoc($result)) { 
extract($row); 
$suggest1 = get_suggest1($suggest_a);

...so I could use the $suggest1 variable to output the specific song_title for the given suggestion. My problem is that, whenever I ran that function, I would receive an error message.

You have an error in your SQL sy开发者_开发技巧ntax;
check the manual that corresponds to your
MySQL server version for the right syntax
to use near '' at line 6

Now, I've so far managed to figure out that it's referring to this:

WHERE
    song_id = ' . $suggest_a;

And, more specifically, to the " = ' . $suggest_a;" part, but I simply can't figure out how to fix that particular issue. Maybe its an assumption of mine (does it matter if the variable argument $suggest_a have a certain name?), or maybe its an error in the function's design - when I used the original example, it worked, though the original argument/variable had had no other correspondant.

Now, I understand that's a fair bit different than the JOIN table I mentioned, but that's the closest I've been able to come to figuring out how to aciheve this. Any help granted would be appreciated.

---

Your table design is wrong. Instead of a single row for a date with multiple columns for suggestions, the table should be a date and a single suggestion, with multiple rows for a given date. This lets you do a simple join of the two tables to get a set of suggestions for each date along with the song title.

---

Well, this is embarrassing - I've solved my own problem. It wasn't a function definition issue, per se, so much as it was an output issue.

// loop through the results 
while ($row = mysql_fetch_assoc($result)) { 
extract($row); 
$suggest1 = get_suggest1($suggest_a);

I was mistakenly using the same argument variable from defining the function ($suggest_a) rather than doing anything with any queried information.

//retrieve the suggestions
$query = 'SELECT
    date_id, suggestion_1
FROM
    draft_suggestions
ORDER BY
    date_id ASC';
$result = mysql_query($query, $db) or die (mysql_error($db));

I should've instead dropped the proper column's variable into the use of the function:

// loop through the results 
while ($row = mysql_fetch_assoc($result)) { 
extract($row); 
$suggest1 = get_suggest1($suggestion_1);

It didn't work because it had nothing to output. Once I gave it something to use, it worked fine.

In any case, thank you to Jim Garrison for the tips on table structure - I'll be sure to take advantage of that the next time this comes up.