Add a value to a multi value list when updating

Hi Jerry,

I've created a couple of functions for adding and removing values to a multi list field:

  $blog = mysql_get('blog',25);

  function addValueToListValues($values, $newValue){
    if(!strstr($values, "\t$newValue\t")){
       return $updatedValue = $values.$newValue."\t";
    }else{
      return $values;
    }
  }

  function removeValueFromListValues($values, $removeValue){
    if(strstr($values, "\t$removeValue\t")){
      return $updatedValue = str_replace($removeValue."\t",'', $values);
    }else{
      return $values;
    }
  }

  $updatedValue = addValueToListValues($blog['user'],'9');
  mysql_update('blog',25, null, array('user' => $updatedValue));



So a multi list field stores its items in an unordered tab separated string. So to add a value to a field you need to add the value and a tab to the end of the current string. Then save that value using the mysql_update function or your own custom MySQL.

So to use these functions you need to pass in the current tab separated string (in this example I'm using a field called user which is a multi checkbox list of users. 

If I wanted to remove a user I would use the removeValueFromListValues function like this:

  $updatedValue = removeValueFromListValues($blog['user'],'9');
  mysql_update('blog',25, null, array('user' => $updatedValue));

Let me know if you have any questions about implementing these functions into your code.

Thanks!

Greg

Greg,

Thanks for tackling this.

I attached a stripped down version of the viewer in it’s current state.

The way I’m understanding you is not quite correct, because it creates a new record instead of appending the old one. (Also, I don’t know what the number 25 stands for.)

If my table is called: attendance_test
and the field that I want to update is called: salon_name
and the value that I want to insert is: 1
And the record that I want to update is identified by matching the Attendee ID entered to the record number

The function below gets inserted in the code somewhere in the page before it’s required.

<?php    function addValueToListValues($values, $newValue){
    if(!strstr($values, "\t$newValue\t")){
       return $updatedValue = $values.$newValue."\t";
    }else{
      return $values;
    }
  }
  ?>

Then in the code where I want to the update to occur I’d remove:

 salon_name = '1',

And insert:

$my_var = mysql_get('attendance_test',25);
$updatedValue = addValueToListValues($my_var['salon_name'],'1');
  mysql_update('attendance_test',25, null, array(''salon_name' => $updatedValue));

Thanks again for helping to sort this out.

Jerry

Hi Jerry, 

Your code looks correct to me, I think you just need to pass in the correct num value into the mysql_get and mysql_update functions.

I probably should have been a bit clearer on how the mysql_update and mysql_get functions work.

So the mysql_get function returns a record in an array from the database, you pass in the tablename and the record number you want to get. 

$array = mysql_get($tableName, $recordNum);

The mysql_update function is used to update a record, you pass in the table name, a record number, then a MySQL where statement, and finally an array of field names and values that you want updating. 

//You would normally pass in a $recordNum OR a $whereStatement, not both eg:
mysql_update($tableName, null, $whereStatement, $arrayOfValues);

OR:

mysql_update($tableName, $recordNum, null, $arrayOfValues);


So in the example code I gave you the previous post the mysql_get function was retrieving a record with a num value of 25, and then trying to update the same record.

I've looked through your code, but I can't see the num value of the record you're trying to update. Are you trying to update more than one record?

Thanks!

Greg

Hi Greg,

Thank you again for helping me to work this through. Sorry I'm not as good at coding as I'd like to be.

The record number in this case is the Attendee number that's entered by the attendee as their 'express sign in ID' -  @$_REQUEST['attendee_number']

And this seems to work. but if you have any suggestions, or security concerns, please let me know...

    // Check for attendee ID number. Update record if ID number is found.
      if (@$_REQUEST['attendee_number']  && !$errorsAndAlerts) {  
               
     $lecturedate =  date("m-y").'AWC, ' ;
                 
      $query = "UPDATE `{$TABLE_PREFIX}attendance_test` SET
                    attended = CONCAT(attended, '$lecturedate'),
                     updatedByUserNum = '".mysql_escape( $CMS_USER['num'] )."',
                     updatedDate      = NOW()
                      WHERE num = '".mysql_escape( $_REQUEST['attendee_number'] )."'";
                      
                      $recnumber = mysql_escape(@$_REQUEST['attendee_number']);    
                          
    
     function addValueToListValues($values, $newValue){
    if(!strstr($values, "\t$newValue\t")){
       return $updatedValue = $values.$newValue."\t";
    }else{
      return $values;
    }
  }
        
    $my_var = mysql_get('attendance_test',$recnumber);
$updatedValue = addValueToListValues($my_var['salon_name'],'1');
  mysql_update('attendance_test',$recnumber, null, array('salon_name' => $updatedValue));
 
   $loggedInUser = mysql_get('attendance_test', $_REQUEST['attendee_number']);
                       
                      // Get the attendee's first name from the updated record.
                        $first_name = @$loggedInUser['first_name'];
                        mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
      $userNum = mysql_insert_id();

      // on success
       $_REQUEST        = array(); // clear form
     $errorsAndAlerts = "Thanks $first_name. We've signed you in, and we're ready for the next guest.";
    }
    }

___________

It also works in another update scenario, where the attendee doesn't have their ID, and they enter their name, and email address, and the email address is used to match the record.

Again, if you have any suggestions, or security concerns, please let me know...

// Check for first name, last name, and duplicate email address. Update record if email address is found. 
      if (!@$_REQUEST['attendee_number'] && $count > 0 && !$errorsAndAlerts) {  
            @$_REQUEST['first_name']=ucwords(@$_REQUEST['first_name']);        
            @$_REQUEST['last_name']=ucwords(@$_REQUEST['last_name']);    
     $lecturedate =  date("m-y, ") ;
                 
      $query = "UPDATE `{$TABLE_PREFIX}salon_listings` SET
                     first_name         = '".mysql_escape( $_REQUEST['first_name'] )."',
                     last_name         = '".mysql_escape( $_REQUEST['last_name'] )."',
                     mmm_attended = CONCAT(mmm_attended, '$lecturedate'),
                     updatedByUserNum = '".mysql_escape( $CMS_USER['num'] )."',
                     updatedDate      = NOW()
                    WHERE email_address = '".mysql_escape( $_REQUEST['email_address'] )."'";
      mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
      $userNum = mysql_insert_id();

// Get the value of  $recnumber

$recnumber =    mysql_get('attendance_test', $CMS_USER['num']);

    function addValueToListValues($values, $newValue){
    if(!strstr($values, "\t$newValue\t")){
       return $updatedValue = $values.$newValue."\t";
    }else{
      return $values;
    }
  }
        
    $my_var = mysql_get('attendance_test',$recnumber);
$updatedValue = addValueToListValues($my_var['salon_name'],'1');
  mysql_update('attendance_test',$recnumber, null, array('salon_name' => $updatedValue));

      // on success
      $first_name = $_REQUEST['first_name'] ;
    $_REQUEST        = array(); // clear form
      $errorsAndAlerts = "Thanks $first_name, we've signed you in, and we're ready for the next guest.";
    }
   

Thanks,

Jerry Kornbluth

Hi Jerry,

Glad you've got it working, your code looks good.

My only suggestion would be to consider using the mysql_update function instead of using this update system:

    $query = "UPDATE `{$TABLE_PREFIX}salon_listings` SET
                     first_name         = '".mysql_escape( $_REQUEST['first_name'] )."',
                     last_name         = '".mysql_escape( $_REQUEST['last_name'] )."',
                     mmm_attended = CONCAT(mmm_attended, '$lecturedate'),
                     updatedByUserNum = '".mysql_escape( $CMS_USER['num'] )."',
                     updatedDate      = NOW()
                    WHERE email_address = '".mysql_escape( $_REQUEST['email_address'] )."'";
      mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
      $userNum = mysql_insert_id();

But that's my personal preference.

Cheers

Greg

Greg,

Thanks for the tip.

I value your personal preferences and will incorporate your suggestion.

I'll publish the complete solution shortly so that others can benefit.

Jerry Kornbluth

Oops, I guess I'm not really sure how to use mysql_update when there are multiple fields involved.

Jerry

Hi Jerry,

Here is an example of how to use it:

  $updateArray = array(
    'title' => 'This is a new title',
    'content' => '<p>Here is some replacment content</p>'
  );
  mysql_update('table_name', $recordNum, $where, $updateArray);


So if you want to update multiple fields you pass in an array where the key is the field name you want to update, and the value is the data you want to replace in the field. So in this example I'm updating the title and content fields.

Cheers

Greg