checkbox and mysql

Hi

i have database table that i displayed for the user  to choose a value for status field that maybe can accept two values 0 ou 1
how i can update it to the mysql using php

i can show my script and what i want to do
****
<?php
 $p='Infos Updated';
$db=mysql_pconnect("localhost:3306","admin","admin");
mysql_select_db("product",$db);
if (isset($_POST['update'])) {
  foreach ($_POST['update'] as $pkey => $newval) {
    $sql = "UPDATE product SET status='$newval' WHERE id='$pkey'";
    if (mysql_query($sql)){
               echo $p;
                $p3=$p;
                if ($p==$p3){
                   $p=' ';
                 }
                 
             else{
                 echo '<p>Error Updating '.mysql_error().'</p>';
             }
}    
 }

}
$query= "SELECT * FROM product  order by type,-seq DESC" ;
// get the rows and put them in an array
$result=mysql_query($query);
if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}
?>
<table bgcolor='#AAccff' border="0" width='100%'
<tr>
<td id='td' width='15%'>Prod_id</td>
<td id='td' width='30%'>Category</td>
<td id='td' width='34%'>product</td>
<td id='td' width='15%'>Type</td>
<td id='td' width='6%'>Status</td></tr></table>
 
 
<?php
if (mysql_num_rows($result) == 0) {
    echo "No rows found";
    exit;
}
$myArray=array();
  while ($row = mysql_fetch_assoc($result)){
     $myArray[]=$row;}
   
?>
<form action="<?php $_SERVER['PHP_SELF']?>" method="POST">
  <table bgcolor='#AAccAA' border="0" width='100%'>
   <?php foreach ($myArray as $values): ?>
      <tr>
      <td width='15%'><?= $values['prod_id']?></td>
      <td width='30%'><?= $values['cat_id']?></td>
      <td width='34%'><?= $values['product']?></td>
      <td width='15%'><?= $values['type']?></td>
      <td><input type="checkbox" checked name="update[<?=$values['id']?>]" value="<?=$values['status']?>"></td>
    </tr>
    </tr>
   <?php endforeach; ?>
  </table>
 <input type="submit" value="Update">
</form>
</body>
</html>
asaidiAsked:
Who is Participating?
 
FugasConnect With a Mentor Commented:
Here zou have it. Tested.
<?php
 $p='Infos Updated';
$db=mysql_pconnect("localhost:3306","admin","admin");
mysql_select_db("product");
if (isset($_POST['update_button'])) {
  if(isset($_POST['update'])){
    $query1 = "UPDATE product SET status=1 WHERE status=0 and id in(". implode(',',       

$_POST['update']).")";
    $query2 = "UPDATE product SET status=0 WHERE status=1 and id not in(". implode(',',     

$_POST['update']).")";

    if(!mysql_query($query1)){
         echo '<p>Error Updating '.mysql_error().'</p>';
     }
    if(!mysql_query($query2)){
         echo '<p>Error Updating '.mysql_error().'</p>';
     }
   }
   else{
    if(!mysql_query("UPDATE product SET status=0")){
         echo '<p>Error Updating '.mysql_error().'</p>';
     }
   }
}   



$query= "SELECT * FROM product  order by type,-seq DESC" ;
// get the rows and put them in an array
$result=mysql_query($query);
if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}
?>
<table bgcolor='#AAccff' border="0" width='100%'
<tr>
<td id='td' width='15%'>Prod_id</td>
<td id='td' width='30%'>Category</td>
<td id='td' width='34%'>product</td>
<td id='td' width='15%'>Type</td>
<td id='td' width='6%'>Status</td></tr></table>
 
 
<?php
if (mysql_num_rows($result) == 0) {
    echo "No rows found";
    exit;
}
$myArray=array();
  while ($row = mysql_fetch_assoc($result)){
     $myArray[]=$row;}
   
?>
<form action="<?php $_SERVER['PHP_SELF']?>" method="POST">
  <table bgcolor='#AAccAA' border="0" width='100%'>
   <?php foreach ($myArray as $values): ?>
      <tr>
      <td width='15%'><?= $values['prod_id']?></td>
      <td width='30%'><?= $values['cat_id']?></td>
      <td width='34%'><?= $values['product']?></td>
      <td width='15%'><?= $values['type']?></td>
      <td><input type="checkbox" id="update_<?php echo $values['id']?>" name="update[]" value="<?php 

echo $values['id']?>" <?php echo $values['status']==1 ? 'checked' : '' ?>"></td>
    </tr>
    </tr>
   <?php endforeach; ?>
  </table>
 <input type="submit" value="Update" name="update_button">
</form>
</body>
</html>

Open in new window

0
 
stilliardCommented:
Hi,
i havent looked over the code in alot of detail yet,
buti m guesing the $values['status'] var is the value of either 1 or 0 then,
if so, try changing: <input type="checkbox" checked name="update[<?=$values['id']?>]" value="<?=$values['status']?>">
to: <input type="checkbox" name="update[<?=$values['id']?>]" value="1"<?php if($values['status']) echo ' checked'; ?>>
0
 
asaidiAuthor Commented:
Hi stilliard

thank you very much for your answer

ok when i check the value the value is checked and in database is 1 but sometimes  i need to uncheck the value and in database must be replaced to value 0 how i can do this please
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
amrish80Commented:
Hi
Try to use value as name of the field and use a common nae for all the check boxes and then update them for more help u can refer

http://www.homeandlearn.co.uk/php/php4p11.html
http://www.plus2net.com/php_tutorial/array_checkbox.php
0
 
FugasCommented:
The problem using chcekbox is when you unchcek it, you haven't that variable in post request variables. So you need to go trough query and check if a row that has status value=1 isn't set in post variables and then update the row. Even so with rows that have status=0. It's better to use select box or radiobuttons. But Ican write the solution for you later.
0
 
stilliardCommented:

What you could try is loop the update statement the same as you looped the checkboxes in.
Something like this...
<?php 

if (isset($_POST['update'])) {
	
	$query= "SELECT * FROM product  order by type,-seq DESC" ;
	$result=mysql_query($query);
	while ($row = mysql_fetch_array($result)){
		
		$newval = ( $_POST['update'][$i] ) ? '1' : '0';
		$sql = "UPDATE product SET status='{$newval}' WHERE id='{$row['id']}'";
		if (mysql_query($sql)) {
			echo $p;
			$p3=$p;
			if($p==$p3) $p=' ';
		}
		else echo '<p>Error Updating '.mysql_error().'</p>';
	}
}

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.