inserting array from multiple select form into db
bkeep
Status: Curious
Joined: 12 Mar 2006
Posts: 9
Reply Quote
hello i have searched a few other places and came across a few posts on this site but i still seem to not get it.
In my form i have a select box form element that
this is this also resides in a tpl file in a smarty based application
:: Code ::

<form action="events.php" method="post" name="events" id="events">

<select name="invitation[]" size="4" multiple="multiple">
  <option value="I1">Amature</option>
  <option value="I2">Club Tournament</option>
  <option value="I3">Must Qualify</option>
  <option value="I4">Public / Open</option>
  <option value="I5">Professional</option>
  <option value="I6">Membership Required</option>
</select>

<input name="submit" type="submit" value="Submit" />
</form>


there are of course other elements to this form but ill leave them out for clarity once this is submitted everything is passed to the events.php and runs though this

:: Code ::

$tplEvents = new TplLoad();

if (getParam("submit","")) {
       $invitation = serialize($_POST["invitation"]);
      $sql = "INSERT INTO events (invitation) VALUES ( '" . $invitation . "')";
      $res = query($sql);
{


and that inserts this a:3:{i:0;s:2:"I1";i:1;s:2:"I2";i:2;s:2:"I3";}
is this a good way to submit data into one mysql field? or is there a better way and if this is an acceptable way of doing things is there any issues with using regular names instead of I1 I2 so on so forth
Thank You for any advice you may have
regards,
Brandon
Back to top
techAdmin
Status: Site Admin
Joined: 26 Sep 2003
Posts: 4124
Location: East Coast, West Coast? I know it's one of them.
Reply Quote
From what I can tell, as long as you've done adequate protection against mysql injection type attacks, all you are doing in this case is inserting a string into mysql, which seems to be a very easy way to handle it.

I like easy solutions, and I like simple solutions, especially when it comes to mysql, so to me this is a good solution.

:: Quote ::
serialize() returns a string containing a byte-stream representation of value that can be stored anywhere.

This is useful for storing or passing PHP values around without losing their type and structure. serialize: php.net

Since passing php values around without losing their type or structure is exactly what you want, this seems perfect to me.

I've seen other people here have huge issues with complex solutions, that didn't work in the end, lots of time spent, for little result.

You even figured out how to do the php arrays on forms, [], that one is tricky until you realize how it works.

Since you're dealing with strings only here, it wouldn't matter what you used as the field values, as long as the serialize/unserialize functions created the correct output. That's what I would test, just serialize it, then unserialize it to make sure the data is back in the form you wanted.
Back to top
jeffd
Status: Assistant
Joined: 04 Oct 2003
Posts: 594
Reply Quote
Make sure to read the php.net comments on serialize to avoid any issues you might have, there are some gotcha's there in terms of the data.

I'm not sure what the limits are with serialize in terms of what characters may trigger a mysql error:

:: Quote ::
I recently had a problem inserting serialized array in mysql DB using PEAR DB_Base class functions. mySQL fails to insert the serialized array because the serialized data contains semicolon. A way to solve that problem is to make same changes to the serialized data:

$serializeorder = serialize($order);
$serializeorder = preg_replace("#;#msi",'0x3B',$serializeorder);
$serializeorder = preg_replace("/(\n)|(\r)/"," ",$serializeorder);
$serializeorder = addcslashes($serializeorder, "\0..\37!@\@\177..\377");
$serializeorder = addslashes($serializeorder);

Here we substitute the semicolon with it's hex. We alse get rid of cariage return. Remember to make the vise versa substitution again when extracting the data.

This would also depend on what you are using to clean the data for mysql insertion in the first place.
Back to top
bkeep
Status: Curious
Joined: 12 Mar 2006
Posts: 9
Reply Quote
Hello again thought id post what my resolution was to get this all working the serialize function works pretty slick.

i pull the data with this.

:: Code ::

      $sql = "SELECT * FROM events";
      $res = query($sql);
           $c=0;
      while ($a_row = mysql_fetch_array($res)) {

      $invitation[$c]["club_name"] = $a_row["club_name"];
      $invitation[$c]["contact_name"] = $a_row["contact_name"];

      $invitation[$c]["invitation"] = unserialize($a_row["invitation"]);
           $c++;
    }
$tplEvents->assign('invitation',$invitation);
$tplEvents->display("events.tpl");


that pulls the data out of the database and gets me an array and then that gets passed over to the tpl file and we loop the output

:: Code ::

{foreach item="inv" from=$invitation}
    {$inv.club_name}<br />
    {$inv.contact_name}<br />
   
   <ul>
    {foreach item="detail" from=$inv.invitation}
        <li>{$detail}</li>
    {/foreach}
   </ul>

{/foreach}


and of course now that we know it works add some security
i found this to be a pretty good article www.nyphp.org/phundamentals/storingretrieving.php
and other posts on this forum are good sources too

Thank you for your time
Regards,
Brandon
Back to top
jeffd
Status: Assistant
Joined: 04 Oct 2003
Posts: 594
Reply Quote
brandon, thanks for posting the completed solution, that's always a good way to finish off a programming thread, very helpful to other visitors.

Glad that method worked for you, I assume you didn't run into any of the serialize issues that were warned about.

Just in case somebody may not understand mysql etc security, we always run the mysql data through this function:

:: Code ::
function stripslashes_deep($value)
{
   $value = is_array($value) ?
               array_map('stripslashes_deep', $value) :
               stripslashes($value);

   return $value;
}

/**
 * Quote a variable to make it safe for insertion
 */
function quote_smart($value)
{
   // Stripslashes if we need to
   if (get_magic_quotes_gpc()) {
       $value = stripslashes_deep($value);
   }

   // Quote it if it's not an integer
   if (!is_int($value)) {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }

   return $value;
}

Which we simply add to the main sql connection library file. Then you put the data that is going to be inserted into the quote_smart($data) function. That's a slightly more simple method than the link above has, probably not quite as flexible, but I've found it's more than adequate for most mysql stuff.
Back to top
serialize and unserialize
vyjayanth
Status: New User - Welcome
Joined: 18 Nov 2009
Posts: 1
Reply Quote
I have serialized the data and inserted the selection to data base but when I reselect the user I do not see the selected physicians highlighted, Please help me out


$_POST['Refer_to_phy'] = serialize($_POST['Refer_to_phy']);
$_POST['Refer_to_phy'] = htmlentities($_POST['Refer_to_phy']);


<select name="Refer_to_phy[]" multiple="multiple" class='text_9' style="width:150px" size="6" id="refer_to">
<option value=''>--Select Physicians--</option>
<?php
$vquery_c = "select * from users order by id";
$vsql_c = mysql_query($vquery_c);

while($vrs=mysql_fetch_array($vsql_c))
{
$pro_ty=$vrs["user_type"];
if($pro_ty==1)
{
$phyName_drop = $vrs['fname'];
if($vrs['fname'] != '' && $vrs['lname'] != '')
{
$phyName_drop = $vrs['lname'].', '.$vrs['fname'];
}
else if($vrs['fname'] == '' && $vrs['lname'] != ''){
$phyName_drop = $vrs['lname'];
}
$phyName_drop .= $vrs['mname'];
echo "<option value='$vrs[id]' $sel >".trim(ucwords($phyName_drop))."</option>";

$sel='';
}
}
?>


</select>
Back to top
Display posts from previous:   

All times are GMT - 8 Hours