cyprus Posted September 13, 2006 Share Posted September 13, 2006 I have been struggling for ages on this. I have a page with 12 checkboxes, which get posted to a page with a dropdown list on. 6 checkboxes choose which table columns the user wants to see in the list. 6 checkboxes select the products he wants to see in the list, products are categorised in 6 groups, so he might choose 1,3,5.How do I get from the checkboxes to building a Select from database line of code?eg Select a,b,c from database where x=1 or x=2 etc. Would be very appreciated if someone could help, my knowledge is small.Thanks Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted September 13, 2006 Share Posted September 13, 2006 You could use if statements...[code=php:0]$sql = "SELECT col1, col2, col3 FROM table WHERE id";if (isset($_GET['checkbox1']))( $sql .= "AND value1 = 'YES'";}if (isset($_GET['checkbox2']))( $sql .= "AND value2 = 'YES'";}$result = mysql_query($sql);[/code]Have you tried something like that?RegardsRich Quote Link to comment Share on other sites More sharing options...
cyprus Posted September 13, 2006 Author Share Posted September 13, 2006 Thanks, no I did not go down that road. I am posting the values through and using session variables. I got as far as:$query = "SELECT Orderdate, " .$op1. " FROM Orders"; There is an $op1,$op2,$op3,$op4,$op5,$op6 which carry the names of six columns to get displayed. There is an $T1,$T2,$T3,$T4,$T5,$T6 which each carry a value of 1 if checked being for the WHERE statement:WHERE $T1=1 or $T2=1 etc.I have been tying myself in knots, especially how to include the "OR" when there maybe only one box selected or its the last of the selections. Its been a real minefield. Thanks Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted September 13, 2006 Share Posted September 13, 2006 SO all the op's are additional columns you can select, and all of the T's are the values for use in the WHERE clause?If so, then just do as suggested, build the query as you go with concatenation.RegardsRich Quote Link to comment Share on other sites More sharing options...
Destruction Posted September 13, 2006 Share Posted September 13, 2006 I wrote a generic function for dynamically creating a WHERE clause from checkboxes or list selections where multiple are allowed.[code]<?phpfunction dynSelect($field, $options = array()){ $list = array(); foreach($options as $option) //Loop through the checkbox selected values { if(!is_numeric($option)) //If not numeric, encase in single quotes for query { $option = "'$option'"; } $list[] = "`$field` = $option"; //Add to array } $list = implode(" OR ", $list); //Join any seperate array sections with OR, if only one value this function will not add the OR. return $list; //Return string list}?>[/code]In the checkbox code you'd put similar to below:[code]<?phpecho '<input type="checkbox" name="options[]" value="$id" />';?>[/code]Then using the function would be such as:[code]<?php$where = dynSelect("id", $_POST['options']);$sql = mysql_query("SELECT * FROM `table` WHERE $where");?>[/code]Hope this helps,Dest Quote Link to comment Share on other sites More sharing options...
Barand Posted September 13, 2006 Share Posted September 13, 2006 is this what you are after[code]<?phpif (isset($_POST['submit'])) { $fields = 'op1'; // default $where = ''; if (count($_POST['showfield'])) $fields = join(',', $_POST['showfield']); if (count($_POST['product'])) { $prodlist = join("','", $_POST['product']); $where = "WHERE product IN ('$prodlist')"; } $sql = "SELECT $fields FROM mytablename $where"; echo "<p>$sql</p>";}?><FORM method='post'>Show fields<br><input type="checkbox" name="showfield[]" value="op1">Op1<br><input type="checkbox" name="showfield[]" value="op2">Op2<br><input type="checkbox" name="showfield[]" value="op3">Op3<br><input type="checkbox" name="showfield[]" value="op4">Op4<br><input type="checkbox" name="showfield[]" value="op5">Op5<br><input type="checkbox" name="showfield[]" value="op6">Op6<br><br>Select products<br> <input type="checkbox" name="product[]" value="product1">product1<br><input type="checkbox" name="product[]" value="product2">product2<br><input type="checkbox" name="product[]" value="product3">product3<br><input type="checkbox" name="product[]" value="product4">product4<br><input type="checkbox" name="product[]" value="product5">product5<br><input type="checkbox" name="product[]" value="product6">product6<br><input type="submit" name="submit" value="Submit"></FORM>[/code] Quote Link to comment Share on other sites More sharing options...
cyprus Posted September 14, 2006 Author Share Posted September 14, 2006 Many Many thanks for all the help folks, I'm astounded by the effort you all make in this forum. I will try all the options out and hopefully get there. Quote Link to comment Share on other sites More sharing options...
cyprus Posted September 14, 2006 Author Share Posted September 14, 2006 I have been playing around with Barand's code, not that I have ignored the others, in fact Destructions code covers my question, but have not had the chance to try it. However I am trying to modify the WHERE statement below. if (count($_POST['product'])) { $prodlist = join("','", $_POST['product']); $where = "WHERE GGPost = ('$prodlist')"; } $sql = "SELECT $fields FROM ORDERS $where"; I am trying to get it to build - Where GGPost=1 or GGPost=2 etc.Any idea how to modify it. Many thanks again to al Quote Link to comment Share on other sites More sharing options...
Barand Posted September 14, 2006 Share Posted September 14, 2006 Why?GGPost IN ('1', '2', '3')is exactly the same as(GGPost = '1') OR (GGPost = '2') OR (GGPost = '3')only it's a lot simpler. Quote Link to comment Share on other sites More sharing options...
cyprus Posted September 14, 2006 Author Share Posted September 14, 2006 Many thanks, you are correct. I put it back and it works. Is there a way to hold checkbox values that are selected after a sumbission without using a table? Also is there a points award on this site for showing gratitude of help. Many thanks again Quote Link to comment Share on other sites More sharing options...
Barand Posted September 14, 2006 Share Posted September 14, 2006 [quote author=cyprus link=topic=107961.msg434421#msg434421 date=1158264900]Is there a way to hold checkbox values that are selected after a sumbission without using a table? [/quote]Not sure what you mean. Can you explain? Quote Link to comment Share on other sites More sharing options...
cyprus Posted September 14, 2006 Author Share Posted September 14, 2006 Thanks. I have a page with checkboxes on, also a dropdown list. The list fills itself from a database query based on the selections of the checkboxes. In order for the list to get filled/updated, the form has to be submitted and returned. However after submission the checkboxes are cleared. Hope that explains it better. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 14, 2006 Share Posted September 14, 2006 You need to check if the value of each c/box was in those values that were posted. If so, add 'checked' inside the option tag. The easiest way to check them all is in a loop[code]Select products<br><?php $products = array ( 1 => 'product1', 2 => 'product2', 3 => 'product3', 4 => 'product4', 5 => 'product5', 6 => 'product6' ); foreach ($products as $id =>$prod) { if ($_POST['product']) { // was value of id in those posted? $chk = in_array($id, $_POST['product']) ? 'checked' : ''; } else $chk = ''; echo "<input type='checkbox' name='product[]' value='$id' $chk>$prod<br>"; }?> [/code] Quote Link to comment Share on other sites More sharing options...
cyprus Posted September 14, 2006 Author Share Posted September 14, 2006 Many thanks, I tried to put it in my existing code, but had no luck. My current code is:<?session_start();?><? if (isset($_POST['submit'])) { $fields = 'op1'; // default $where = ''; if (count($_POST['showfield'])) $fields = join(',', $_POST['showfield']); if (count($_POST['product'])) { $prodlist = join("','", $_POST['product']); $where = "WHERE GGPost IN ('$prodlist')"; } $sql = "SELECT $fields FROM ORDERS $where"; echo "<p>$sql</p>"; }?><FORM method='post'>Included Data<br><input type="checkbox" name="showfield[]" value="OrderNumber">Order Number<br><input type="checkbox" name="showfield[]" value="Orderdate">Order Date<br><br>Select Product<br> <input type="checkbox" name="Product[]" value="1">Digital Betacam<br><input type="checkbox" name="product[]" value="2">Betacam SP<br><input type="checkbox" name="product[]" value="3">DVCPro<br><input type="checkbox" name="product[]" value="4">HDCAM<br><input type="checkbox" name="product[]" value="5">Mini DV<br><input type="submit" name="submit" value="Submit"></FORM>Would be very appreciative if you could advise where to put the code to retain checkbox positions after submit. Many thanks Quote Link to comment Share on other sites More sharing options...
Barand Posted September 14, 2006 Share Posted September 14, 2006 replace[code]<input type="checkbox" name="product[]" value="1">Digital Betacam<input type="checkbox" name="product[]" value="2">Betacam SP<input type="checkbox" name="product[]" value="3">DVCPro<input type="checkbox" name="product[]" value="4">HDCAM<input type="checkbox" name="product[]" value="5">Mini DV[/code]with[code]<?php $products = array ( 1 => 'Digital Betacam', 2 => 'Betacam SP', 3 => 'DVCPro', 4 => 'HDCAM', 5 => 'Mini DV' ); foreach ($products as $id =>$prod) { if ($_POST['product']) { // was value of id in those posted? $chk = in_array($id, $_POST['product']) ? 'checked' : ''; } else $chk = ''; echo "<input type='checkbox' name='product[]' value='$id' $chk>$prod<br>"; }?>[/code] Quote Link to comment Share on other sites More sharing options...
cyprus Posted September 14, 2006 Author Share Posted September 14, 2006 Thanks a million, absolutely perfect. I will modify the other checkboxes in the same way. So glad I moved over to this site with people like you willing to help. The last forum was filled with "Look in the manual", very greatful for all your help. Thanks Quote Link to comment Share on other sites More sharing options...
cyprus Posted September 14, 2006 Author Share Posted September 14, 2006 Absolutely perfect. I have just modified the other checkboxes the same way and all is fine. So pleased I joined this forum, with people around like you out there. Thanks again Quote Link to comment Share on other sites More sharing options...
Barand Posted September 14, 2006 Share Posted September 14, 2006 Glad to have helped 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.