Help w arrays!

[dottomm]

Hello and thanks in advance to anyone who can help me with my array problem. I've always struggled with arrays, and now I Need to understand how I can use them to solve my latest problem. I've created a test scenario to recreate what I'm trying to do in real-life.
In my test scenario, the input page 'in.php' I give the user a form to check their favorite colors. The form uses checkboxes. I would like to take the checkbox values and store them as an array.
So if the user checks off the colors red, blue and green, it would save the values '1','2', '3' as an array in the table 'users'.
And then in my 'out.php' I would like to unroll the array values and look up in the table 'colorkey' for each value in the array.
So for each user it would echo users.id and the list of colors they chose.

//create colorkey table
CREATE TABLE  `arraytest`.`colorkey` (
  `color_id` int(11) NOT NULL AUTO_INCREMENT,
  `color` varchar(20) NOT NULL,
  PRIMARY KEY (`color_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

INSERT INTO `arraytest`.`colorkey` VALUES  (1,'red'),
 (2,'Blue'),
 (3,'Green'),
 (4,'Yellow'),
 (5,'Brown'),
 (6,'Purple');

//create a users table
CREATE TABLE  `arraytest`.`users` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `colorarray` varchar(65) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;

in.php

<?php

 $dbconnection // this is my db connection file.

    $color = array();
        if(isset($_POST['color'])){
        $color  = $_POST['color'];
        }else{
        $color = NULL;
        }   

    $newcolor = implode(", ", $color);

  if(isset($_POST['submit'])){
    mysql_query("insert into users (colorarray) values ('{$newcolor}')")
    or die('No Insert: ' .mysql_error());
        HEADER("Location:out.php");
}

?>


<?php echo"<form action='{$_SERVER['PHP_SELF']}' method='post'> "; ?>
    <input type='hidden' name='doit' value='yes'>
<input type='checkbox' name = 'color[]' value = '1'> Red <br />
<input type='checkbox' name = 'color[]' value = '2'> Blue <br />
<input type='checkbox' name = 'color[]' value = '3'> Green <br />
<input type='checkbox' name = 'color[]' value = '4'> Yellow <br/>
<input type='checkbox' name = 'color[]' value = '5'> Brown <br />
<input type='checkbox' name = 'color[]' value = '6'> Purple </p>

<p><input type='submit' value='submit' name='submit'>

//out.php

So here is my output page, and I don't know where to go next. I can echo out the id and their array values, but have no idea how to do a query like SELECT id, colorarray from users
inner join colorkey.color_id = users.colorarray
where id='$id'
//so that it would output the users_id and the list of colors they've chosen.

<?php

 $dbconnection // this is my db connection file.

    $id = getvar("id");
    $colorarray =array();


    $result = mysql_query("Select id, colorarray from users ");
    while($row = mysql_fetch_array($result)){



    echo"{$row['id']} | {$row['colorarray']}<br />";
    }

Again, Thanks for taking the time to look at this and if you can point me in the correct direction I would be very grateful. Thank you.

[diafol]

It would help if you mentioned the nature of the issue? What do you want? What's currently wrong?

[dottomm]

Hi diafol. I'm sorry. I thought I was clear. I would like to take the value of colorarray (an array) and for each array piece, look it up against the colorkey table. So if the array value was '1,3' using the example above, the out.php page would display Red,Green. I hope this makes sense.

[diafol]

Sorry, for some reason, when I replied I could only see the last code snippet and nothing else. Strange.

[dottomm]

I had some issues while trying to post and had to edit it a few times. I'm sure you saw it while I was editing. Thanks for looking.

[diafol]

 $dbconnection // this is my db connection file.
    $color = array();
        if(isset($_POST['color'])){
        $color  = $_POST['color'];
        }else{
        $color = NULL;
        }   
    $newcolor = implode(", ", $color);
  if(isset($_POST['submit'])){
    mysql_query("insert into users (colorarray) values ('{$newcolor}')")
    or die('No Insert: ' .mysql_error());
        HEADER("Location:out.php");
}

OK, you need to escape all input otherwise you could get sql injection.

 $dbconnection // this is my db connection file.
 if(isset($_POST['color']) && !empty($_POST['color'])){
    $color  = (array) $_POST['color'];
    $newcolor = mysql_real_escape_string(implode(", ", $color)); //ESCAPED HERE
    mysql_query("insert into users SET colorarray ='$newcolor'") or die('No Insert: ' .mysql_error());
    header("Location:out.php");
 }

I pared down the original to my understanding, but IMO, this sin't quite the waty to do it. You data is relational and not flat. You shouldn't try to store arrays in a field - well not as a rule because you can't search effectively for a colour associated with an user.

I'd suggest:

COLORS

color_id
color

USERS

user_id
username (ect)

USERCOLORS

uc_id (optional)
user_id
color_id

The last table can have multiple rows for the same user.

So for your POST['color'] array:

INSERT INTO USERCOLORS (`user_id`, `color_id`) VALUES ( (34, 1) , (34, 3), (34, 4) )

Just a thought. The colors table can also be used to create your checkboxes.

[dottomm]

Thank you. I like your suggestion. It makes a lot of sense but I don't understand how would I insert a new row for each value?

[diafol]

OK, you need to build up a string to insert into the SQL:

//$user_id I assume will be from $_SESSION variable 
$arr = array();
$cols = (array) $_POST['color'];
foreach($cols as $col){
    $arr[] = "($user_id,$col)";
}
$colstring = implode(",",$arr);

$sql = "INSERT INTO USERCOLORS (`user_id`, `color_id`) VALUES ( $colstring )";

[dottomm]

Yes, Your example is great. One problem though is I am getting the error "Operand should contain 1 column". If I put the $colstring into my error message I can see that the colstring is populated correctly, but It won't let me insert it. Any idea? Thanks again.

[diafol]

Instead of running the query, do this:

echo "INSERT INTO USERCOLORS (`user_id`, `color_id`) VALUES ( $colstring )";
exit;

Then print the output here.

(I'm assuming that you have two fields to fill = user_id and color_id)

[dottomm]

Yes. So I ran the echo statement instead and it printed
"INSERT INTO USERCOLORS (user_id, color_id) VALUES ( (1,5),(1,6) )"

So it looks correct. 1=is the user_id and the second numeral coresponds with the color selected. But how to get the imploded into the two cols?

[diafol]

Sorry you don't need the outer brackets:

//$user_id I assume will be from $_SESSION variable 
$arr = array();
$cols = (array) $_POST['color'];
foreach($cols as $col){
    $arr[] = "($user_id,$col)";
}
$colstring = implode(",",$arr);
$sql = "INSERT INTO USERCOLORS (`user_id`, `color_id`) VALUES $colstring";

[dottomm]

Hi diafol! I apologize for my delayed response. I was unexpectedly off-line.
Thank you! That is exactly what I was trying to do! Thank you for your help. I've marked this as solved. You are a genius!