tgavin Posted September 12, 2006 Share Posted September 12, 2006 This query returns the correct information from within mysql:[code=php:0]$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());[/code]<br /><br />Then what I do is loop through a table that contains mailing list names and descriptions.[code=php:0]$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>";[/code]<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 />[code]TABLE subscribersid email26 abc@aol.comTABLE list_namesln_id ln_name ln_description1 lista blah, blah2 listb blah, blah3 listc blah, blah4 listd blah, blah5 liste blah, blahTABLE list_subscribersls_id ls_list_id ls_sub_id1 1 262 4 26[/code]<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. Quote Link to comment Share on other sites More sharing options...
tgavin Posted September 12, 2006 Author Share Posted September 12, 2006 Here's the full code if needed<br /><br />[code=php:0]$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>[/code] Quote Link to comment Share on other sites More sharing options...
tgavin Posted September 18, 2006 Author Share Posted September 18, 2006 Am I asking for something that can't be done? Is there some other way to do this then? Quote Link to comment Share on other sites More sharing options...
craygo Posted September 18, 2006 Share Posted September 18, 2006 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted September 18, 2006 Share Posted September 18, 2006 This will do it[code]<?phpinclude '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/>";}?>[/code] Quote Link to comment Share on other sites More sharing options...
tgavin Posted September 19, 2006 Author Share Posted September 19, 2006 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 />[code]<?phpif(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";}?>[/code]<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!!! Quote Link to comment Share on other sites More sharing options...
Barand Posted September 19, 2006 Share Posted September 19, 2006 When processing you need to query the list_names table and process each ln_id in that table(pseudocode)[code]SELECT ln_id FROM list_namesfor each ln_id if ln_id in the $data array insert a record else delete record end ifend for each[/code] Quote Link to comment Share on other sites More sharing options...
tgavin Posted September 19, 2006 Author Share Posted September 19, 2006 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 20, 2006 Share Posted September 20, 2006 try[code]<?phpinclude '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 loopecho "<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>";?>[/code] Quote Link to comment Share on other sites More sharing options...
craygo Posted September 20, 2006 Share Posted September 20, 2006 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.[code]<?phpif(isset($_POST['checkbox'])){$checkbox = $_POST['checkbox'];} else {$checkbox = '0';}?>[/code]Ray Quote Link to comment Share on other sites More sharing options...
tgavin Posted September 20, 2006 Author Share Posted September 20, 2006 Thanks to both of you! Barand, Again, I REALLY appreciate your patience and help!!!Here's what finally worked[code]<?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()); }}?>[/code] Quote Link to comment Share on other sites More sharing options...
Barand Posted September 20, 2006 Share Posted September 20, 2006 Are you sure that using this query works (line 2)? [code]$res = mysql_query("SELECT ls_list_id FROM list_subscribers WHERE ls_sub_id = '$id'");[/code]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[code]$res = mysql_query("SELECT ln_id FROM list_names");[/code] Quote Link to comment Share on other sites More sharing options...
tgavin Posted September 20, 2006 Author Share Posted September 20, 2006 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:[code]Warning: in_array() [function.in-array]: Wrong datatype for second argument in /path/to/file.php on line 52<?php// line 52if(in_array($ln_id, $data)) {?>[/code]Here's what I have in the script[code]<?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()); }}?>[/code] Quote Link to comment Share on other sites More sharing options...
Barand Posted September 20, 2006 Share Posted September 20, 2006 Change $data = $_POST['ln_id'];to$data = isset($_POST['ln_id']) ? $_POST['ln_id'] : array(); Quote Link to comment Share on other sites More sharing options...
Barand Posted September 20, 2006 Share Posted September 20, 2006 BTW, have you got your own custom function "strip()" ? Quote Link to comment Share on other sites More sharing options...
tgavin Posted September 20, 2006 Author Share Posted September 20, 2006 [quote author=Barand link=topic=107845.msg438287#msg438287 date=1158782593]Change $data = $_POST['ln_id'];to$data = isset($_POST['ln_id']) ? $_POST['ln_id'] : array();[/quote]That did the trick :) What's does the "?" do?[quote author=Barand link=topic=107845.msg438289#msg438289 date=1158782764]BTW, have you got your own custom function "strip()" ?[/quote]Yep.[code]<?php/* FILTER DB INPUT */// use this function in an INSERT or UPDATE mysql_queryfunction 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;}?>[/code] Quote Link to comment Share on other sites More sharing options...
Barand Posted September 20, 2006 Share Posted September 20, 2006 [code]$data = isset($_POST['ln_id']) ? $_POST['ln_id'] : array();[/code]is a shorthand way of coding[code]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} [/code] Generic case[code]$var = <condition> ? <value if condition true> : <value if condition false>;[/code] Quote Link to comment Share on other sites More sharing options...
tgavin Posted September 20, 2006 Author Share Posted September 20, 2006 Now THAT'S a handy piece of knowledge!Thanks! Quote Link to comment Share on other sites More sharing options...
tgavin Posted September 21, 2006 Author Share Posted September 21, 2006 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)) { "[code]<?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 tableecho '<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>';}?>[/code] Quote Link to comment Share on other sites More sharing options...
Barand Posted September 21, 2006 Share Posted September 21, 2006 What are you trying to do? This ?[code]<?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 neededif ($i % 4 != 0) { while ($i++ % 4) echo "<td> </td>"; echo "</tr>\n";}echo "</table>\n";?>[/code] Quote Link to comment Share on other sites More sharing options...
tgavin Posted September 21, 2006 Author Share Posted September 21, 2006 Thanks again! :) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.