Jump to content


Photo

loop through checkboxes, tick off selected


  • Please log in to reply
20 replies to this topic

#1 tgavin

tgavin
  • Members
  • PipPipPip
  • Advanced Member
  • 176 posts
  • LocationNaples, FL

Posted 12 September 2006 - 11:13 PM

This query returns the correct information from within mysql:
$sql = mysql_query("SELECT ln_id,ln_name,ls_list_id,ls_sub_id FROM list_names JOIN list_subscribers ON list_names.ln_id = list_subscribers.ls_list_id LEFT OUTER JOIN subscribers ON list_subscribers.ls_sub_id = subscribers.id WHERE id='".$id."'") or die(mysql_error());
<br /><br />Then what I do is loop through a table that contains mailing list names and descriptions.
$sql = mysql_query("SELECT * FROM list_names") or die(mysql_error());
$row_ml_names = mysql_fetch_assoc($sql);
echo "<table>
<tr>";
do {
	echo "
	<td>
		<input type=\"checkbox\" name=\"ln_id[]\" value=\"".$row_ml_names['ln_id']."\" class=\"checkbox\" />
		<strong>".$row_ml_names['ln_name']."</strong><br />
		".$row_ml_names['ln_description']."
	</td>";
	if(!isset($nested_list_names)) {
		$nested_list_names= 1;
	}
	if(isset($row_ml_names) && is_array($row_ml_names) && $nested_list_names++ % 2==0) {
		echo "</tr><tr>";
	}
} while ($row_ml_names);
echo "</tr>
</table>";
<br /><br />Now the problem: For each list that the subscriber belongs to, I need to have the checkbox ticked. I've been racking my brain with this but can't get it to work!<br /><br />
TABLE subscribers
id   email
26   abc@aol.com


TABLE list_names
ln_id  ln_name   ln_description
1      lista     blah, blah
2      listb     blah, blah
3      listc     blah, blah
4      listd     blah, blah
5      liste     blah, blah


TABLE list_subscribers
ls_id  ls_list_id  ls_sub_id
1      1           26
2      4           26
<br /><br />So, the subscriber with the id# of 26 is subscribed to lists 1 and 4.
In the page, I want to echo all 5 lists (as checkboxes), but only check off the boxes that the subscriber belongs to. In this case lists 1 and 4.
A noble spirit embiggens the smallest man.
- Jebediah Springfield

#2 tgavin

tgavin
  • Members
  • PipPipPip
  • Advanced Member
  • 176 posts
  • LocationNaples, FL

Posted 12 September 2006 - 11:13 PM

Here's the full code if needed<br /><br />
$id = $_GET['id'];
$list_names_name = mysql_query("SELECT * FROM list_names") or die(mysql_error());
$row_ml_names = mysql_fetch_assoc($list_names_name);
$totalRows_ml_names = mysql_num_rows($list_names_name);

$sql = mysql_query("SELECT id,email,bounce_count FROM subscribers WHERE id='".$id."'") or die(mysql_error());
$row_subscribers = mysql_fetch_assoc($sql);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Details for <?php echo $row_subscribers['email']; ?></title>
</head>
<body>
<table style="width:100%">
	<tr>
		<td align="left" valign="top"><label>Mailing Lists</label></td>
		<td align="left" valign="top">
			<table style="width:100%">
				<tr>
				<?php $sql = mysql_query("SELECT ln_id,ln_name,ls_list_id,ls_sub_id FROM list_names JOIN list_subscribers ON list_names.ln_id = list_subscribers.ls_list_id LEFT OUTER JOIN subscribers ON list_subscribers.ls_sub_id = subscribers.id WHERE id='".$id."'") or die(mysql_error()); ?>
				<?php do { // horizontal looper ?>
					<td align="left" valign="top" width="50%" style="padding:0 4px 0 4px;">
						<input type="checkbox" name="ln_id[]" value="<?php echo $row_ml_names['ln_id']; ?>" class="checkbox" 
						<?php while($row = mysql_fetch_array($sql)) {
							if($row['ln_id'] == $row['ls_list_id']) { echo ' checked=\"checked\" '; }
						} ?> />
						<strong><?php echo $row_ml_names['ln_name']; ?></strong><br />
						<?php echo $row_ml_names['ln_description']; ?>
					</td>
					<?php $row_ml_names = mysql_fetch_assoc($list_names_name);
					if (!isset($nested_list_names)) {
						$nested_list_names= 1;
						}
					if (isset($row_ml_names) && is_array($row_ml_names) && $nested_list_names++ % 2==0) {
						echo "</tr><tr>";
						}
				} while ($row_ml_names); //end horizontal looper ?>
				</tr>
			</table>
		</td>
	</tr>
</table>
</body>
</html>

A noble spirit embiggens the smallest man.
- Jebediah Springfield

#3 tgavin

tgavin
  • Members
  • PipPipPip
  • Advanced Member
  • 176 posts
  • LocationNaples, FL

Posted 18 September 2006 - 04:18 PM

Am I asking for something that can't be done? Is there some other way to do this then?
A noble spirit embiggens the smallest man.
- Jebediah Springfield

#4 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 18 September 2006 - 05:23 PM

do you have a set number of mailing lists to go by?? if you do what table is it in?? give me some table definitions to help you out.

Ray

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 18 September 2006 - 06:09 PM

This will do it
<?php
include 'db2.php'; // db connection

$subscriber_id = 26;
$sql = "SELECT n.ln_id, n.ln_name, s.ls_list_id
        FROM list_names n
        LEFT JOIN list_subscribers s
          ON n.ln_id = s.ls_list_id AND s.ls_sub_id = '$subscriber_id'";
$res = mysql_query($sql) or die(mysql_error());
while (list($nid, $name, $id)=mysql_fetch_row($res)) {
    $chk = $id ? 'checked' : '';  // ls_list_id will be NULL if there was no matching record in subscriber table
    echo "<input type='checkbox' name='cbox[]' value='$nid' $chk> $name<br/>";
}
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 tgavin

tgavin
  • Members
  • PipPipPip
  • Advanced Member
  • 176 posts
  • LocationNaples, FL

Posted 19 September 2006 - 06:33 PM

Barand: Awesome! That worked perfectly. You have no idea how much I appreciate that! :)

I hope I'm not overstepping, but I have one more question. For each checkbox selected, this user is added to the list_subscribers table. This is working perfectly.<br />
<?php
if(isset($_POST['ln_id'])) {
	// subscribe
	$data = $_POST['ln_id'];
	foreach($data as $ls_list_id) {
		// check to see if already subscribed to this list
		$sql = mysql_query("SELECT ls_list_id FROM list_subscribers WHERE ls_list_id = '".$ls_list_id."' AND ls_sub_id = '".$id."'") or die(mysql_error());
		$result_check = mysql_num_rows($sql);
		
		// if no matches, subscribe to the selected list
		if($result_check == 0) {
			$list = mysql_query("INSERT INTO list_subscribers (ls_id,ls_list_id,ls_sub_id) VALUES ('','".strip($ls_list_id)."','".strip($id)."')") or die(mysql_error());
		}
	}
}

// the checkbox loop
	$sql = "SELECT n.ln_id, n.ln_name, s.ls_list_id FROM list_names n LEFT JOIN list_subscribers s ON n.ln_id = s.ls_list_id AND s.ls_sub_id = '".$id."'";
	$res = mysql_query($sql) or die(mysql_error());
	while (list($nid, $name, $id)=mysql_fetch_row($res)) {
	$chk = $id ? "checked=\"checked\"" : ""; // ls_list_id will be NULL if there was no matching record in subscriber table
	echo "<input type=\"checkbox\" name=\"ln_id[]\" value=\"".$nid."\" ".$chk."> ".$name."<br />\n";
}
?>
<br />However, I'm having trouble doing the opposite. For each checkbox NOT selected, then the user gets removed from the list_subscribers table.<br /><br />Thanks again!!!
A noble spirit embiggens the smallest man.
- Jebediah Springfield

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 19 September 2006 - 06:57 PM

When processing you need to query the list_names table and process each ln_id in that table

(pseudocode)
SELECT ln_id FROM list_names
for each ln_id
       if ln_id in the $data array
            insert a record
       else
            delete record
       end if
end for each

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 tgavin

tgavin
  • Members
  • PipPipPip
  • Advanced Member
  • 176 posts
  • LocationNaples, FL

Posted 19 September 2006 - 10:02 PM

I must be making this too complicated. So far, everything I've tried isn't working. I think my biggest problem is that I still haven't completely grasped arrays and how to use them.
A noble spirit embiggens the smallest man.
- Jebediah Springfield

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 20 September 2006 - 02:03 AM

try
<?php
include 'db2.php';
$subid = $_REQUEST['subscriber'];

if(isset($_POST['submit'])) {
	// subscribe
	$data = $_POST['ln_id'];
    $res = mysql_query("SELECT ln_id FROM list_names");
    while (list($ln_id) = mysql_fetch_row($res)) {
	    if (in_array($ln_id, $data)) {
		    // check to see if already subscribed to this list
		    $sql = mysql_query("SELECT ls_list_id FROM list_subscribers 
            WHERE ls_list_id = '$ln_id' 
            AND ls_sub_id = '$subid'") 
            or die(mysql_error());
		    $result_check = mysql_num_rows($sql);
		    
		    // if no matches, subscribe to the selected list
		    if($result_check == 0) {
			    $list = mysql_query("INSERT INTO list_subscribers (ls_id,ls_list_id,ls_sub_id) 
                VALUES (null,'$ln_id','$subid')") or die(mysql_error());
		    }
        }
        else {
            mysql_query ("DELETE FROM list_subscribers 
                    WHERE ls_sub_id = '$subid' 
                    AND ls_list_id = '$ln_id'");
        }
	}
}

// the checkbox loop
echo "<FORM method='POST'>\n";
	$sql = "SELECT n.ln_id, n.ln_name, s.ls_list_id FROM list_names n LEFT JOIN list_subscribers s ON n.ln_id = s.ls_list_id AND s.ls_sub_id = '".$subid."'";
	$res = mysql_query($sql) or die(mysql_error());
	while (list($nid, $name, $id)=mysql_fetch_row($res)) {
	$chk = $id ? "checked=\"checked\"" : ""; // ls_list_id will be NULL if there was no matching record in subscriber table
	echo "<input type=\"checkbox\" name=\"ln_id[]\" value=\"".$nid."\" ".$chk."> ".$name."<br />\n";
}
echo "<input type='hidden' name='subscriber' value='$subid'>";
echo "<input type='submit' name='submit' value='Submit'>
</FORM>";
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 20 September 2006 - 01:38 PM

When a checkbox is unchecked it does not get passed from the form. What you have to do is put a few if statements on the form processing page. I sucks when you have alot of checkboxes. You could probably loop through them by quering the database if you have a lot of them.

<?php
if(isset($_POST['checkbox'])){
$checkbox = $_POST['checkbox'];
} else {
$checkbox = '0';
}?>

Ray



#11 tgavin

tgavin
  • Members
  • PipPipPip
  • Advanced Member
  • 176 posts
  • LocationNaples, FL

Posted 20 September 2006 - 07:19 PM

Thanks to both of you! Barand, Again, I REALLY appreciate your patience and help!!!

Here's what finally worked
<?php
$data = $_POST['ln_id'];
$res = mysql_query("SELECT ls_list_id FROM list_subscribers WHERE ls_sub_id = '$id'");
while(list($ln_id) = mysql_fetch_row($res)) {
	if(in_array($ln_id, $data)) {
		foreach($data as $ls_list_id) {
			// check to see if already subscribed to this list
			$sql = mysql_query("SELECT ls_list_id FROM list_subscribers WHERE ls_list_id = '".$ls_list_id."' AND ls_sub_id = '".$id."'") or die(mysql_error());
			$result_check = mysql_num_rows($sql);
			
			// if no matches, subscribe to the new list
			if($result_check == 0) {
				$list = mysql_query("INSERT INTO list_subscribers (ls_id,ls_list_id,ls_sub_id) VALUES ('','".strip($ls_list_id)."','".strip($id)."')") or die(mysql_error());
			}
		}
	} else {
		$delete = mysql_query("DELETE FROM list_subscribers WHERE ls_list_id = '".$ln_id."' AND ls_sub_id = '".$id."' ") or die(mysql_error());
	}
}
?>

A noble spirit embiggens the smallest man.
- Jebediah Springfield

#12 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 20 September 2006 - 07:38 PM

Are you sure that using this query works (line 2)?
$res = mysql_query("SELECT ls_list_id FROM list_subscribers WHERE ls_sub_id = '$id'");
You need to loop through all posible values of $ln_id, and not just those they already have, otherwise it won't add new ones. Thats why I used

$res = mysql_query("SELECT ln_id FROM list_names");

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#13 tgavin

tgavin
  • Members
  • PipPipPip
  • Advanced Member
  • 176 posts
  • LocationNaples, FL

Posted 20 September 2006 - 07:55 PM

You're right. I don't know how I posted that??? I jumped the gun anyway :(
It works the way it's supposed to, *except* that if the user is already subscribed to a list, and then I unsubscribe him - so that ALL checkboxes are empty - then click submit, I get the following error:
Warning: in_array() [function.in-array]: Wrong datatype for second argument in /path/to/file.php on line 52

<?php
// line 52
if(in_array($ln_id, $data)) {
?>
Here's what I have in the script
<?php
$data = $_POST['ln_id'];
$res = mysql_query("SELECT ln_id FROM list_names");
while(list($ln_id) = mysql_fetch_row($res)) {
	if(in_array($ln_id, $data)) {
		foreach($data as $ls_list_id) {
			// check to see if already subscribed to this list
			$sql = mysql_query("SELECT ls_list_id FROM list_subscribers WHERE ls_list_id = '".$ls_list_id."' AND ls_sub_id = '".$id."'") or die(mysql_error());
			$result_check = mysql_num_rows($sql);
			
			// if no matches, subscribe to the new list
			if($result_check == 0) {
				$list = mysql_query("INSERT INTO list_subscribers (ls_id,ls_list_id,ls_sub_id) VALUES ('','".strip($ls_list_id)."','".strip($id)."')") or die(mysql_error());
			}
		}
	} else {
		$delete = mysql_query("DELETE FROM list_subscribers WHERE ls_list_id = '".$ln_id."' AND ls_sub_id = '".$id."' ") or die(mysql_error());
	}
}
?>

A noble spirit embiggens the smallest man.
- Jebediah Springfield

#14 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 20 September 2006 - 08:03 PM

Change

$data = $_POST['ln_id'];

to

$data = isset($_POST['ln_id']) ? $_POST['ln_id'] : array();
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#15 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 20 September 2006 - 08:06 PM

BTW, have you got your own custom function "strip()" ?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#16 tgavin

tgavin
  • Members
  • PipPipPip
  • Advanced Member
  • 176 posts
  • LocationNaples, FL

Posted 20 September 2006 - 08:16 PM

Change

$data = $_POST['ln_id'];

to

$data = isset($_POST['ln_id']) ? $_POST['ln_id'] : array();

That did the trick :) What's does the "?" do?

BTW, have you got your own custom function "strip()" ?

Yep.
<?php
/* FILTER DB INPUT */
// use this function in an INSERT or UPDATE mysql_query
function strip($value) {
	// strip slashes
	if(get_magic_quotes_gpc()) {
		$value = stripslashes($value);
		$value = mysql_real_escape_string($value);
	}
	
	// quote if not integer
	if(!get_magic_quotes_gpc()) {
		if(!is_numeric($value) || $value[0] == '0') {
			$value = mysql_real_escape_string($value);
		}
	}
	return $value;
}
?>

A noble spirit embiggens the smallest man.
- Jebediah Springfield

#17 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 20 September 2006 - 08:23 PM

$data = isset($_POST['ln_id']) ? $_POST['ln_id'] : array();

is a shorthand way of coding
if (isset($_POST['ln_id'])) {             // were any checkboxes set as checked?

        $data = $_POST['ln_id'];        // set data to the posted array of checkbox values

}
else {                                         // no checked boxes

       $data = array();                    // set $data to be an empty array
}


Generic case
$var = <condition> ? <value if condition true> : <value if condition false>;

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#18 tgavin

tgavin
  • Members
  • PipPipPip
  • Advanced Member
  • 176 posts
  • LocationNaples, FL

Posted 20 September 2006 - 11:02 PM

Now THAT'S a handy piece of knowledge!

Thanks!
A noble spirit embiggens the smallest man.
- Jebediah Springfield

#19 tgavin

tgavin
  • Members
  • PipPipPip
  • Advanced Member
  • 176 posts
  • LocationNaples, FL

Posted 21 September 2006 - 01:17 AM

After all the help you've given me, I hate asking another question. But I have to. I've been working on this for 30 mins and can't get the loop to work. No matter what I try, I keep getting sql errors on "while($rows = mysql_fetch_assoc($sql)) { "
<?php
$sql = "SELECT n.ln_id, n.ln_name, s.ls_list_id FROM list_names n LEFT JOIN list_subscribers s ON n.ln_id = s.ls_list_id AND s.ls_sub_id = '".$id."'";
$res = mysql_query($sql) or die(mysql_error());
while(list($nid, $name, $id)=mysql_fetch_row($res)) {
$chk = $id ? "checked=\"checked\"" : ""; // ls_list_id will be NULL if there was no matching record in subscriber table

echo '<table width="100%" border="1" cellspacing="0" cellpadding="0">';
$i=0; 
while($rows = mysql_fetch_assoc($sql)) { // keep getting errors here
	if(($i++ % 4)==0) { echo '</tr><tr>'; } 
	echo '<td><input type="checkbox" name="ln_id[]" value='.$nid.' '.$chk.'> '.$name.'<br />\n</td>';    
} 
echo '</tr>'; 
echo '</table>';
}
?>

A noble spirit embiggens the smallest man.
- Jebediah Springfield

#20 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 21 September 2006 - 07:24 AM

What are you trying to do? This ?

<?php

$sql = "SELECT n.ln_id, n.ln_name, s.ls_list_id FROM list_names n LEFT JOIN list_subscribers s ON n.ln_id = s.ls_list_id AND s.ls_sub_id = '".$subid."'";
$res = mysql_query($sql) or die(mysql_error());

echo '<table width="100%" border="1" cellspacing="0" cellpadding="0">';
$i=0;
while (list($nid, $name, $id)=mysql_fetch_row($res)) {
    if ($i % 4 == 0) echo '<tr>';
    $chk = $id ? "checked=\"checked\"" : ""; // ls_list_id will be NULL if there was no matching record in subscriber table
    echo "<td><input type='checkbox' name='ln_id[]' value='$nid' $chk> $name</td>\n";
    $i++ ;
    if ($i % 4 == 0) echo '</tr>';    
}
// finish last row if needed
if ($i % 4 != 0) {
   while ($i++ % 4) echo "<td>&nbsp;</td>";
   echo "</tr>\n";
}

echo "</table>\n";

?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users