Pesky checkbox arrays and a MySQL song database

[tameone]

I'm not one who likes to ask for help, so for the last three days my brain has been turning to mush while I try to create a DJ Request Song Wish List. Anyone who can help me out will be saving me from severe insanity. :)

My goal is to
1. Display search results for song title, artist and genre. (accomplished)
2. Allow the user to add songs to their Wish List. This is done by displaying checkboxes with the corresponding value equal to the songID. (working on it)
3. Allow the user to update their wishlist by adding/deleting songs.

So far I have a form that retrieves the song info from a database in a repeating region. In Dreamweaver:

<input name="songID[]" type="checkbox" id="songID[]" value="<b><?php echo $row_RecordsetSearchResults['Title'];?></b> by <?php echo $row_RecordsetSearchResults['Artist'];?>">

This puts the corresponding songID number into a checkbox value, which in turn is submitted by form POST action to the following page as an array.

To process this array I have:

if(isset($_POST['songID']))
{
$songID = $_POST['songID'];

print '<p>Song IDs:</p>';
// process items
$n = count($songID);
for($i = 0; $i <= $n; $i++)
{
//See if the values are being passed from POST
echo $songID[$i];
echo '<br>';
}
$songsInsert = '*'.implode('*',$_POST['songID']).'<br>';
$insertSQL = sprintf("INSERT INTO wishlist (Username, Title) VALUES (%s, %s)",
GetSQLValueString($MM_Username, "text"),
GetSQLValueString($songsInsert, "text"));

mysql_select_db($database_ToneDeaf, $ToneDeaf);

$Result1 = mysql_query($insertSQL, $ToneDeaf) or die(mysql_error());
}

The problem is, I'm using implode. It does store the songs, but only as one long block of text. So if the user picks "New York by Frank Sinatra" and "Hot In Here by Nelly" it is stored as "*New York by Frank Sinatra*Hot In Here by Nelly*"
Obviously this gives me no options to make this actually readable, let alone allow the user to remove one of those songs from their list (both would be have to be removed.)

Also, referencing only the songID number as stored in the music database doesn't seem a good option, since the song database will be changing frequently and song data may be overwritten. Not sure if there's a viable workaround there.

Originally I wanted to store Title, Artist, Genre under seperate columns so I could link them to something like "See more songs from this Artist." However the more I read about arrays and multidimensional arrays, I realize I'm not ready to go there yet. (I just started learning PHP two weeks ago.)

I've really been ambitious about this project, but I'm losing steam fast. I've tried so many variations of code that my head is swimming. Any suggestions?

[ironik]

don't lose steam! you'll get there.

implode() has a opposite function explode() so you can turn your string back into an array, all you have to do it supply the delimiter ('*'):

$array = explode('*', $myString);

Your idea to store artist, title and genre in seperate columns is along the right track though, and is much better than storing the information just as a straight string. SQL is quite powerful and you'll be able to query multiple columns and tables as you learn more.

Getting the information from the database isn't too difficult either:

$result = mysql_query('SELECT myColumn1, myColumn2 FROM myTable', $ToneDeaf) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{
echo $row['myColumn1'];
echo $row['myColumn2'];
}

If you need help writing some SQL for multiple columns there's plenty of people here who can help you.

[tameone]

Thanks for the encouragement, Ironik.
I'm experimenting with explode and other array stuff with some interesting results.

I've found that when the form states:

<input name="songID[]" type="checkbox" id="songID[]" value="<?php echo $row_RecordsetSearchResults['Title'];?>">

And I changed the processing to:

if(isset($_POST['songID']))
{
$songID = $_POST['songID'];

print '<p>Song IDs:</p>';
// process items
$n = count($songID);
for($i = 0; $i <= $n; $i++)
{
//See if the values are being passed from POST

$songsInsert = $songID[$i];
echo 'songinsert is ';
echo $songsInsert;
echo '<br>';
$insertSQL = sprintf("INSERT INTO wishlist (Username, Title) VALUES (%s, %s)",
GetSQLValueString($MM_Username, "text"),
GetSQLValueString($songsInsert, "text"));

mysql_select_db($database_ToneDeaf, $ToneDeaf);
}

I get a result that looks like this:

Song IDs:

songinsert is Accidentally In Love
songinsert is Addicted To Love
songinsert is All for Love
songinsert is Amazing Love
songinsert is

Which means the code really is splitting up my checkbox array. The value stored in the database is "Array" and I'm pretty sure I've read an answer to that question somewhere.

After playing with implode and explode I don't think they're going to meet my needs, unless I'm missing something.

Querying the database isn't too bad for me, it's really the php functions and such I haven't learned yet. And to any others out there who are also struggling to learn, Ironik is right, Don't give up! A month ago I was afraid to even log in to PHPMyAdmin. I know that with a bit of help I can and will learn this stuff.

Looking forward to more suggestions. :)

[dmmh]

if(isset($_POST['songID']))
{
$songID = htmlentities(mysql_real_escape_string(trim($_POST['songID']))); //escape user input!

print '<p>Song IDs:</p>';
// process items
$n = count($songID);
for($i = 0; $i <= $n; $i++)
{
//See if the values are being passed from POST

$songsInsert = $songID[$i];
echo 'songinsert is ';
echo $songsInsert;
echo '<br>';
$query = 'INSERT INTO wishlist'. " VALUES ('', '$MM_Username', '$songsInsert')";
$result = mysql_query($query) or die ("Error in query" . mysql_error());
}

one more advise: dont use usernames to store this...what happens if someone wants to change his name or more ppl have the same name? Gone is the uniqueness
Using userid's is the preferred option

ive adapted this to a query I like better myself, should work though, assuming you do have 3 columns in the 'wishlist' DB of which the first is a AUTO_INCREMENT one, so it generates unique id's

[tameone]

Thank you for the tips, dmmh! You're absolutely right.

I expect this databse won't grow very large. Users are not allowed to change their Username, hence much of the info is tied to that. When first registering the Username is checked to make sure it's Unique. Though I do have an autoincrement ID column, it's mostly for my own use to track testing data. :)

On a somewhat unrelated topic, it may be cruel and unusual to disallow the changing of Usernames, but it's acceptable in this case because only DJ clients will (or should be) using the Client system for which this is being designed.

I didn't get the code to do exactly what I wanted (that is, to store Title, Artist, and Genre data inside of an array which in turn would be stored in an array) but I've settled for an acceptable alternative.
My solution:

if(isset($_POST['songID']))
{
$songID = $_POST['songID'];

// process items
$n = count($songID);
for($i = 0; $i < $n; $i++)
{

$this = each($songID);
$songsInsert = ($this["value"]);

$insertSQL = sprintf("INSERT INTO wishlist (Username, Title) VALUES (%s, %s)",
GetSQLValueString($MM_Username, "text"),
GetSQLValueString($songsInsert, "text"));

mysql_select_db($database_ToneDeaf, $ToneDeaf);

$Result1 = mysql_query($insertSQL, $ToneDeaf) or die(mysql_error());
}
$updateGoTo = "mywishlist.php";
if (isset($_SERVER['QUERY_STRING'])) {
$updateGoTo .= (strpos($updateGoTo, '?'))? "&" : "?";
$updateGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $updateGoTo));
}

Still struggling with other pieces of the code, but that's how we all learn!
Hope this post helps someone else figure out how to loop through an array and extract the values.