Update Multiple Rows in MySQL Database

Discussion in 'PHP' started by Norrad, Feb 14, 2012.

0
  1. #1
    Hi There,

    I am having a major problem with updating multiple rows in my Database from my php script.
    It's a simple staff rating system that I am developing for our call centre.

    Below is my current code:
    
$post_record = $_POST['record'];
$post_complete = $_POST['complete'];
$post_satisfied = $_POST['satisfied'];
$post_reason = $_POST['reason'];
$post_other = $_POST['other'];
$post_rate = $_POST['rate'];
$post_overall = $_POST['overall'];
$post_proceed = $_POST['proceed'];

foreach ($post_record as $callback_id) {
	$edit_callback = mysql_query("UPDATE callbacks SET callback_complete='1', callback_staff='" . $callback_staff . "', callback_satisfied='" . $post_satisfied . "', callback_reason='" . $post_reason . "', callback_other='" . $post_other . "', callback_rate='" . $post_rate . "', callback_overall='" . $post_overall . "', callback_proceed='" . $post_proceed . "' WHERE callback_id='" . $callback_id . "'");
}
    Code (markup):
    And my form looks something like this:

    
      <tr>
        <td width="20"><input type="hidden" name="record[30]" value="30" /><input name="complete[30]" type="checkbox" value="1"  /></td>
        <td><strong>Mr Test User</strong><br />
          <small>USA</small><br />
          <font color="#000000"><strong>Darron</strong></font></td>
        <td>+1 654 7624<br />
          test@test.com</td>
        <td>ACR</td>
        <td>Manual</td>
        <td><select name="satisfied[30]">
          <option selected="selected">Yes</option>
          <option >No</option>
        </select></td>
        <td><select name="reason[30]">
          <option selected="selected">Price</option>
          <option >Offering</option>
          <option >Timeline</option>
          <option >Lack of funding</option>
          <option >Other</option>
        </select></td>
        <td><select name="other[30]">
          <option selected="selected">Yes</option>
          <option >No</option>
        </select></td>
        <td>SA Flyer</td>
        <td><select name="rate[30]">
          <option selected="selected">1</option>
          <option >2</option>
          <option >3</option>
          <option >4</option>
          <option >5</option>
        </select></td>
        <td><select name="overall[30]">
          <option selected="selected">1</option>
          <option >2</option>
          <option >3</option>
          <option >4</option>
          <option >5</option>
        </select></td>
        <td><select name="proceed[30]">
          <option selected="selected">Yes</option>
          <option >No</option>
        </select></td>
      </tr>
      <tr>
        <td width="20"><input type="hidden" name="record[31]" value="31" /><input name="complete[31]" type="checkbox" value="1"  /></td>
        <td><strong>Mrs Nittaya Beetge</strong><br />
          <small>Thailand</small><br />
          <font color="#0000ff"><strong>Michelle</strong></font></td>
        <td>+66 87 337 3367<br />
          nongneungka@gmail.com</td>
        <td>PPL-A</td>
        <td>Manual</td>
        <td><select name="satisfied[31]">
          <option selected="selected">Yes</option>
          <option >No</option>
        </select></td>
        <td><select name="reason[31]">
          <option selected="selected">Price</option>
          <option >Offering</option>
          <option >Timeline</option>
          <option >Lack of funding</option>
          <option >Other</option>
        </select></td>
        <td><select name="other[31]">
          <option selected="selected">Yes</option>
          <option >No</option>
        </select></td>
        <td>SA Flyer</td>
        <td><select name="rate[31]">
          <option selected="selected">1</option>
          <option >2</option>
          <option >3</option>
          <option >4</option>
          <option >5</option>
        </select></td>
        <td><select name="overall[31]">
          <option selected="selected">1</option>
          <option >2</option>
          <option >3</option>
          <option >4</option>
          <option >5</option>
        </select></td>
        <td><select name="proceed[31]">
          <option selected="selected">Yes</option>
          <option >No</option>
        </select></td>
      </tr>
    Code (markup):
    Any ideas about where I may be going wrong?
     
    Solved! View solution.
    Norrad, Feb 14, 2012 IP
  2. #2
    Do it like this?

    foreach ($_POST['record'] AS $id => $value)
    {
    // update statement with $_POST['overal'][$id] AND $_POST['proceed'][$id] and so on.
    }

    This should work even better..

    BTW always SECURE your data when you add it to the database by using MYSQL_REAL_ESCAPE_STRING (read on PHP.net what it does!)
     
    EricBruggema, Feb 14, 2012 IP
  3. adityamenon

    adityamenon Peon

    Messages:
    28
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    0
    #3
    Can you elaborate on what the "major problem" is? Are you getting an error screen? What does the error say?

    Did you try outputting the SQL queries generated by your loop into plaintext? Usually seeing this you can easily point out where the problem is...
     
    adityamenon, Feb 14, 2012 IP
  4. Andre91

    Andre91 Peon

    Messages:
    197
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    0
    #4
    Since the data collected from the form is intended for multiple fields, you should store them in an array when it's collected in your PHP script. That way you can loop through the array one by one and update that specific field.

    Your whole setup as it stands, both PHP and HTML is kinda poorly written, but I wrote this PHP code to work with the HTML that you have provided, although if it were me, I'll scrap everything and write better code both HTML and PHP. Try the following PHP, and tell me if it works. I didn't check for errors cuz I'm not gonna take time to set up a database etc for testing.



    
<?php
$numOfRecords = 0;
$thisRecord;
$x = 0;

foreach($_POST as $variableName => $value){
	$thisVar = substr($variableName, 0, 6);
	if ($thisVar == 'record'){ $numOfRecords++; 
		$thisRecord[$i] = preg_replace("/[^0-9]/", '', $variableName);
		$x++;
	}
}

for ($i=0; $i<$numberOfRecords; $i++){
	$id = $thisRecord[$i];
	$post_record[$i] = $_POST['record['.$id.']'];
	$post_complete[$i] = $_POST['complete['.$id.']'];
	$post_satisfied[$i] = $_POST['satisfied['.$id.']'];
	$post_reason[$i] = $_POST['reason['.$id.']'];
	$post_other[$i] = $_POST['other['.$id.']'];
	$post_rate[$i] = $_POST['rate['.$id.']'];
	$post_overall[$i] = $_POST['overall['.$id.']'];
	$post_proceed[$i] = $_POST['proceed['.$id.']'];
}

for ($i=0; $i<$numberOfRecords; $i++){
	$edit_callback = mysql_query("UPDATE callbacks SET callback_complete='1', callback_staff='".$callback_staff."', callback_satisfied='".$post_satisfied[$i]."', callback_reason='".$post_reason[$i]."', callback_other='".$post_other[$i]."', callback_rate='".$post_rate[$i]."', callback_overall='".$post_overall[$i]."', callback_proceed='".$post_proceed[$i]."' WHERE callback_id='".$post_record[$i]."'");
}

?>
    PHP:
     
    Andre91, Feb 14, 2012 IP
  5. Norrad

    Norrad Well-Known Member

    Messages:
    483
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    128
    #5
    Thanks everyone,

    Managed to get it sorted using EricBruggema's suggestion. Thanks for the input Andre91, I looked at your code and tried implementing some of it into the new callback form. It works great adding records but now I am having a problem with form validation, it adds a blank record even if the newname field isn't filled in:

    
$post_newcomplete = $_POST['newcomplete'];
	$post_newname = $_POST['newname'];
	$post_newemail = $_POST['newemail'];
	$post_newcell = $_POST['newcell'];
	$post_newcountry = $_POST['newcountry'];
	$post_newcourse = $_POST['newcourse'];
	$post_newhear = $_POST['newhear'];
	$post_newestimate = $_POST['newestimate'];
	$post_newsatisfied = $_POST['newsatisfied'];
	$post_newreason = $_POST['newreason'];
	$post_newother = $_POST['newother'];
	$post_newrate = $_POST['newrate'];
	$post_newoverall = $_POST['newoverall'];
	$post_newproceed = $_POST['newproceed'];
	
	$limit = count($post_newname);
	
	for ($i = 0; $i<$limit; $i++) {
		$post_newcomplete[$i] = mysql_real_escape_string($post_newcomplete[$i]);
		$post_newname[$i] = mysql_real_escape_string($post_newname[$i]);
		$post_newemail[$i] = mysql_real_escape_string($post_newemail[$i]);
		$post_newcell[$i] = mysql_real_escape_string($post_newcell[$i]);
		$post_newcountry[$i] = mysql_real_escape_string($post_newcountry[$i]);
		$post_newcourse[$i] = mysql_real_escape_string($post_newcourse[$i]);
		$post_newhear[$i] = mysql_real_escape_string($post_newhear[$i]);
		$post_newestimate[$i] = mysql_real_escape_string($post_newestimate[$i]);
		$post_newsatisfied[$i] = mysql_real_escape_string($post_newsatisfied[$i]);
		$post_newreason[$i] = mysql_real_escape_string($post_newreason[$i]);
		$post_newother[$i] = mysql_real_escape_string($post_newother[$i]);
		$post_newrate[$i] = mysql_real_escape_string($post_newrate[$i]);
		$post_newoverall[$i] = mysql_real_escape_string($post_newoverall[$i]);
		$post_newproceed[$i] = mysql_real_escape_string($post_newproceed[$i]);
		
		$add_callback = mysql_query("INSERT INTO callbacks (callback_complete, callback_staff, callback_date, callback_name, callback_email, callback_cell, callback_country, callback_course, callback_hear, callback_estimate, callback_satisfied, callback_reason, callback_other, callback_rate, callback_overall, callback_proceed) values ('" . $post_newcomplete[$i] . "', '" . $cookie_staff . "', '" . $report_date . "', '" . $post_newname[$i] . "', '" . $post_newemail[$i] . "', '" . $post_newcell[$i] . "', '" . $post_newcountry[$i] . "', '" . $post_newcourse[$i] . "', '" . $post_newhear[$i] . "', '" . $post_newestimate[$i] . "', '" . $post_newsatisfied[$i] . "', '" . $post_newreason[$i] . "', '" . $post_newother[$i] . "', '" . $post_newrate[$i] . "', '" . $post_newoverall[$i] . "', '" . $post_newproceed[$i] . "')");
	}
    Code (markup):
     
    Norrad, Feb 14, 2012 IP
  6. heroz

    heroz Active Member

    Messages:
    513
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #6
    what is the error messages you have received ??
     
    heroz, Feb 14, 2012 IP
  7. Norrad

    Norrad Well-Known Member

    Messages:
    483
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    128
    #7
    No error message. It just inserts a new row even if the $post_newname field is empty.
     
    Norrad, Feb 15, 2012 IP
  8. EricBruggema

    EricBruggema Well-Known Member

    Messages:
    1,740
    Likes Received:
    28
    Best Answers:
    13
    Trophy Points:
    175
    #8
    then try to validate your data before you insert it into your database.

    php.net/empty or php.net/strlen (check length of string)

    Coz i had some spare time, i rewrote your coding, please use this style to work with queries, its easier to find errors and give other scripters a faster view on the working of your code

    

    foreach ($_POST['newname'] A $id => $val)
    {
        // check if newname is empty? if not, use it for the database
        if (!empty($val))
        {
            $add_callback = mysql_query("INSERT INTO callbacks (callback_complete, callback_staff, callback_date, callback_name, callback_email, 
                                                                callback_cell, callback_country, callback_course, callback_hear, callback_estimate, 
                                                                callback_satisfied, callback_reason, callback_other, callback_rate, callback_overall, 
                                                                callback_proceed) 
                                          VALUES ('" . mysql_real_escape_string($_POST['newcomplete'][$id]) . "', 
                                                  '" . mysql_real_escape_string($cookie_staff) . "', 
                                                  '" . mysql_real_escape_string($report_date) . "', 
                                                  '" . mysql_real_escape_string($_POST['newname'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newemail'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newcell'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newcountry'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newcourse'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newhear'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newestimate'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newsatisfied'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newreason'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newother'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newrate'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newoverall'][$id]) . "', 
                                                  '" . mysql_real_escape_string($_POST['newproceed'][$id]) . "')");
        
        }
    }
    Code (markup):
    i have removed all 'strings' i don't know if your script uses them at other positions but i don't think so. Hope you learn from this :)
     
    Last edited: Feb 15, 2012
    EricBruggema, Feb 15, 2012 IP
  9. ankushsharma

    ankushsharma Peon

    Messages:
    19
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    Thanks for sharing nice information.I have been looking information on the how to update multiple rows in the table..This discussion has solve my concern to a great extent.I am very grateful..
     
    ankushsharma, Feb 19, 2012 IP