[Resolved] Would an array be the best solution for this?

badgoat

Hi!

I have a script which allows me to create a music group and store it in a db. Now, I want to be able to associate and store song titles which the music groups have performed. But since the # of songs is not the same per music group, would an array be the best way to store them? And if so, how to insert them into the db?

Thank you again for all the help, I am becoming less and less a noob with each post 😉

TheDefender

No...

The best solution is for you to explore the use of normalized tables, as it is easier to search on, and is an overall better way to store your data. Have one table for the bands, and one for the songs that uses a foreign key to refer back to the bands table. Samples below:

Bands Table
Band_ID
Band_Name

Songs Table
Song_ID
Song_Name
Band_ID (Foreign Key from the Bands Table)

Sample data may look something like this:

Bands Table
1 - Nine Inch Nails
2 - Beck

Songs Table
1 - Closer - 1
2 - Ringfinger - 1
3 - Loser - 2

Hope this clears it up a bit for you. Save the array manipulation for after you pull the data out of the tables.

cafrow

what I would do is have 2 tables in the database, one call music group, and the other songtitles. Then have a Unique ID field in the Music group table that is linked to the Songtitles. Where is an example of what I would make it look like

{Table: Music_Groups}
-ID: uniqueID Field
-Name: Group Name

{Table: Song_Titles}
-ID: uniqueID Field
-GroupID: Same Value of the ID field in the Music Group
-Name: Song Title

hope this make sence, so your song titles will be linked to the group by the ID and GroupID field. Your title would look like this

[id]-[GroupID]-[Title]
1 - 1 -whatever Title1
2 - 1 -Whatever Title2
3 - 1 -Whatever Title3
4 - 2 -Greatest hits1
5 - 2 -Greastest hit2

badgoat

Hi!

I do currently have two tables.. The first is music_group_db and is:

group_id | music_group_name

and another table for the songs, song_db and is:

song_id | song_title | rel_group_name_id

I have this script which allows me to select a group from a drop-down list and add a song, and it does so successfully. A problem I am having though, is that it displays the ID number associated with the group and not the group name itself. How do I display the group name instead of the ID?

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional">
<html>
<head>
<title>Create Song Record</title>
</head>
<body>
<?php
    // Connect to database
$connect= mysql_connect("localhost","root")
   or die("Could not connect to database in localhost !");
mysql_select_db("music")
   or die("Could not select that database !");

$music_group_name=$HTTP_POST_VARS['music_group_name'];






echo'
<table>
<TR>
<form name="form3" method="post" action="song_title_added.php">
<td  COLSPAN="2" bgcolor="#FF0099"><b><center />Add Song </b></td>
</TR>

<TR>
    <TD><b>Song Title:</b></TD>
    <TD><input type=text name="song_title" size=40></TD>
</TR>

<TR>

 <TD><B>Music Group Information:</B></TD>
 <TD><select name="rel_group_name_id" tabindex="4"><option value="notset">- Select a Group -</option>    ';

                    //--- CREATE DEPONENT SELECT ---
                    $sql = "SELECT DISTINCT music_group_name, group_id FROM music_group_db ORDER BY music_group_name";
                    $music_group_name = mysql_query($sql) or die($sql . '<br />' . mysql_error());
                    while ($row = mysql_fetch_array($music_group_name)) {
                    echo '<option value="' . $row['group_id'] . '">' . $row['music_group_name'] . '</option>';
                    }    ;

	  echo '</select></TD>
</TR>

<TR>
    <TD COLSPAN=2><center><br><input type="submit" name="Submit" value="Submit" action="required"></center></TD></FORM>
</TR>
</TABLE>
</body>
</html>         ';
?>

TheDefender

Where, in this script you provided, is it showing the group's ID number instead of the name?

badgoat

It's not.. Because I am unsure how to. On the next page which displays the data just submitted, I have it set to display the ID

<tr>
<td>Music Group Name</td>
<td>'.$_POST['rel_group_name_id'].'</td>
</tr>

I am thinking that I need a sql join, to associate the rel_group_name_id' to the group name.. Is that correct? I've never done one.

TheDefender

Use that ID to query the music_group_db table.

SELECT * FROM music_group_db where group_id = $POST_['rel_group_name_id']

Then you can use that to display the group's name.

badgoat

I have been trying iterations of the above, but I either get errors or nothing displays.

If I try

$sqlquery1 = "SELECT * FROM music_group_db where group_id = "$_POST['rel_group_name_id']"";

I get this error:

Parse error: parse error, unexpected T_VARIABLE in c:\phpdev5\www\music\song_title_added.php on line 21

If I try

$sqlquery1 = "SELECT * FROM music_group_db where group_id = $rel_group_name_id";

I get nothing displayed when using any iteration of

<td>Music Group Name</td>
<td>' .$music_group_name. '</td>

I am not sure what I am doing wrong. 🙁

TheDefender

Gotta escape the quotes:

$sqlquery1 = "SELECT * FROM music_group_db where group_id = '" . $_POST['rel_group_name_id'] . "'";

badgoat

Ayep, thanks, that got rid of the error. When I try yo echo the group name it displays nothing. Am I missing an elementary step in displaying the variable from the music_group_db table?

<td>'.$music_group_name.'</td>

TheDefender

Well, the query does just that... query the DB. You then need to do a fetch of the data for display. The sequence is as follows:

$sql = "Qeury here";
$getdata = mysql_query($sql);
$gotdata = mysql_fetch_array($getdata);

echo $gotdata['field_name'];

Take a look in the manual at [man]mysql_query[/man] and [man]mysql_fetch_array[/man].

The example above assumes you are expecting one row returned. If you are expecting more than one result, you need to use a while loop for the fetch operation. The manual has examples to show you how to do this.

badgoat

I do not enjoy the manual. It's like throwing a Chilton's Manual at a gardener and expecting him to be able to fix a Ferrari. Maybe I am too much of a right-brainer, but I learn far more from tuts and from reading other resolved threads, and of course, getting mine answered.

I altered your example above into

$sql = "SELECT * FROM music_group_db where group_id = '" . $_POST['rel_group_name_id'] . "'";
$getdata = mysql_query($sql);
$gotdata = mysql_fetch_array($getdata);

and

<TD>' .$gotdata['group_name']. '</TD>

And I still get no music group displayed, but I think I understand how to find it now, and will beat my head against my keyboard until it works.

Thank you for the help!