updating multiple records with arrays

Slowie · May 31, 2011

hello guys moving on from inserting multiple records im now stuck with updating multiple records. i can get most of the details nailed but i need it to match the id's for the table the code i have in the sql part are

if (isset($_POST['submit'])) {
//Assign each array to a variable
$StaffMember = $_POST['StaffMember'];
$referrer = $_POST['referrer'];
$referred = $_POST['referred'];
$SentOut = $_POST['SentOut'];
$today = date("y.m.d H:i:s");
$user_id = $_SESSION['user_id'];
$IssueNum = $_POST['Referrerid'];
$limit = count($StaffMember);



$values = array(); // initialize an empty array to hold the values
for($k=0;$k<$limit;$k++){
$msg[] = "$limit New KPI's Added";



	$values[$k] = "( '{$StaffMember[$k]}', '{$referrer[$k]}', '{$referred[$k]}', '{$SentOut[$k]}', '{$today}', '{$user_id}' )"; // build the array of values for the query string
}
$query = "UPDATE `Referrer` (StaffMember, referer, referred, SentOut, 
								  SentOutDate, SentOutBy) 
								  VALUES " . implode( ', ', $values ) . "
								  WHERE IssueNum= '{$IssueNum[$k]}'";
								  

echo $query;




}

i obviously want the records updating where the issuenumber is the same and if the check box in the form is ticked

help with this one?

xyph · June 1, 2011

You're close, you just need to make sure you're defining an array when you want to, then loop through your results.

<?php 

for($k=0;$k<$limit;$k++){
$msg[] = "$limit New KPI's Added";	
$values[$k] = array(
	$StaffMember[$k],$referrer[$k],$referred[$k],$SentOut[$k],$today,$user_id
); // build the array of values for the query string
}

foreach( $values as $key => $value ) {
$query = "UPDATE `Referrer` (StaffMember, referer, referred, SentOut, 
	SentOutDate, SentOutBy) 
	VALUES ('" . implode( '\', \'', $value ) . "')
	WHERE IssueNum= '{$IssueNum[$key]}'";
mysql_query($query) or die(mysql_error());

?>

Psycho · June 1, 2011

I think we need to see your form. I don't think this will work as you need it based on this statement

i obviously want the records updating where the issuenumber is the same and if the check box in the form is ticked

The problem is that only ckeckboxes that are checked are passed in the POST data. If you are naming you fields as arrays with empty indexes so that they are automatically numerically indexed you will not be able to associate the checkboxes with the corresponding fields. For example, if you had five checkboxes and the user checked boxes 1, 3 & 5 they would be posted as an array with indexes of 0, 1, & 2. You should specify the value of the checkboxes as the primary index value of the records. Then in the input fields that correspond to each checkbox, name the fields as an array but explicitly assign the index for the arrays with the primary index value. You can then determine which checkboxes were checked (by their value) and then associate that with the appropriate fields with the corresponding values.

Slowie · June 1, 2011

ah it ok the only checkbox for each record is one which changes a 0 to a 1 one its submitted

Slowie · June 1, 2011

it is

<?php 
include 'dbc.php';


page_protect();
company();

	   
$Referrer = mysql_query("SELECT * FROM Referrer WHERE SentOut='0' ");



if (isset($_POST['submit'])) {
//Assign each array to a variable
$StaffMember = $_POST['StaffMember'];
$referrer = $_POST['referrer'];
$referred = $_POST['referred'];
$SentOut = $_POST['SentOut'];
$today = date("y.m.d H:i:s");
$user_id = $_SESSION['user_id'];
$IssueNum = $_POST['Referrerid'];
$limit = count($StaffMember);


for($k=0;$k<$limit;$k++){
$msg[] = "$limit New KPI's Added";	
$values[$k] = array(
	$StaffMember[$k],$referrer[$k],$referred[$k],$SentOut[$k],$today,$user_id
); // build the array of values for the query string
}

foreach( $values as $key => $value ) {
$query = "UPDATE `Referrer` (StaffMember, referer, referred, SentOut, 
	SentOutDate, SentOutBy) 
	VALUES ('" . implode( '\', \'', $value ) . "')
	WHERE IssueNum= '{$IssueNum[$key]}'";
mysql_query($query) or die(mysql_error());


}


								  






}



if (checkAdmin()) {





?>



            
<html>
<head>
<title>Book Off Holiday</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script src="php_calendar/scripts.js" type="text/javascript"></script>

<link href="styles.css" rel="stylesheet" type="text/css">
</head>

<body>



<form name="form" action="SendReferrers.php" method="post"> 
<table width="100%" border="0" cellspacing="0" cellpadding="5" class="main">
  <tr> 
    <td colspan="3"> </td>
  </tr>
  
  
   
   
    <td width="160" valign="top">
	<?php 
		if (isset($_SESSION['user_id'])) {
		}


	?>
		<a href="admin.php">Admin CP </a>


</td>


  
    <td width="732" valign="top">

<p>
	<h3 class="titlehdr">New KPI</h3>  




  <table width="300px" border="0" align="Centre" cellpadding="2" cellspacing="0">
          <tr bgcolor="#000050">
            
          
		<td width="20px"><h3 class="Text2">Referrer ID</h3></td>
            <td width="20px"><h3 class="Text2">Staff Member</h3></td>
		<td width="20px"><h3 class="Text2">referrer</h3></td>
		<td width="20px"><h3 class="Text2">referred</h3></td>
		<td width="40px"><h3 class="Text2">Sent Out</h3></td>



                   </tr>
         
          <?php while ($rrows = mysql_fetch_array($Referrer)) {?>
          <tr> 
          
	  
  			<td><h3 class="Text3"><input type="" name="Referrerid[]" id="Referrerid[]" size="4" value="<?php echo $rrows['IssueNum'];?>" /></h3></td>
		<td><h3 class="Text3"><input type="" name="StaffMember[]" id="StaffMember[]" size="4" value="<?php echo $rrows['StaffMember'];?>" /></h3></td>
		<td><h3 class="Text3"><input type="" name="referrer[]" id="referrer[]" value="<?php echo $rrows['referer'];?>" /></h3></td>
		<td><h3 class="Text3"><input type="" name="referred[]" id="referred[]" value="<?php echo $rrows['referred'];?>" /></h3></td>
		<td><h3 class="Text3"><input name="SentOut[]" type="checkbox" value="1" id="SentOut[]"></h3></td>




                 
          </tr>
        
          <?php } ?>
  </table>
<input name="submit" type="submit" id="submit" value="Create">



</table>
</form> 
</body>
</html>
<?php }
	?>

Psycho · June 1, 2011

ah it ok the only checkbox for each record is one which changes a 0 to a 1 one its submitted

Say what? You are creating multiple sets of fields to edit each record, but you are not providing any reference in those fields to be able to determine which record each is for. And, you have a lot of code that makes no sense whatsoever. For example:

			if (isset($_SESSION['user_id'])) {
		}

That does absolutely nothing!

if (checkAdmin()) {

Before that IF statement is all the "update" logic and after it comes the HTML output. That doesn't seem right. That means if a user is not an admin you want them to be able to make the updates but not see the form. You should probably have a checkAdmin() check at the top of the page. If it fails then send them to another page or display an error message.

Not to mention you have a ton of invalid HTML in your code: giving multiple elements the same ID, not setting the type on input fields, etc., etc.

This was all coded on-the-fly and untested so I am sure there are some errors.

<?php 
include 'dbc.php';
page_protect();
company();

if (!checkAdmin()) { exit(); }

//Check if form was submitted
if (isset($_POST['submit']))
{
    //Define values common to all records
$SentOutDate = date("y.m.d H:i:s");
$SentOutBy = $_SESSION['user_id'];

    foreach($_POST['SentOut'] as $IssueNum)
    {
        //Create and run update queries
        $StaffMember = mysql_real_escape_string(trim($_POST['StaffMember'][$IssueNum]));
        $referrer    = mysql_real_escape_string(trim($_POST['referrer'][$IssueNum]));
        $referred    = mysql_real_escape_string(trim($_POST['referred'][$IssueNum]));

        $query = "UPDATE `Referrer`
                      (StaffMember, referer, referred, SentOut, SentOutDate, SentOutBy) 
                  VALUES
                      ('$StaffMember', '$referrer', '$referred', '1', '$SentOutDate', '$SentOutBy')
                  WHERE IssueNum= '{$IssueNum}'";
        mysql_query($query) or die (mysql_error());
    }
}

//Create and run query to generate form fields
$query = "SELECT * FROM Referrer WHERE SentOut='0'";
$result = mysql_query($query);
if(!$result)
{
    $formFields .= "<tr><td colspan=\"5\">";
    $formFields .= "There was an error running the query<br><br>\n";
    $formFields .= "Query: {$query}<br>\n";
    $formFields .= "Error: " . mysql_error();
    $formFields .= "</td><tr>";
}
elseif(mysql_num_rows($result)<1)
{
    $formFields .= "<tr><td colspan=\"5\">There were no results.</td><tr>";
}
else
{
    while ($row = mysql_fetch_assoc($result))
    {
        $id = $row['IssueNum'];
        $formFields .= "<tr> \n";
        $formFields .= "<td><h3 class=\"Text3\"><input type=\"text\" name=\"Referrerid[]\" id=\"Referrerid_{$id}\"size=\"4\" value=\"{$id}\" /></h3></td>\n";
        $formFields .= "<td><h3 class=\"Text3\"><input type=\"text\" name=\"StaffMember[{$id}]\" id=\"StaffMember_{$id}\" size=\"4\" value=\"{$row['StaffMember']}\" /></h3></td>\n";
        $formFields .= "<td><h3 class=\"Text3\"><input type=\"text\" name=\"referrer[{$id}]\" id=\"referrer_{$id}\" value=\"{$rows['referer']}\" /></h3></td>\n";
        $formFields .= "<td><h3 class=\"Text3\"><input type=\"text\" name=\"referred[{$id}]\" id=\"referred_{$id}\" value=\"{$row['referred']}\" /></h3></td>\n";
        $formFields .= "<td><h3 class=\"Text3\"><input type=\"checkbox\" name=\"SentOut[]\" value=\"{$id}\" id=\"IssueNum_{$id}\"></h3></td>\n";
        $formFields .= "</tr>\n";
    }

}

?>
<html>
<head>
<title>Book Off Holiday</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script src="php_calendar/scripts.js" type="text/javascript"></script>

<link href="styles.css" rel="stylesheet" type="text/css">
</head>

<body>
<form name="form" action="SendReferrers.php" method="post"> 
<table width="100%" border="0" cellspacing="0" cellpadding="5" class="main">
    <tr> 
        <td colspan="3"> </td>
    </tr>
        <td width="160" valign="top">
            <a href="admin.php">Admin CP </a>
        </td>
        <td width="732" valign="top">
            <p><h3 class="titlehdr">New KPI</h3>  
            <table width="300px" border="0" align="Centre" cellpadding="2" cellspacing="0">
                <tr bgcolor="#000050">
                    <td width="20px"><h3 class="Text2">Referrer ID</h3></td>
                    <td width="20px"><h3 class="Text2">Staff Member</h3></td>
                    <td width="20px"><h3 class="Text2">referrer</h3></td>
                    <td width="20px"><h3 class="Text2">referred</h3></td>
                    <td width="40px"><h3 class="Text2">Sent Out</h3></td>
                </tr>
                <?php echo $formFields; ?>
            </table>
            <input name="submit" type="submit" id="submit" value="Create">
        </td>
    </tr>
</table>
</form> 
</body>
</html>

Slowie · June 2, 2011

Thank you for your input and the code im well aware my code is very messy at the moment but im gradually getting better as i read more code provided by kind people like yourself. the issue im having with your code is that it still is throwing back a mysql error like so

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( SentOut, SentOutDate, SentOutBy) VALUES ' at line 2

but OMG your code is tidy and thank you ill try coding more like this in the future.

any idea why it would throw the error?

EDIT

I have now done this thank you so so so so much

the error was in the mysql query itself rather than using

$query = "UPDATE `Referrer`
                      (StaffMember, referer, referred, SentOut, SentOutDate, SentOutBy) 
                  VALUES
                      ('$StaffMember', '$referrer', '$referred', '1', '$SentOutDate', '$SentOutBy')
                  WHERE IssueNum= '{$IssueNum}'";

i Used

 $query = "UPDATE `Referrer` SET SentOut='1', SentOutDate='$SentOutDate', SentOutBy='$SentOutBy'
                 WHERE IssueNum= '{$IssueNum}'";

and it works so smoothly

again Thank you

Sign In

updating multiple records with arrays

Recommended Posts

Slowie

Link to comment

Share on other sites

xyph

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Slowie

Link to comment

Share on other sites

Slowie

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Slowie

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information