stevew Posted July 8, 2012 Share Posted July 8, 2012 <?php $honda = $_POST["honda"]; $toyota = $_POST["toyota"]; $mazda = $_POST["mazda"]; ?> <form name="myform" action="checkboxtest.php"> Honda:<input type="checkbox" value="Honda" name="honda[]"><br /> Toyota:<input type="checkbox" value="Toyota" name="toyota[]"><br /> Mazda:<input type="checkbox" value="Mazda" name="mazda[]"><br /> <input type="submit" value="submit" name="submit"> </form> Table structure: id........................................honda.........toyota............mazda 1.........................................yes..............no.................no 2.........................................no..............yes.................no 3.........................................yes..............no.................yes 4.........................................yes..............yes.................yes 5.........................................no...............no.................no etc I am trying to figure out how to create the SELECT statement. I have been piecing together information but cannot get it to work properly. (basically) $result ($honda, $toyota, $mazda)= mysql_query ("SELECT * FROM tbl WHERE (honda,toyota,mazda) LIKE 'yes%' ") ; Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/ Share on other sites More sharing options...
Pikachu2000 Posted July 8, 2012 Share Posted July 8, 2012 What exactly are you wanting to do, and what's the "big picture" of what's happening here? Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360093 Share on other sites More sharing options...
hakimserwa Posted July 8, 2012 Share Posted July 8, 2012 <?php $honda = $_POST["honda"]; $toyota = $_POST["toyota"]; $mazda = $_POST["mazda"]; ?> <form name="myform" action="checkboxtest.php"> Honda:<input type="checkbox" value="Honda" name="honda[]"><br /> Toyota:<input type="checkbox" value="Toyota" name="toyota[]"><br /> Mazda:<input type="checkbox" value="Mazda" name="mazda[]"><br /> <input type="submit" value="submit" name="submit"> </form> Table structure: id........................................honda.........toyota............mazda 1.........................................yes..............no.................no 2.........................................no..............yes.................no 3.........................................yes..............no.................yes 4.........................................yes..............yes.................yes 5.........................................no...............no.................no etc I am trying to figure out how to create the SELECT statement. I have been piecing together information but cannot get it to work properly. (basically) $result ($honda, $toyota, $mazda)= mysql_query ("SELECT * FROM tbl WHERE (honda,toyota,mazda) LIKE 'yes%' ") ; this is exactly what you asked here. http://forums.phpfreaks.com/index.php?topic=362176.0 and some ideas were given if you never checked but just try to explain what you want to archieve may be we shall then give a better i dea. Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360094 Share on other sites More sharing options...
stevew Posted July 8, 2012 Author Share Posted July 8, 2012 this is exactly what you asked here. http://forums.phpfreaks.com/index.php?topic=362176.0 and some ideas were given if you never checked but just try to explain what you want to archieve may be we shall then give a better i dea. Same idea but with checkboxes so more suitable for multiple option searches. Based on the table posted above: A search for honda toyota would be: id4 A search for honda would be: id1,id3,id4 A search for honda mazda would be: id3,id4 etc. $result ($honda, $toyota, $mazda)= mysql_query ("SELECT * FROM tbl WHERE (honda,toyota,mazda) LIKE 'yes%' ") ; ?? Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360139 Share on other sites More sharing options...
Barand Posted July 8, 2012 Share Posted July 8, 2012 A search for honda toyota would be: ... WHERE honda='yes' AND toyota='yes' Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360155 Share on other sites More sharing options...
stevew Posted July 9, 2012 Author Share Posted July 9, 2012 What exactly are you wanting to do, and what's the "big picture" of what's happening here? this is exactly what you asked here. http://forums.phpfreaks.com/index.php?topic=362176.0 and some ideas were given if you never checked but just try to explain what you want to archieve may be we shall then give a better i dea. Sorry about that... I was pressed for time this morning and was not clear. I have the radio buttons functioning now thanks to the suggestions. I will try to explain it better here. Checkboxes If I am looking to buy a house...and the only thing that really matters to me is that it has a porch and a pool...all other criteria is secondary...so naturally I will begin my search by clicking the check boxes (on the advanced search page) for porch and pool. There may be other checkboxes say for tennis courts...indoor garage...etc...but these are not important to me so I leave them blank. So I click submit. As long as a house contains both a porch and a pool, it will be listed in the results...regardless of all other criteria. example: A house has a porch, pool, tennis courts, no indoor garage: listed A house has a porch, pool, no tennis courts, indoor garage: listed A house has a porch, pool, tennis courts, indoor garage: listed A house has a porch, pool, no tennis courts, no indoor garage: listed What do I need to do with the SELECT statement to accomplish this? Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360195 Share on other sites More sharing options...
Drummin Posted July 9, 2012 Share Posted July 9, 2012 Part of the problem may lie in the fact that you're posting these values as array's by adding [] after the name, eg name="honda[]". If the form fields are not looping through a list of records then just use a singular name, name="honda" Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360197 Share on other sites More sharing options...
Pikachu2000 Posted July 9, 2012 Share Posted July 9, 2012 For the long term you should google "database normalization" but to make this work now, what you need to do is create the query string dynamically, only adding the selected criteria to the WHERE clause. Set all of the checkboxes up as a single form array. Since unchecked checkboxes aren't even sent by a form, if you use the values from the database fields, you should be able to format the values from the $_POST array in a loop then implode the subarray with AND. <?php $_POST['checkboxes'] = array('pool', 'patio', 'fireplace'); // simulated data from form $criteria = array(); foreach( $_POST['checkboxes'] as $val ) { $criteria[] .= "$val = 'yes'"; } $criterion = empty($criteria) ? '' : ' WHERE ' . implode( ' AND ', $criteria ); $query = "SELECT fields FROM table" . $criterion; echo $query; // RETURNS: SELECT fields FROM table WHERE pool = 'yes' AND patio = 'yes' AND fireplace = 'yes' Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360200 Share on other sites More sharing options...
Drummin Posted July 9, 2012 Share Posted July 9, 2012 Not as nice as Pikachu2000's solution, but as I was typing out while he was posting, I thought I'd post it anyway. <?php //add DB connection include("access.php"); //////////////////// if (isset($_POST['submit'])){ $values=array(); if (isset($_POST['honda'])){ $values[]= mysql_real_escape_string(trim($_POST['honda'])); } if (isset($_POST['toyota'])){ $values[]= mysql_real_escape_string(trim($_POST['toyota'])); } if (isset($_POST['mazda'])){ $values[]= mysql_real_escape_string(trim($_POST["mazda"])); } $sql = "SELECT * FROM tbl"; $filerby=implode("='yes' AND ",$values); $filerby.="='yes'"; if (count($values)>0){ $sql = "WHERE $filerby"; } } ?> <html> <body> <form name="myform" action="" method="post"> Honda:<input type="checkbox" value="honda" name="honda"><br /> Toyota:<input type="checkbox" value="toyota" name="toyota"><br /> Mazda:<input type="checkbox" value="mazda" name="mazda"><br /> <input type="submit" value="submit" name="submit"> </form> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360205 Share on other sites More sharing options...
Pikachu2000 Posted July 9, 2012 Share Posted July 9, 2012 Which reminds me, the code I posted has no validation or sanitizing of any of the form data. You'll definitely want to add some. Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360206 Share on other sites More sharing options...
Drummin Posted July 9, 2012 Share Posted July 9, 2012 Should have been concatenated on the WHERE section on my copy. $sql .= "WHERE $filerby"; Full update. <?php //add DB connection include("access.php"); //////////////////// if (isset($_POST['submit'])){ $values=array(); if (isset($_POST['honda'])){ $values[]= mysql_real_escape_string(trim($_POST['honda'])); } if (isset($_POST['toyota'])){ $values[]= mysql_real_escape_string(trim($_POST['toyota'])); } if (isset($_POST['mazda'])){ $values[]= mysql_real_escape_string(trim($_POST["mazda"])); } $sql = "SELECT * FROM tbl"; $filerby=implode("='yes' AND ",$values); $filerby.="='yes'"; if (count($values)>0){ $sql .= "WHERE $filerby"; } } ?> Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360210 Share on other sites More sharing options...
stevew Posted July 10, 2012 Author Share Posted July 10, 2012 Should have been concatenated on the WHERE section on my copy. $sql .= "WHERE $filerby"; Full update. <?php //add DB connection include("access.php"); //////////////////// if (isset($_POST['submit'])){ $values=array(); if (isset($_POST['honda'])){ $values[]= mysql_real_escape_string(trim($_POST['honda'])); } if (isset($_POST['toyota'])){ $values[]= mysql_real_escape_string(trim($_POST['toyota'])); } if (isset($_POST['mazda'])){ $values[]= mysql_real_escape_string(trim($_POST["mazda"])); } $sql = "SELECT * FROM tbl"; $filerby=implode("='yes' AND ",$values); $filerby.="='yes'"; if (count($values)>0){ $sql .= "WHERE $filerby"; } } ?> Not as nice as Pikachu2000's solution, but as I was typing out while he was posting, I thought I'd post it anyway. <?php //add DB connection include("access.php"); //////////////////// if (isset($_POST['submit'])){ $values=array(); if (isset($_POST['honda'])){ $values[]= mysql_real_escape_string(trim($_POST['honda'])); } if (isset($_POST['toyota'])){ $values[]= mysql_real_escape_string(trim($_POST['toyota'])); } if (isset($_POST['mazda'])){ $values[]= mysql_real_escape_string(trim($_POST["mazda"])); } $sql = "SELECT * FROM tbl"; $filerby=implode("='yes' AND ",$values); $filerby.="='yes'"; if (count($values)>0){ $sql = "WHERE $filerby"; } } ?> <html> <body> <form name="myform" action="" method="post"> Honda:<input type="checkbox" value="honda" name="honda"><br /> Toyota:<input type="checkbox" value="toyota" name="toyota"><br /> Mazda:<input type="checkbox" value="mazda" name="mazda"><br /> <input type="submit" value="submit" name="submit"> </form> </body> </html> I have been trying these suggestions, and they function without errors...but are only "printing" the row names but not the field data. This is the echo code i am trying to display...but wherever I place it, the results still display blank. while($row = mysql_fetch_array( $result )) { // Print out the contents of each row echo "<br><hr>"; echo "Name:";echo $row['name'] . "<br>"; echo $row['text'] . "<br>"; echo "<img src='".$row['image'] ."'>"."</a>"; echo "<br><br>"; echo "<a href='".$row['link']."'>".$row['link']."</a>"; echo "<br>"; Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360501 Share on other sites More sharing options...
Drummin Posted July 10, 2012 Share Posted July 10, 2012 Did you add the mysql_query to the filter string? Also be sure to change the name of the "tbl". <?php //add DB connection include("access.php"); //////////////////// if (isset($_POST['submit'])){ $values=array(); if (isset($_POST['honda'])){ $values[]= mysql_real_escape_string(trim($_POST['honda'])); } if (isset($_POST['toyota'])){ $values[]= mysql_real_escape_string(trim($_POST['toyota'])); } if (isset($_POST['mazda'])){ $values[]= mysql_real_escape_string(trim($_POST["mazda"])); } $sql = "SELECT * FROM tbl"; $filerby=implode("='yes' AND ",$values); $filerby.="='yes'"; if (count($values)>0){ $sql = "WHERE $filerby"; } $result = mysql_query($sql) or die (mysql_error()); while($row = mysql_fetch_array($result)) { // Print out the contents of each row echo "<br><hr>"; echo "Name:"; echo "{$row['name']}<br>"; echo "{$row['text']}<br>"; echo "<img src='".$row['image'] ."'>"."</a>"; echo "<br><br>"; echo "<a href='".$row['link']."'>".$row['link']."</a>"; echo "<br>"; } } ?> Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360534 Share on other sites More sharing options...
stevew Posted July 10, 2012 Author Share Posted July 10, 2012 Did you add the mysql_query to the filter string? Also be sure to change the name of the "tbl". Yep. Here is the code I am using...it produces the check box form...but on submit it just refreshes with no results. BTW...does the field text need to be the names (honda, toyota, mazda) or "yes" for the code to work? <?php <form name="myform" action="checkboxtest.php"> Honda:<input type="checkbox" value="Honda" name="honda[]"><br /> Toyota:<input type="checkbox" value="Toyota" name="toyota[]"><br /> Mazda:<input type="checkbox" value="Mazda" name="mazda[]"><br /> <input type="submit" value="submit" name="submit"> </form> ?> <?php include("mysql.php");//database connection if (isset($_POST['submit'])){ $values=array(); if (isset($_POST['honda'])){ $values[]= mysql_real_escape_string(trim($_POST['honda'])); } if (isset($_POST['toyota'])){ $values[]= mysql_real_escape_string(trim($_POST['toyota'])); } if (isset($_POST['mazda'])){ $values[]= mysql_real_escape_string(trim($_POST["mazda"])); } $sql = "SELECT * FROM ads"; $filerby=implode("='yes' AND ",$values); $filerby.="='yes'"; if (count($values)>0){ $sql = "WHERE $filerby"; } $result = mysql_query($sql) or die (mysql_error()); while($row = mysql_fetch_array($result)) { // Print out the contents of each row echo "<br><hr>"; echo "Name:"; echo "{$row['name']}<br>"; echo "{$row['text']}<br>"; echo "<img src='".$row['image'] ."'>"."</a>"; echo "<br><br>"; echo "<a href='".$row['link']."'>".$row['link']."</a>"; echo "<br>"; } } ?> Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360593 Share on other sites More sharing options...
Drummin Posted July 10, 2012 Share Posted July 10, 2012 Note form field values and compare to DB field names. In your first post you listed DB field names as lower case. In my example I also made form field values lower case to match. You are also posting names as arrays by adding [] after the name, i.e. name="honda[]". Change to singular names, name="honda". Quote Link to comment https://forums.phpfreaks.com/topic/265387-select-statement-for-checkboxes/#findComment-1360604 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.