Add a comma delimited string to a database

[sean04]

As of now I have a textbox that I will tell users to place a comma in between each word they write. I would like all of these words to be broken up by the commas and inserted into a database. The database is something like this and the text field like this

 

User 1

Enter Activities: Hockey,Soccer,Running

User 2

Enter Activities: Baseball

 

Activities Database:

 

User_ID      Activity

1                Hockey

1                Soccer

1                Running

2                Baseball

 

 

Any code samples or links will be great! Thanks!

[tomtimms]

do you have any code that you have written so far?  I recommend checking out http://us.php.net/explode to get your values.  We would need to see more code in order to see where you need assistance.

[sean04]

Thanks for the reply. I haven't written anything just yet. I'm looking at a few things right now. I am looking into what you suggested. I was just looking for some options

[Pikachu2000]

You really don't want to store them like that; that breaks database normalization. Create another table and insert each activity as its own record using the user_id as a foreign key for each record. On the form, you can either let them have multiple text inputs, or better still, define an array of check boxes for them, and let them select up to whatever maximum number of checkboxes you want.

[sean04]

Thank you Pikachu2000. That sounds like a reasonable way to do it. I will take a look into that. That being said, I'm still interested on how I would come about completing this in a way that my first post stated.

 

If I have something like this:

 

<?PHP

$InterestsList = $_POST[interests];
$Interests= explode(",", $InterestsList);

?>

 

After something like that, how would I loop all the values into a table that looks like the one in my first post?

 

Thanks for the help!

 

[litebearer]

Psuedo code...

 

$InterestsList = $_POST[interests];
$Interests= explode(",", $InterestsList);
count the number of elements in the $interests array
connect to database
$i=0;
while ($1 < count)
$variable = array value[$l]
  insert into table field $variable
}
?>

 

make sense?

[sean04]

Yes I see what your doing. I will have to give that a try and see how it works out.

 

Thanks for the reply,

Sean

[sean04]

So something like this? I haven't tried it yet

 

<?PHP
$InterestsList = $_POST[interests];
$Interests= explode(",", $InterestsList);
$InterestsCount = count($Interests);
connect to database
$l=0;
while ($1 < InterestsCount)
$variable = array value[$l]
  $result = mysql_query(insert into interests (User_ID,Interests) values($loggedin[user_ID],$variable));
}
?>

 

[litebearer]

<0ld fart has glassess off)  - the $1 should be an 'i'

[sean04]

OK and what is the purpose of the 'i' if its not being used?

[roopurt18]

<?php
    
$_POST['activities'] = 'foo,bar,    ,world'; // TODO Remove this line

$user_id = 1; // Assume you have this elsewhere in your code or can set it here
$activities = array_key_exists( 'activities', $_POST ) ? explode( ',', $_POST['activities'] ) : false;
if( $activities ) {
    foreach( $activities as $k => $v ) {
        // We want to clean up each activity
        $v = trim( $v );
        if( ! strlen( $v ) ) {
            // Remove empty activities
            unset( $activities[ $k ] );
        }else{
            //$acitivities[ $k ] = mysql_real_escape_string( $v ); // TODO Uncomment this line
        }
    }
    
    // $activities array is now clean and void of empty values, but if the array
    // itself is empty we do nothing
    if( count( $activities ) > 0 ) {
        $sql = "insert into `my_table` ( `user_id`, `activity` ) values" . PHP_EOL;
        $sql .= "( {$user_id}, '" . implode( "' )" . PHP_EOL . "( {$user_id}, '", $activities ) . "' )" . PHP_EOL;
        echo $sql; // TODO Execute query instead of echo
    }else{
        echo 'Nothing to insert.'; // TODO Print better message?
    }
}else{
    echo 'Activities not in POST.';  // TODO Print better message?
}
?>

[litebearer]

what I wrote was NOT the exact coding (hence PSUEDO code)

 

The purpose of $i is simply to keep looping thru the array until all the elements have been processed. Perhaps some extra commenting may help

 

<?PHP

$InterestsList = $_POST[interests]; // get the variable from the form

$Interests= explode(",", $InterestsList); // creates an array where each word that was separated by comma becomes an element in the array

$InterestsCount = count($Interests); // Counts the total elements in the array

connect to database // Do you actual database connection here

$i=0; // set your counter

while ($i < $InterestsCount) { // begin your loop

  $what_interest = $Interests[$i]; // get the value of the current element

  $result = mysql_query("insert into interests (User_ID,Interests) values('$loggedin[user_ID]', '$what_interest')"; // insert appropriate values into the database

  $i = $ i + 1; // increment your counter
}
?>

 

any clearer?

 

 

[sean04]

Thank you both for your comments!

 

And litebearer, I was aware it was Psuedo code and i figured your 1's were suppose to be i's.

 

Thanks!

[roopurt18]

I see that I made a typo and spelled activities wrong here:

//$acitivities[ $k ] = mysql_real_escape_string( $v ); // TODO Uncomment this line

[sean04]

Hey thanks for the help! I managed to complete that.

 

I have another question now though. How would I add checkbox values to a table. Like I have 4 checkboxes and the user can select as many as they want. how would I get all those that they checked and add them to my table? The table I guess would look like the one that I showed on my first post

 

Thanks