problems with inserting / updating arrays in mysql

endswithaW · April 12, 2007

whew. i've honestly been staring at this code for a week trying to search boards and use tutorials. here's the lowdown:

i'm trying to insert and update arrays from a form in mysql. currently the values are seperated by a pipe in the form. my code to pull the | seperated values from the database is working correctly, but i cannot get the values from the form to INSERT or UPDATE into the database.

i've tried implode and serialize without any luck.

in hopes of carrying on with my life, i was hoping someone could point me to a tutorial or give me the code i should be working with in my insert / update function. i'm no PHP expert, but i can hang. let me know if you need any more information to troubleshoot my information. as always, thanks!

the column name is "area", and i have the form looking like this:

<option value="Active Directory|">Active Directory</option>

<option value="ActivIdentity -Secure Single Sign-on|">ActivIdentity -Secure Single Sign-on</option>

<option value="Adobe Acrobat Reader|">Adobe Acrobat Reader</option>

<option value="Adobe Audition|">Adobe Audition</option>

<option value="Adobe Illustrator|">Adobe Illustrator</option>

<option value="Adobe Photoshop|">Adobe Photoshop</option>

...

</select>

and my insert statement is currently attempting to insert the | seperated values normally:

$area = $_POST['area'];

UPDATE tablename SET area='$area' WHERE id='$id';

pikemsu28 · April 12, 2007

sounds like to me you need to implode your values separated by |

$areaimplode = implode(' | ', $area);

remove the | from your form and implode the array before you post into the database.

<option value="Active Directory">Active Directory</option>

to retrieve the data, just explode the data into an array.

endswithaW · April 12, 2007

hm. an error. thanks for your quick reply. being at my desk all day allows me to stalk. :-)

Warning: implode() [function.implode]: Bad arguments. in /home/mgansert/public_html/dpr/update_record.php on line 12

let me paste some more code. i have some javascript that moves options from one select box to another select box. once they are moved, the source code ends up looking like this:

<option value="AIX">AIX</option>\n<option value="SASSY">SASSY</option>\n

</select>

here is my code for updating the database:

// let's sort out this mess of an "area" array

$area = $_POST['area'];

$areaimplode = implode('|' , $area);

...

$query = "UPDATE dpr SET title='$title', nupdate='$nupdate', status='$status', owner='$owner', tasks='$tasks', prlink='$prlink', sla='$sla', downtime='$downtime', area='$areaimplode', ccrc='$ccrc', ccrf='$ccrf' WHERE id='$id'";

$result = mysql_query($query) or die ("Error in query: ".$query.mysql_error());

Wildbug · April 12, 2007

Can you run the following and post output?


echo '<pre>',print_r($_POST['area'],true),'</pre>';

pikemsu28 · April 12, 2007

is the javascript moving the options from area to area1 to be inserted into the database?

endswithaW · April 12, 2007

the options are definately moving from one select box to the other. i can see it in the HTML source.

as for the pre tags, the output was blank. this makes me think that the variable is somehow not being passed to the processing script.

what else can i supply to help with troubleshooting?

Wildbug · April 12, 2007

It would be helpful to know what exactly is being passed to $_POST.


echo '<pre>',print_r($_POST,true),'</pre>';

The print_r() function prints variables and their contents, formatting arrays with indented elements. The PRE tags keep it spaced/formatted.

pikemsu28 · April 12, 2007

if you have javascript moving the options to area2, then i would think that you need to post area2 and implode area2. i dont know javascript that well to know exactly what it is doing.

endswithaW · April 12, 2007

this is my first shot at arrays, so be gentle.

';
  // Areas are defined, parse them out of the variable
  if ($row['area']) {
                $areas= preg_split('/\|/', $row['area']);
  }
  // Need to push a bogus key on the array $areas
  array_unshift($areas, "bogus_data");

  $area_result = mssql_query("SELECT DISTINCT SupPerson.sys_attr_value 'Primary Support Team' FROM sm_sys_attr SupPerson, sm_attr SupRole WHERE SupRole.attr_id = 317 AND SupPerson.attr_id = SupRole.attr_id;");

  for($x=0;$x<mssql_num_rows($area_result);$x++){
          $row_area=mssql_fetch_array($area_result);
          if ($key=array_search($row_area['Primary Support Team'], $areas)) {
          }
          else {
                  $no_affect[]=$row_area['Primary Support Team'];
          }
  }

// now let's allow the user to choose additional affected areas
echo '
<tr>
        <td width="25%"><b>Choose Areas:</b><br>'; getApps(); echo '</td>
        <td width="25%" valign="middle" align="center">
               <INPUT TYPE="button" NAME="right" VALUE=" > " ONCLICK="moveSelectedOptions(this.form[\'area1\'],this.form[\'area[]\'],true)"><br>
               <INPUT TYPE="button" NAME="right" VALUE="All >>" ONCLICK="moveAllOptions(this.form[\'area1\'],this.form[\'area[]\'],true)"><br>
               <INPUT TYPE="button" NAME="left" VALUE=" < " ONCLICK="moveSelectedOptions(this.form[\'area[]\'],this.form[\'area1\'],true)"><br>
               <INPUT TYPE="button" NAME="left" VALUE="All <<" ONCLICK="moveAllOptions(this.form[\'area[]\'],this.form[\'area1\'],true)">
        </td>
        <td width="25%">
        <b>Chosen Areas:</b><br>
        <select multiple name="area[]" id="thearea" onDblClick="moveSelectedOptions(this.form[\'area\'],this.form[\'area1[]\'],true)">
';

// Remvoe bogus entry from $areas
  array_shift($areas);
  if ($areas) {
          foreach ($areas as $value) {
                  echo '<option value="'.$value.'">'.$value.'</option>\n';
          }
  }
  else {
          echo '<option value="No Affected Areas">No Affected Areas</option>\n';
  }
echo '
        </select>
        </td>
</tr>
<tr>

the function "getApps()" has a select line. this is the list of apps the user can choose from. they get sent to the select box "area" (above)

<select multiple name="area1" onDblClick="moveSelectedOptions(this.form[\'area1[]\'],this.form[\'area[]\'],true)">

here is the full database update script. obviously this has variables that i didn't paste from the above form

include("inc/db_mysql.php");

// let's sort out this mess of an "area" array
$area = $_POST['area'];
$areaimplode = implode('|' , $area);

// define variables

        $id = $_POST['id'];
        $title = $_POST['title'];
        $owner = $_POST['owner'];
        $status = $_POST['status'];
        $ccrc = $_POST['ccrc'];
        $ccrf = $_POST['ccrf'];
        $sla = $_POST['sla'];
        $downtime = $_POST['downtime'];
        $tasks = $_POST['tasks'];
//        $area = $_POST['area'];
        $prlink = $_POST['prlink'];
        $nupdate = nl2br($_POST['nupdate']);

if((!$title) | (!$ccrc) | (!$ccrf) | (!$downtime) | (!$prlink) | (!$nupdate) | (!$tasks)) {
        $title = "";
        $ccrc = "";
        $ccrf = "";
        $downtime = "0";
        $prlink = "";
        $nupdate = "";
        $tasks = "";
        };

$query = "UPDATE dpr SET title='$title', nupdate='$nupdate', status='$status', owner='$owner', tasks='$tasks', prlink='$prlink', sla='$sla', downtime='$downtime', area='$areaimplode', ccrc='$ccrc', ccrf='$ccrf' WHERE id='$id'";

$result = mysql_query($query) or die ("Error in query: ".$query.mysql_error());

$msg=base64_encode("Record Updated!");
header("Location: view_record.php?id=$id&msg=$msg");

// end.
?>

and, of course, i'll take any recommendations! thanks for everyone's input.

fenway · April 12, 2007

Why are you storing pipe-delimeted values in a database?

endswithaW · April 13, 2007

ah. good question. i would usually insist on normalizing the database, but i have to deal with a backwards compatibility issue as well as importing information from a non-normalized database. ugly, isn't it?

Wildbug · April 13, 2007

Show us $_POST. Don't make me beg.

endswithaW · April 13, 2007

you mean echo out the variables that are getting posted?

it's very odd since i have the user typing in piped variables in text boxes and it posts to the database fine. my thought is that the select box isn't passing the $area variable to the scipr that posts to the database.

i wonder why i can't just post to the database as a literal string? i don't see why php has to care about arrays in this case. i guess it has to do with the select box.

fenway · April 13, 2007

i wonder why i can't just post to the database as a literal string?

What does this mean?

endswithaW · April 13, 2007

my first solution (hack obviously) was to just do a simple insert and include the pipe in the actual option. again, pulling this out of the database hasn't been a problem. just getting it to INSERT or UPDATE isn't working.

<select name="area">
<option value="AIX|">AIX</option>
<option value="HPUX|">HPUX</option>
</select>

and do a INSERT or UPDATE directly into the db.

[code]
$query = "UPDATE dpr SET area='$area' WHERE id='$id'";

[/code]

Wildbug · April 13, 2007

you mean echo out the variables that are getting posted?

Yes, please.


echo '<pre>',print_r($_POST,true),'</pre>';

endswithaW · April 13, 2007

It appears that the select box isn't being passed as a variable to the script.

Array
(
    [id] => 1
    [title] => This is a serious problem!
    [status] => L
    [ccrc] => 12245|99898
    [ccrf] => 12341
    [sla] => T
    [downtime] => 34549
    [owner] =>  Customer Service
    [prlink] => http://sites/ProblemMgmt/Incident%20Reports/AIX_LOGIN_BUG%20-%20050806.doc
    [nupdate] => blah blah blah.  blah blah blah. blach. #2.  updated. updated. again. work!@
ENter\' Key TEST special cha\'racters )($#*&@#)@*#@_)#(_$&*@(
yes.

yes.  blah blah blah.  blah blah blah. blach. #2.  updated. updated. again. work!@
ENter Key special characters )($#*&@#)@*#@_)#(_$&*@(
yes.\'

yes.
    [tasks] => 99199|92384
    [submit] => >>>>
)

Here's the "area" portion of my code. Again, the javascript just moves values from one select box to the other. Ugly.

<tr>
';
                        $temp=0;
                        if ($row['area']) {
                                $areas= preg_split('/\|/', $row['area']);
                                $number=count($areas);
                                foreach ($areas as $area) {
                                        if (! $area)
                                                $temp++;
                                        $number -=1;
                                }
                                while ($temp>0) {
                                        array_pop($areas);
                                        $temp -=1;
                                }
                                $number=count($areas);
                                if ($number>0)
                                        echo "<td width=\"25%\" colspan=\"3\"><b>Affected Areas:</b>";
                                        foreach ($areas as $area) {
                                                if ($number>1)
                                                        echo " $area,&nbsp";
                                                else
                                                        echo " $area";
                                                $number -=1;
                                        }
                        }

echo '
        </td>
</tr>
';
  // Areas are defined, parse them out of the variable
  if ($row['area']) {
                $areas= preg_split('/\|/', $row['area']);
  }
  // Need to push a bogus key on the array $areas
  array_unshift($areas, "bogus_data");

  $area_result = mssql_query("SELECT DISTINCT SupPerson.sys_attr_value 'Primary Support Team' FROM sm_sys_attr SupPerson, sm_attr SupRole WHERE SupRole.attr_id = 317 AND SupPerson.attr_id = SupRole.attr_id;");

  for($x=0;$x<mssql_num_rows($area_result);$x++){
          $row_area=mssql_fetch_array($area_result);
          if ($key=array_search($row_area['Primary Support Team'], $areas)) {
          }
          else {
                  $no_affect[]=$row_area['Primary Support Team'];
          }
  }
echo '
<tr>
        <td width="25%"><b>Choose Areas:</b><br>'; getApps(); echo '</td>
        <td width="25%" valign="middle" align="center">
<INPUT TYPE="button" NAME="right" VALUE=" > " ONCLICK="moveSelectedOptions(this.form[\'area1\'],this.form[\'area[]\'],true)"><br>
               <INPUT TYPE="button" NAME="right" VALUE="All >>" ONCLICK="moveAllOptions(this.form[\'area1\'],this.form[\'area[]\'],true)"><br>
               <INPUT TYPE="button" NAME="left" VALUE=" < " ONCLICK="moveSelectedOptions(this.form[\'area[]\'],this.form[\'area1\'],true)"><br>
               <INPUT TYPE="button" NAME="left" VALUE="All <<" ONCLICK="moveAllOptions(this.form[\'area[]\'],this.form[\'area1\'],true)">
        </td>
        <td width="25%">
        <b>Chosen Areas:</b><br>
        <select multiple name="area[]" id="thearea" onDblClick="moveSelectedOptions(this.form[\'area\'],this.form[\'area1[]\'],true)">
';

// Remvoe bogus entry from $areas
  array_shift($areas);
  if ($areas) {
          foreach ($areas as $value) {
                  echo '<option value="'.$value.'">'.$value.'</option>\n';
          }
  }
  else {
          echo '<option value="No Affected Areas">No Affected Areas</option>\n';
  }
echo '
        </select>
        </td>
</tr>

maybe i should be passing $value? no. that wouldn't make sense since no $value is showing up in the pre code.

endswithaW · April 13, 2007

now i remember why it has to be an array and i can't just pass it a literal value. when just using one select box, the problem is that it will only pass the last highlighted option.

that is a possible solution. abandon the javascript and multiple select boxes and just pass the array. what would that code look like? i imagine passing $area to an implode statement.

endswithaW · April 13, 2007

Ahh. Replying to myself again...

Here is the output if I abandon the multiple select boxes, have each option looking like this:

<option value"AIX|">AIX</option>

that seems like a hack though. implode would fix that?

and send area as $area[]

Array
(
    [id] => 1
    [title] => This is a serious problem!
    [status] => L
    [ccrc] => 12245|99898
    [ccrf] => 12341
    [sla] => T
    [downtime] => 34549
    [area] => Array
        (
            [0] => ActivIdentity -Secure Single Sign-on|
            [1] => Arta|
            [2] => AV Equipment|
            [3] => Double-Take |
        )

    [owner] => Customer Service
    [prlink] => http://sites/ProblemMgmt/Incident%20Reports/AIX_LOGIN_BUG%20-%20050806.doc
    [nupdate] => blah blah blah.  blah blah blah. blach. #2.  updated. updated. again. work!@
ENter Key special characters )($#*&@#)@*#@_)#(_$&*@(
yes.

yes.  blah blah blah.  blah blah blah. blach. #2.  updated. updated. again. work!@
ENter Key special characters )($#*&@#)@*#@_)#(_$&*@(
yes.

yes.
    [tasks] => 99199|92384
    [submit] => >>>>
)

Wildbug · April 13, 2007

Thank you.

(This paragraph referred to your previous post, not the most recent one.)

Okay, it doesn't look like any of your SELECT boxes are being passed via POST. Did you have any selected when you ran the print_r($_POST) code? If not, could you run it again? If data is being passed, I'd like to see what it is.

There should either be an area[] or area1[] bit.

On the other hand, are you sure your JavaScript is correct? I coded a page last year using a similar paradigm, moving choices from one SELECT to another SELECT and collecting those in the second SELECT via the PHP script. The problem was that moving SELECT items from one box to another was a non-trivial process in JavaScript.

Finally, are you selecting all the items in the "area1" SELECT before submitting the form? Only selected items are passed to the server from a MULTIPLE SELECT. That means you need a function that's called onSubmit to select everything in area1.

endswithaW · April 13, 2007

Thanks for that. I've decided to abandon the multiple select boxes for a simple select box. The database is updating fine using the implode code that was submitted earlier. Thanks for that.

The only issue I have now is ADDING and DELETING entries in the array list as opposed to it being cleared out everytime the person edits the row. Is there a way to add this functionality? Of course, I'll start searching this forum for an answer...

Second question: would explode be able to replace this?

';
                        $temp=0;
                        if ($row['area']) {
                                $areas= preg_split('/\|/', $row['area']);
                                $number=count($areas);
                                foreach ($areas as $area) {
                                        if (! $area)
                                                $temp++;
                                        $number -=1;
                                }
                                while ($temp>0) {
                                        array_pop($areas);
                                        $temp -=1;
                                }
                                $number=count($areas);
                                if ($number>0)
                                        echo "<td width=\"25%\" colspan=\"3\"><b>Affected Areas:</b>";
                                        foreach ($areas as $area) {
                                                if ($number>1)
                                                        echo " $area,&nbsp";
                                                else
                                                        echo " $area";
                                                $number -=1;
                                        }
                        }

I'm so close to finishing this app I can taste it. Thanks for everyone's input! I'll be sure to put you in the credits even though noone outside the admins will see it.

Wildbug · April 13, 2007

I don't quite understand the bit about "ADDING and DELETING." Could you elaborate?

What is that code doing, removing empties? Use the PREG_SPLIT_NO_EMPTY flag for preg_split() instead of that foreach loop.

endswithaW · April 13, 2007

eh. scratch that code above. it's not important to my rollout.

essentially, i have a list of 300 applications in a select multiple box. the user picks the applications affected, adjusts some other data, and hits submit. i want them to be able to add and delete applications (what i'm storing as an array) if they make a mistake after the submit.

i can't think of a way to do it with select multiple. maybe checkboxes or something? let me know if i'm still unclear. :-X

Wildbug · April 13, 2007

What you're doing sounds exactly like what I was doing last year. I had a long OPTION list (400+) in a SELECT MULTIPLE. Since the list was so long, I figured users would be more likely to make mistakes using Ctrl+click while making a complex selection throughout the list (i.e., forgetting to Ctrl-click after selecting a few would deselect them). My solution was to create another SELECT MULTIPLE and provide buttons to move selections between the boxes and clear the selection. Like I said, this turned out to be non-trivial, but I did get it working.

Sounds like what you're doing, right?

Within JavaScript SELECT OPTIONS are objects, and you'll need to use the document.createElement method(s). MSIE acts differently than other browsers, so you'll need to take that into account (it's not too hard). The JavaScript goes through one SELECT MULTIPLE, finds SELECTED OPTIONs, adds them to the other SELECT MULTIPLE, then removes them from the first. As far as editing previous choices, I had my options in a seperate database table (user_id,option), but if you want to keep your delimited string, you just need to explode it into an array, and, while outputting each SELECT OPTION, see if that option is in_array($users_exploded_string). If so, mark it SELECTED. Then have an onLoad function to move the SELECTED options into the second SELECT MULTIPLE. If the user makes changes, overwrite the previous value.

You could use checkboxes, but it's messier. Also, I think there might be a greater performance hit on slower computers/older browsers for rendering 300 checkboxes vs. a SELECT MULTIPLE. But that is an option.

Is that helpful, or a confusing mess? If it looks like it's what you need, maybe I can send you my code, with my specific site stuff chopped out.

endswithaW · April 13, 2007

I would like to see that code. Can the user add and remove the select items from the second select multiple box? That is what I implemented, but since I had so many problems with the insert (see thread) I abandoned my approch.

I imagine I would need some information regarding your database format and how you pull those values out after the user inserts them.

Thanks for everyone's help the past few days. I'll keep posting until I get a complete resolution that way the post doesn't end up being a thread of 30 questions and no solution. :-)

Sign In

problems with inserting / updating arrays in mysql

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information