Extracting an array into dynamic variables

Question

I am trying to be lazy (or smart): I have 7 checkboxes which correlate with 7 columns in a MySQL table.

The checkboxes are posted in an array:

$can = $_POST['can'];

I've created the following loop to dump the variables for the MySQL insert:

for($i=1;$i<8;$i++){
    if($can[$i] == "on"){
        ${"jto_can".$i} = 'Y';
    }
    else{
        ${"jto_can".$i} = 'N';
    }
}
print_r($jto_can1.$jto_can2.$jto_can3.$jto_can4.$jto_can5.$jto_can6.$jto_can7);

This correctly outputs:

YYNYYYY

However, when I attempt to use those variables in my MySQL update, it doesn't accept the changes.

mysqli_query($db, "UPDATE jto SET jto_can1 = '$jto_can1', jto_can2 = '$jto_can2', jto_can3 = '$jto_can3', jto_can4 = '$jto_can4', jto_can5 = '$jto_can5', jto_can6 = '$jto_can6', jto_can7 = '$jto_can7' WHERE jto_id = '$id'")or die(mysqli_error($db));

Can anyone explain why the print_r displays the variables whereas MySQL update does not?

Stick with the array, there's no point or benefit in creating all of those variables. — nickb
– nickb, Commented Feb 25, 2013 at 18:09
Also, you're missing a space in between the "WHERE" here: jto_can7 = '$jto_can7'WHERE jto_id = '$id' — nickb
– nickb, Commented Feb 25, 2013 at 18:09
You'd be better off making your update statement in the loop you have, and appending the values to the end. Have an array of the column names, and then those values, and going from there. — Gyhth
– Gyhth, Commented Feb 25, 2013 at 18:13
@thebarless can you echo the query to know what is printing... — Vineet1982
– Vineet1982, Commented Feb 25, 2013 at 18:37

nickb · Accepted Answer · 2013-02-25 18:14:16Z

2

Stick with the array, and form the query dynamically:

$sql = 'UPDATE jto SET ';

$cols = array();
foreach( range( 1, 7) as $i) {
    $value = $_POST['can'][$i] == 'on' ? 'Y' : 'N'; // Error check here, $_POST['can'] might not exist or be an array
    $cols[] = 'jto_can' . $i . ' = "' . $value . '"'; 
}

$sql .= implode( ', ', $cols) . ' WHERE jto_id = "' . $id . '"';

Now do a var_dump( $sql); to see your new SQL statement.

answered Feb 25, 2013 at 18:14

nickb

59.7k13 gold badges115 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc B · Accepted Answer · 2013-02-25 18:16:02Z

1

this is not a mysql problem. mysql will only see what you put into that string. e.g. dump out the query string BEFORE you do mysql_query. I'm guessing you're doing this query somewhere else and have run into scoping problems. And yes, this is lazy. No it's not "smart". you're just making MORE work for yourself. What's wrong with doing

INSERT ... VALUES jto_can1=$can[0], jto_can2=$can[1], etc...

answered Feb 25, 2013 at 18:16

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

thebarless Over a year ago

The only difficulty I have with this method is that I'm not seeing how to control that $can[0] get's a "N" value when the box is unchecked. The array will only pass the checked ones (unless I'm missing something).

Collectives™ on Stack Overflow

Extracting an array into dynamic variables

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related