Jump to content

Select statement for checkboxes

stevew

By stevew
in PHP Coding Help

 Share

Recommended Posts

stevew Member

stevew

Posted
Posted

<?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%' ") ;

 

 

 

 

 

 

 

Link to comment
Share on other sites
hakimserwa Member

hakimserwa

Posted
Posted

<?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.

Link to comment
Share on other sites
stevew Member

stevew

Posted
  • Author
Posted

 

 

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%' ") ; 

 

??

 

 

Link to comment
Share on other sites
stevew Member

stevew

Posted
  • Author
Posted

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?

Link to comment
Share on other sites
Drummin Member

Drummin

Posted
Posted

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"

Link to comment
Share on other sites
Pikachu2000 Prolific Member

Pikachu2000

Posted
Posted

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'

Link to comment
Share on other sites
Drummin Member

Drummin

Posted
Posted

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>

Link to comment
Share on other sites
Drummin Member

Drummin

Posted
Posted

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"; 
}
}
?>

Link to comment
Share on other sites
stevew Member

stevew

Posted
  • Author
Posted

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>";

Link to comment
Share on other sites
Drummin Member

Drummin

Posted
Posted

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>";
}
}
?>

Link to comment
Share on other sites
stevew Member

stevew

Posted
  • Author
Posted

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>";
}
}
?>

 

 

 

 

 

 

Link to comment
Share on other sites
Drummin Member

Drummin

Posted
Posted

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".

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.