I don't how to confirm multliple records at once by checkbox?

<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="test_mysql1"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>


<form id="form" name="form" method="post" action="accept.php"><table border='1' cellspacing='0' width='612'>
<tr>
<th bgcolor='green'><font color='white'>#</font></th>
<th bgcolor='green'><font color='white'>Id</font></th>
<th bgcolor='green'><font color='white'>Name</font></th>
<th bgcolor='green'><font color='white'>Lastname</font></th>
<th bgcolor='green'><font color='white'>Email</font></th>
</tr>
               
<?php
$i = 0; 

$number = 0;
while($row = mysql_fetch_array($result)){

$number++;

?>
   
<?php
$i++;

if($i%2)
{
$bg_color = "#EEEEEE";
}
else {
$bg_color = "#E0E0E0";
}
?> 
   
   <tr bgcolor='". $bg_color ."'>
   <td><center><Strong><font color='red'><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $rows['id']; ?>">
   </font></Strong>
   </center></td>
   <td><center><Strong><font color='red'><?php echo $row['id']; ?></font></Strong></center></td>
  <td><center><Strong><?php echo $row['name']; ?></font></Strong></center></td>
  <td><center><Strong><?php echo $row['lastname']; ?></Strong></center></td>
  <td><center><Strong><?php echo $row['email'];  ?></Strong></center></td>
  </tr>
<?php } ?>
            </table>
            <div class="select"><strong>Other Pages: </strong>
               <select>
                  <option>1</option>
               </select>
           </div>
             
               <label>
                 <input name="accept" type="submit" id="accept" value="Accept">


                </label></form>

accept.php

<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name1="test_mysql1"; // Table 1 name
$tbl_name2="test_mysql2"; // Table 2 name  
$tbl_name3="test_mysql3"; // Table 2 name


// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");



      if(isset($_POST['accept'])) {
   
      foreach($_POST['checkbox'] as $id){

         $usersid = mysql_query("SELECT * FROM ".$tbl_name1." WHERE id=".$id."");
         $row = mysql_fetch_array($usersid);
         $username = ($row['username']);  


         $users = mysql_query("SELECT * FROM ".$tbl_name2." WHERE users =".$username."");
         $row1 = mysql_fetch_array($users);
         $section = ($row1['section']); 

         mysql_query("UPDATE ".$tbl_name3." SET Status = 'Complete' WHERE section =".$section.""); 

 


    }
 
          header('Location: admin.php');
          
      
    }

Recommended Answers

All 21 Replies

Try to state your problem and show the relevant parts of your code instead of posting all your code and leaving us to find out what you are looking for.

I tick the multiple checkboxes to update a few records in database via three tables at once. My problem is I don't how to write the code. the code below is not correct! Can anyone help me to rewrite it?

if(isset($_POST['accept'])) {       
foreach($_POST['checkbox'] as $id){          
$usersid = mysql_query("SELECT * FROM ".$tbl_name1." WHERE  id=".$id."");    
$row = mysql_fetch_array($usersid);         
$username = ($row['username']); 

            
$users = mysql_query("SELECT * FROM ".$tbl_name2." WHERE users =".$username."");         
$row1 = mysql_fetch_array($users);         
$section = ($row1['section']);
           
mysql_query("UPDATE ".$tbl_name3." SET Status = 'Complete' WHERE section=".$section."");
commented: Leaves us standing in the rain guessing what he wants. +0

Don't leave us guessing. If the code is not correct, what's the error message or the unsuspected behaviour?
Do you really have a table named "users"?
If users and section are text/char fields, their value have to be enclosed by quotes in the WHERE clauses (which is good practice anyway):

$users = mysql_query("SELECT * FROM ".$tbl_name2." WHERE users ='".$username."'"); 
mysql_query("UPDATE ".$tbl_name3." SET Status = 'Complete' WHERE section='".$section."'");

No, i don't really use the users and section. It is just an example.

After rewriting the code. it is not work!

I mean i don't know the method to write the code! The incorrect code only show the ideas what i want to do.

Member Avatar for diafol

Have to say I'm with smartscheff here, I'm totally bewildered at what you're trying to achieve. You're using an example which is not what you want to do. Why?

If you need to update a few tables at the same times which are linked with common fields ('primary -> foreign keys' in strict relationships), you need to look up relationships and the JOIN syntax in the mysql online manual.

Sorry!

I click two checkboxes and pressing "accept" button. It should be echo two results, but one result is shown!

How to echos two resutls?

if(isset($_POST['accept'])) {       
foreach($_POST['checkbox'] as $id){          
$usersid = mysql_query("SELECT * FROM ".$tbl_name1." WHERE  id=".$id."");    
$row = mysql_fetch_array($usersid);         
$username = ($row['username']); 

            
$users = mysql_query("SELECT * FROM ".$tbl_name2." WHERE users =".$username."");         
$row1 = mysql_fetch_array($users);         
$section = ($row1['section']);

echo"$section":
Member Avatar for diafol

YOu wan to create a relational model using integers in this case.
Also, as I mentioned, use the JOIN syntax to create just one sql statement.

s below code possible? Thanks!

$section = mysql_query("SELECT section FROM ".$tbl_name2." WHERE users IN ("SELECT username FROM ".$tbl_name1." WHERE id=".$id."");

echo"$section":
Member Avatar for diafol

It's certainly possible, but slooow. Use the JOIN syntax:

SELECT tbl2.section, tbl1.username FROM tbl2
INNER JOIN tbl1 ON tbl1.username = tbl2.users
WHERE tbl1.username = '$id'

Not successful ! It echo "Resource #id 5" instead of "1 5"
t

Member Avatar for diafol

Need to see your code and where it's going wrong.

Not successful ! It echo "Resource id #5" instead of "1 5"
t

if(isset($_POST['accept'])) {
   
foreach($_POST['checkbox'] as $id){

$section = mysql_query("SELECT tbl2.section, tbl1.username FROM tbl2
INNER JOIN tbl1 ON tbl1.username = tbl2.users
WHERE tbl1.username = ".$id."");

}

echo"$section":

}
Member Avatar for diafol

You can't echo $section. You have to extract the data via mysql_fetch_array() and then access the array. Look it up in the online manual.

Not work! I click two checkboxes but only show one result. Any wrong in my code!

if(isset($_POST['accept'])) {
   
foreach($_POST['checkbox'] as $id){

$section = mysql_query("SELECT tbl2.section, tbl1.username FROM tbl2
INNER JOIN tbl1 ON tbl1.username = tbl2.users
WHERE tbl1.username = ".$id."");


$row = mysql_fetch_array($section);
$session1=($row['session']);

}

echo"$section1":

}

Each checkbox needs a unique name by which it is identified in the $_POST array. If all checkboxes have the name "checkbox", you will only get the value of one of them.

How to rewrite the code?

Member Avatar for diafol

No need, give the checkboxes the same name, but with an array:

<input type="checkbox" id="checkbox_<?php echo $rows['id']; ?>" name="checkbox[]" value="<?php echo $rows['id']; ?>" />

Pretty much what you've done.

$myarray = (array) $_POST['checkbox']; //will produce an array e.g. 4,7,8,9
$in_list = implode(",", $myarray);

You can then use the IN syntax to extract all the relevant records in the DB. No need for a loop and endless number of SQL calls.

...WHERE id IN ($in_list)...

Sorry. I am so confused now! I still dont understand!

I still dont know which codes should be erased and which should be added? How about JOIN synxt? How about the code "foreach($_POST as $id)"?

Member Avatar for diafol

OK, I haven't tested this totally, but I tried the SQL on a similar setup and it worked:

if(isset($_POST['accept'])) {
        $checks = (array) $_POST['checkbox']; 
        $in_list = implode(",",$checks);
	$r = mysql_query("SELECT section.tbl2, username.tbl1 FROM tbl1 INNER JOIN tbl2 ON username.tbl = users.tbl2 WHERE id.tbl1 IN ($in_list)");
	while($d = mysql_fetch_array($r)){
		$sections[] = $d['section'];
	}
	$in_sections = implode(",",$sections);
	$r = mysql_query(UPDATE tbl3 SET Status = 'Complete' WHERE section IN ($in_sections));
}

This way you only have to make 2 sql queries as opposed to, well, a lot.

JOINS and IN save a lot. They may be slower individually, but they're quicker than running loads of statements.

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\deleteApplication4.php on line 28

Thanks you for your continuous support! It is really work ! Thank you very much!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.