Solved

checkbox and mysql

Posted on 2010-08-26
6
387 Views
Last Modified: 2013-12-13
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>
0
Comment
Question by:asaidi
6 Comments
 
LVL 6

Expert Comment

by:stilliard
ID: 33537972
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
 

Author Comment

by:asaidi
ID: 33538779
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
 
LVL 5

Expert Comment

by:amrish80
ID: 33539022
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 4

Expert Comment

by:Fugas
ID: 33541379
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
 
LVL 6

Expert Comment

by:stilliard
ID: 33541569

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
 
LVL 4

Accepted Solution

by:
Fugas earned 500 total points
ID: 33541783
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now