array into DB:

Continuation of thread: Entering mutiple value with checkbox into mysql

[FelixMc]

Hi all, I've read quite a few posts already about serialising an array and then imploding? however I've not much experience - my code is a bit like lego at the moment. I'm getting 'N;' in my db instead of a nice list.

...
<br>
<input type="checkbox" name="Membership[]" value="value1"> value1
<br>
<input type="checkbox" name="Membership[]" value="value2"> value2
<br>
<input type="checkbox" name="Membership[]" value="value3"> value3
<br>
br>
...
<?php
include "connect.php";

$membershiparray = serialize($_POST['Membership']);
$sql="INSERT INTO generic_email (Name, Membership, OU, email, Not_listed, Additional, Further_Info)
VALUES
('$_POST[Name]','$membershiparray','$_POST[OU]','$_POST[email]','$_POST[Not_listed]','$_POST[Additional]','$_POST[Further_Info]')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";

mysql_close($con)

?>

[cameraman]

Welcome to WebmasterWorld, FelixMc!
To reference an array element in quoted text, you need to surround it with braces. You should also surround an alpha index with quotes. So each of your POST elements should look like:
('{$_POST['Name']}'...

Once you see that work, have a look at mysql_real_escape_string() [us3.php.net] to protect yourself against sql injection attacks.

[xiongbin]

how long as column Membership in you table ..you can use
join(",",$_POST['Membership']) as a string ..before insert into db

[FelixMc]

Cameraman, thanks for the advice: I've added the braces and the quotes. Not inserting yet.

Xiongbin, also thankyou. I tried join as you suggested however my coding isn't great so not sure if I'm setting it up correctly.

I've patched a working solution but it's not very efficient, if anyone fancies rewriting I think it would be a good lesson (please :-)

...
<br>
<input type="checkbox" name="Membership1" value="value1"> value1
<br>
<input type="checkbox" name="Membership2" value="value2"> value2
<br>
<input type="checkbox" name="Membership3" value="value3"> value3
<br>
br>
...

/
<?php

include "connect.php";

$array = array($_POST['Membership1'],$_POST['Membership2'],$_POST['Membership3']);
$comma_separated = implode(",", $array);

$sql="INSERT INTO generic_email (Name, Membership, OU, email, Not_listed, Additional, Further_Info)
VALUES
('{$_POST['Name']}','$comma_separated','{$_POST['OU']}','{$_POST['email']}','{$_POST['Not_listed']}','{$_POST['Additional']}','{$_POST['Further_Info']}')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";

mysql_close($con)

?>

[cameraman]

I'm guessing you made the Membership checkboxes scalars because you had problems with the syntax for accessing them; just so's you know (and if this is the case, and pardon me if it's not ;) ), it would be:
$_POST['Membership'][0]
Also note that if the checkbox isn't checked, it won't be defined in the POST array; that is, if someone hasn't checked one of the boxes, this will cause an error:
$array = array($_POST['Membership1'],$_POST['Membership2'],$_POST['Membership3']);

I think it would be better to put them back as an array.

Do you have any single or double quotes in the data you're trying to insert? If so, that could cause the insert to fail; mysql_real_escape_string would fix that, too. Try escaping each of the POSTed fields.
$name = mysql_real_escape_string($_POST['Name']);
$membershiparray = mysql_real_escape_string(serialize($_POST['Membership']));
$ou = mysql_real_escape_string($_POST['OU']);
.
.
$sql = "INSERT ...('$name','$membershiparray',...";

[FelixMc]

Thanks Cameraman, I appreciate your advice. I couldn't understand how I was unable to recreate the errors you described. I read up on mysql_real_escape_string and thought 'yep, better do that!, however upon looking at connect.php (which was given to me at work) I see it's already done for me. Have posted below, I think it's good way to keep the db & pwd hidden and strips everything for you.

I've kinda got what I need so the tempation is to move on but I'll stick with trying to get the array working for a couple more hours.

<?php
$con = mysql_connect();
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

@mysql_select_db("DB", $con); // use the database called DB if (mysql_errno())

if(!get_magic_quotes_gpc())
{
$_GET = array_map('mysql_real_escape_string', $_GET);

$_POST = array_map('mysql_real_escape_string', $_POST);

$_COOKIE = array_map('mysql_real_escape_string', $_COOKIE);
}
else
{
$_GET = array_map('stripslashes', $_GET);

$_POST = array_map('stripslashes', $_POST);

$_COOKIE = array_map('stripslashes', $_COOKIE);

$_GET = array_map('mysql_real_escape_string', $_GET);

$_POST = array_map('mysql_real_escape_string', $_POST);

$_COOKIE = array_map('mysql_real_escape_string', $_COOKIE);
}
$adminmail="[e-mail]";

$path="http://www.path.com/email";

[edited by: FelixMc at 10:57 am (utc) on June 5, 2008]

[cameraman]

Interesting. You may find that you don't always want to do that before you have a chance to process the content. Keep it in mind any time you encounter unexpected results.

For this situation, try doing an echo on $membershiparray (the serialized form of the check boxes). Then comment out the line that includes connect.php and any lines that call database functions (or just be prepared to ignore the expected errors) and echo $membershiparray again. Examining both should clue you in as to whether it's causing a problem. If you've assigned alpha content to the checkbox values, you'll want to call mysql_real_escape_string after the serialize, as I did in my post above.

If t'were me, I think I'd put this just after the select_db line in the connect script:
if(!defined('raw_input')) {

and of course a matching closing brace just before the $adminmail line. Then if you're in a situation where you want to work with the data before it gets all escaped up, you do:
define('raw_input',1);
include 'connect.php';

That is, you define it before you include the other script. And, obviously, you'd have to escape it yourself after your manipulations.