Multiple Updates 1

[d0nny]

OK, I've been grappling with this for a while and now I give up.

I have a long list of 'products' on a page with various fields associated with each product. This is laid out in a tabular format, one product per line.
Two of the columns in this table are 'active' and 'stock'. Active defines if the product is active on the customer facing side and stock defines whether the product is in stock (I'm not getting into the debate about whether a product should be shown if it is active and not in stock... I know, I know!).

But basically I want to have this page of products to update the DB only for these two checkboxes.
So if I tick the 'active' box for a certain product and perhaps update the 'stock' box for another product, then select 'Submit' at the bottom of the page, I only want these products updated with their new values.

You help will be much appreciated.

 

[jpadie]

Hi Don
forgive me but I don't understand the rules you are specifying

 

[d0nny]

I have a page which lists products.
Each product has title, description, code, price, stock, active listed in a single row, with multiple products listed on the page.

I want to update the products table with the stock and active values (1 or 0) using checkboxes within the table.
So I could select several products to be active from that list and then submit the form and the table is updated with these values. I also want to update the stock checkboxes on the same form.
So two values per product, multiple products per form.

 

[jpadie]

ok. that's straightforward.

can you post a sample of the html form that you use? or point us at a url?

 

[jman78]

You will have to define the checkboxes as arrays and parse through the arrays to update the table.

I did something like this recently, but it was in Ruby on Rails - seems to be a lot easier to create/access arrays of checkboxes.

Should be able to set it up similarly in PHP, however...

Jason

 

[alan92rttt]

Use an array.

You control names can be an array so you could have an array of all of the values on the page.

Its been a while since I needed/wanted to do this so bear with me this is from memory.

Your resulting HTML should look something like this
<input type="hidden" name="key[1]" value="16546">
<input type="hidden" name="stockorig[1]" value="0">
<input type="text" name="stock[1]" value="0">
<input type="hidden" name="availorig[1]" value="0">
<input type="text" name="avail[1]" value="0">

<input type="hidden" name="key[2]" value="7982">
<input type="hidden" name="stockorig[2]" value="1">
<input type="text" name="stock[2]" value="1">
<input type="hidden" name="availorig[2]" value="0">
<input type="text" name="avail[2]" value="0">


When the page is posted your $_POST array will contain several arrays. Now you just need to parse through the array looking for changes and then issue update when you find one. You could skip the stockorig and availorig and just check the DB for changes but I think the array is faster.

Alan

 

[d0nny]

Ok, here's my code for listing the products...

...
$query = "select * from products where main='$maincat' and secondary='$cat' order by sortorder";
$result = mysql_query ($query);
...

if ($result) {
	echo '<form name="updateForm" method="post" action="'.$_SERVER['PHP_SELF'].'">';
    echo '<table align="center" cellspacing="0" cellpadding="2" border="1" style="color:#000000; font-size:12px; font-family:arial; margin-bottom:10px;">';
    echo '<tr bgcolor="#006666" style="font-weight:bold; color:#FFFFFF;"><td colspan="7">'.ucfirst($maincat).' - '. ucfirst($cat) . '</td></tr>';
    echo '<tr bgcolor="#006666" style="font-weight:bold; color:#FFFFFF;"><td>Title</td><td>Description</td><td>Code</td><td>Price</td><td>Active</td><td>In Stock</td><td>Image</td></tr>';
    // Fetch and print all the records.
     $a = 0;
     $b = 0;
     while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
      $pathto = '/home/account/public_html'.$row[8];
      $mysock = getimagesize($pathto);
      echo "<tr class='normal11'>";
      echo "<input type='hidden' name='id[$a]' value='{$row[0]}' />";
      echo "<td align='left'><a href='product_edit.php?id=$row[0]'>$row[4]</a></td>";
      echo "<td align='left'>$row[5]</td>";
      echo "<td align='left'>$row[6]</td>";
      echo "<td align='left'>&pound;$row[7]</td>";
      
      echo "<td align='center'>";
      echo "<input type='checkbox' name='active[$a]' value='1' ";
      if ($row['15'] == 1) { echo "checked"; } else {};
      echo " /></td>";
      
      echo "<td align='center'>";
      echo "<input type='checkbox' name='bespoke[$b]' value='1' ";
      if ($row['13'] == 1) { echo "checked"; } else {};
      echo " /></td>";
      
      echo "<td><img src='$row[8]' ". imageResize($mysock[0],$mysock[1], 100);    
      echo ">";
      echo '</tr>';
      ++$a;
      ++$b;
    }
    echo '<tr><td colspan="7" align="center">';
    echo '<input type="submit" name="Submit" value="Save" />';
    echo '</td></tr>';
    echo '</table>';
    
    echo '</form>';
    mysql_free_result ($result);
    }

Actually, I think I've managed to fix it now as I had now action on my form. I've now added the above and the data is now updating.
I suppose I haven't listed my submit condition in my script to do the update either...

But does this make sense??

 

[d0nny]

Nope, it doesn't work.
It seems to work on the first couple of products I update, but not for all of them.

Here's my update script...

if ($_POST['Submit'] == 'Save') {
     // find out how many records there are to update
     $sizeA = count($_POST['active']);
     $sizeB = count($_POST['bespoke']);

     $a = 0;
     $b = 0;
     while ($a < $sizeA) {
          // define each variable
          $Active = $_POST['active'][$a];
          $Bespoke = $_POST['bespoke'][$b];
          $id = $_POST['id'][$a];
          
          $query = "UPDATE products SET `active`='$Active', `bespoke`='$Bespoke' WHERE `id` = '$id' LIMIT 1";
          mysql_query($query) or die ("Error in query: $query");
          ++$a;
          ++$b;
     }
}

Forgot to mention... don't worry about the 'bespoke' parameter... I am reusing this field in the table as a stock checkbox. I know, I know...

 

[alan92rttt]

I can't see why it's not working. Try echoing your updates to screen and take close look at them.

Alan

 

[vacunita]

From what I see there's no way to actually correlate the check boxes to the ID's of the rows that need updating.

Check boxes will only get submitted if they are checked otherwise they don't appear in the $_POST array.

Your Id's however exist in hidden fields that will always be submitted.
so you get a list of ID's and n way to match them to the checked checkboxes.

What I would do, is use the value property of the checkboxes to store the ID of the item needing to be updated.

That way you can use it to form the UPDATE statements.

This however only works for the items that were not checked and are now checked, if they where checked but the user unchecks them there's no way to know because unchecked items do not get submitted.


You'll need to build a before array so you can compare that to whatever was sent. If your before array contains say 3 checked values but your POSTed array only has 1 that means that 2 of them have been unchecked. You'll need to compare the remaining one to your control array to determine which ones where unchecked.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

like vacunita I think that the issue is one of checkboxes and null values. i'll bet your database columns are set to not null. that's why some updatse are working and some are not.

i suggest simplifying your processing code a bit and going with something more like this. The alternative is to use radio boxes rather than checkboxes where an explicit 'No' value can be submitted.

<?php
if (!empty($_POST['Submit']) && $_POST['Submit'] == 'Save') {
    foreach($_POST['id'] as $key=>$id){ //iterate the incoming data
    	//test the values of the checkboxes by examining whether they are set
    	$a = empty($_POST['active'][$key]) ? 0 : 1;
		$b = empty($_POST['bespoke'][$key]) ? 0 : 1;
		
		//ensure no spoof of the id
		$_id = mysql_real_escape_string($id);
		
		$query = "Update products set active=$a, bespoke=$b where id='$id'"; //no need for limit clause as id is the primary key
		mysql_query($query) or die ("Mysql error:<br/>".mysql_error() ."<br/>Query was:<br/>$query");
    }
} 
?>

remember also that the checked attribute for a checkbox is a key-value pair. you need to include 'checked="checked"' to be correct. by just including 'checked' you are relying on browsers' tolerance to fix your html.

let us know how you get on.

 

[d0nny]

OK, I must confess I'm not really sure I understand the solution posted by jpadie, but I copied and pasted it and it works.
I've done some extensive testing on it and everything does get updated, so many, many thanks for that.

As a subsequent request, after I submit the form, I get a blank page (except for my header and menu I have on the page), but I do not get any product listing that I started with.
Is there something I can add to the submission script that will then display the product listing again?

 

[jpadie]

explanation:

the way you have crafted your form means that for each 'row' you supply three fields called id[], active[] and bespoke[].

when the form is submitted there MUST be an id[] field returned for each row but there MAY be either active or bespoke or both.

my code relies on the presence of the id field and iterates through the superglobal $_POST['id'] to obtain the index and the id for each row.

then for each index found it checks to see whether an active field is present. if it is, then the value for $a is set to 1. if no field is present (unchecked) then it is set to zero. ditto for bespoke.

now as for the second question you will need to supply the cat and secondcat fields in the form and then run the normal page presentation code after the processing code. essentially just shove the block of code I posted at the top of the file and the rest should work ok (since I assume that $cat and $secondcat are derived from POST variables. If they are GET params then just add the query string to the action parameter in the form tag instead of coding hidden fields.) the other alternative is to store the cat/secondcat values in a session variable.

 

[d0nny]

Thanks.
That makes a lot more sense to me now. Thanks again.

As for the second part of the question, I was looking ot use the easiest option I know which is

header('Location: [URL unfurl="true"]http://www.example.com/');[/URL]

but I didn't think this was a great way to do it.

 

[jpadie]

nah.

your page is autoprocessing. so you don't need to do that. something like this

<<< PUT FORM PROCESSING CODE HERE >>>

<<<PUT NORMAL PAGE CODE BELOW >>>

And for the form

<form method="post" action="{$_SERVER['PHP_SELF']}?maincat=$maincat&cat=$cat" >

 

[d0nny]

Oh no, now another question....

The issue I have is I use the single quotes system generally in my code and I am outputting the form from an echo statement.
So using your example, this is what my code looks like...

echo '<form method="post" action="{$_SERVER['PHP_SELF']}?maincat=$maincat&cat=$cat">';

But that ain't gonna work!
How you best explain how I could achieve this?
Even if I use double quotes I'm not it works.

 

[jpadie]

oh. ok just concatenate?

echo '<form method="post" action="{$_SERVER['PHP_SELF']}?maincat='.$maincat.'&cat='.$cat.'">';

or use heredoc

echo <<<HTML
<form method="post" action="{$_SERVER['PHP_SELF']}?maincat=$maincat&cat=$cat">
HTML;

 

[alan92rttt]

if you take

echo '<form method="post" action="{$_SERVER['PHP_SELF']}?maincat=$maincat&cat=$cat">';

and change it to

echo '<form method="post" action="?maincat=$maincat&cat=$cat">';

It will work. The browser will see that you just put parameters in the action and just recall the page adding the parameters you specified in action.

Alan

 

[jpadie]

It will work.

No it won't. php does not expand variables within single quotes.

 

[alan92rttt]

oops missed that small detail. I never rely on PHP processing the variables in the quotes I always do it manually.

This will(should) work

echo '<form method="post" action="?maincat='.$maincat.'&cat='.$cat.'">';

Alan