0

I am building a plant reference guide that pulls from two tables within a mysql db . One table "findplantsdb" has the plant name, desc, image and the other table "plantdb" has the availability of plants listed in the yard and field.

I need both databases to come together (JOIN) by matching the name of the plant in both databases and provide the information that the visitor checkboxes in two different categories: Rose Class and Flower Color

So far everything is working except the availability part (plantsdb).

Currently it only displays the availability if there is only one checkbox selected in the "Rose Class" and one in "Flower Color". If there are more selected in either checkbox category, it gives me the error "no results found".

I think this is a looping problem, but I do not know how to fix it. I need it to display the availability for ALL the plants selected by checkbox.

Can anyone assist with this? Thanks!!

(I will provide below only the php relating to the issue described above)

<?PHP

$data = "SELECT plantsdb.name, plantsdb.type, plantsdb.size, plantsdb.yard, plantsdb.field, plantsdb.total FROM plantsdb LEFT JOIN findplantsdb ON plantsdb.name=findplantsdb.Name WHERE findplantsdb.Color = $FlowerList AND findplantsdb.Lastword = $LastwordList AND findplantsdb.Type LIKE 'ROSE'";  

$result = mysql_query ($data); //run the query.


Print "<table border=0 cellspacing=2 cellpadding=0 align='center'>";
Print "<font size=2 face='Arial'>";


$num_of_rows = mysql_num_rows($result);
if($num_of_rows > 0)

{
 
while($row = mysql_fetch_array($result)) {
 

Print "<tr>"; 
Print "<td>".$row['name'] . "</td>";
Print "<td>-Size: ".$row['size'] . "</td>";
Print "<td>-Type: ".$row['type'] . "</td>";
Print "<td>-Yd: ".$row['yard'] . "</td>";
Print "<td>-Fld: ".$row['field'] . "</td></tr>";

} 


Print "</table>"; 

} 

else

{ 


print "no results found";

}
 
 
?>
2
Contributors
5
Replies
7
Views
5 Years
Discussion Span
Last Post by cereal
0

Can you post the form where there are the checkbox fields? In order to work the checkbox name as to be the same for each group, so you send an array to the script, when you receive that, you implode() the array or do a loop to use it in the query:

<input type="checkbox" name="roses[]" value="blue" /> blue
<input type="checkbox" name="roses[]" value="red" /> red
<input type="checkbox" name="roses[]" value="white" /> white

On PHP side you write:

$FlowerList = implode(',',$_POST['roses']);

$data = "SELECT plantsdb.name, plantsdb.type, plantsdb.size, plantsdb.yard, plantsdb.field, plantsdb.total FROM plantsdb LEFT JOIN findplantsdb ON plantsdb.name=findplantsdb.Name WHERE findplantsdb.Color in($FlowerList) AND findplantsdb.Lastword = $LastwordList AND findplantsdb.Type LIKE 'ROSE'";

Do the same for Rose Class. This example works only if in findplantsdb there is only one color per row.

0

The form basically allows visitors to select whatever rose class and color they would like to retrieve more info on and that would include the availability of those roses. It is successfully pulling the roses selected for via findplantsdb for the desc, picture, etc, but it is the second part (the availability) that is screwing up. Thank you so much for any help at all!

<form name="plantsearch" id="myform" method="post">

   
<?php
	  

if (isset($_POST['Lastword'])) 
    { 
        foreach ($_POST['Lastword'] as $selectedLastword) 
            $selected[$selectedLastword] = "checked"; 
    }




 if (isset($_POST['Flower'])) 
    { 
        foreach ($_POST['Flower'] as $selectedFlower) 
            $selected[$selectedFlower] = "checked"; 
    }



?>

    
  <p align="center"><b>ROSE CLASS<br />
- must select at least one or choose 'Any'</b></p>
 <p align="center">


<fieldset style="border:hidden">


<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Lastword[]" id="Lastword" <?php echo $selected['ROSE ANTIQUE'] ?> value="ROSE ANTIQUE" class=2 /> 
ANTIQUE </div><br>

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Lastword[]" id="Lastword" <?php echo $selected['ROSE CLIMBING'] ?> value="ROSE CLIMBING" class=2 />
CLIMBING
</div><br>

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Lastword[]" id="Lastword" <?php echo $selected['ROSE ENGLISH ROSE'] ?> value="ROSE ENGLISH ROSE" class=2 />
ENGLISH ROSE
</div><br>

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Lastword[]" id="Lastword" <?php echo $selected['ROSE FLORIBUNDA'] ?> value="ROSE FLORIBUNDA" class=2 />
FLORIBUNDA
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Lastword[]" id="Lastword" <?php echo $selected['ROSE GRANDIFLORA'] ?> value="ROSE GRANDIFLORA" class=2 />
GRANDIFLORA
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Lastword[]" id="Lastword" <?php echo $selected['ROSE GROUNDCOVER'] ?> value="ROSE GROUNDCOVER" class=2 />
GROUNDCOVER
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Lastword[]" id="Lastword" <?php echo $selected['ROSE HYBRID TEA'] ?> value="ROSE HYBRID TEA" class=2 />
HYBRID TEA
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Lastword[]" id="Lastword" <?php echo $selected['ROSE MINIATURE'] ?> value="ROSE MINIATURE" class=2 />
MINIATURE
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Lastword[]" id="Lastword" <?php echo $selected['ROSE SHRUB'] ?> value="ROSE SHRUB" class=2 />
SHRUB
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Lastword[]" id="Lastword" <?php echo $selected['ROSE SHRUBLET'] ?> value="ROSE SHRUBLET" class=2 />
SHRUBLET
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" class="checkall"> 
ANY</div>

</fieldset>

<br>
 

<p align="center"><img src="http://www.domain.com/mobile/images/greenlineswirlM.jpg" alt="" width="294" height="30" border="0" /></p>

   

    </p>
<p>&nbsp;</p>
 
    
  <p align="center"><b>FLOWER COLOR<br />
- must select at least one or choose 'Any'</b></p>
 <p align="center">


<fieldset style="border:hidden">



<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Flower[]" id="Flower" <?php echo $selected['coral flower'] ?> value="coral flower" class=2 /> 
CORAL </div><br>

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Flower[]" id="Flower" <?php echo $selected['lavender flower'] ?> value="lavender flower" class=2 />
LAVENDER
</div><br>

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Flower[]" id="Flower" <?php echo $selected['mixed flower'] ?> value="mixed flower" class=2 />
MIXED
</div><br>

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Flower[]" id="Flower" <?php echo $selected['orange flower'] ?> value="orange flower" class=2 />
ORANGE
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Flower[]" id="Flower" <?php echo $selected['pink flower'] ?> value="pink flower" class=2 />
PINK
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Flower[]" id="Flower" <?php echo $selected['purple flower'] ?> value="purple flower" class=2 />
PURPLE
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Flower[]" id="Flower" <?php echo $selected['red flower'] ?> value="red flower" class=2 />
RED
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Flower[]" id="Flower" <?php echo $selected['white flower'] ?> value="white flower" class=2 />
WHITE
</div><br />

<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" name="Flower[]" id="Flower" <?php echo $selected['yellow flower'] ?> value="yellow flower" class=2 />
YELLOW
</div><br />




<div align="center" style="background-color:#D3EDE1; cursor:hand">
<input type="checkbox" class="checkall"> 
ANY</div>


</fieldset>

<br>
 




 
</p> 
 
  <p align="center">

  <label><input name=submit type=submit value="SEARCH PLANTS" style="font-family: Arial; font-size: 13pt; color: #000000" /></label>
 <p align="center">

</form> 

<div align="center">

<?  






if(isset($_POST['submit']))  
{ 


  
 
  foreach($_POST['Lastword'] As $Lastword) { 
   printf("%s<br>", $Lastword);  
  }  
  $LastwordList = "'".join("','",$_POST['Lastword'])."'"; 
  
  
 
  foreach($_POST['Flower'] As $Flower) { 
    printf("%s<br>", $Flower);  
   }

  $FlowerList = "'".join("','",$_POST['Flower'])."'"; 
  

 
// Make a MySQL Connection 
    mysql_connect("", "", "") or die(mysql_error()); 
    mysql_select_db("") or die(mysql_error()); 
	

$x = "";
 


  $query = "SELECT * FROM findplantsdb WHERE Name LIKE 'ROSA%' AND Lastword IN ($LastwordList) AND Color IN ($FlowerList)"; 
  



  $result= mysql_query($query);  
$num_results = mysql_num_rows($result);  
   
  for ($i=0; $i <$num_results; $i++) {  
    $row = mysql_fetch_array($result);  


  echo "<h4> ", $row['Name'], " &nbsp; ", $row['Patent'], "</h4> ",$row['Common'], "<p> Height:  ", $row['Hname'], "<br> Spread:  ", $row['Sname'], "<br> Color:  ", $row['Color'], "<br> Light:  ", $row['Light'], "<br> Zone:  ", $row['Zone'], "<p> <img src=http://www.domain.com/mobile/",$row['Picname'],  " /> <p><p>", $row['Notes'], "<p><p> <hr width='50%' size='1' color='#A3A3A3'> <p>"; 
  }  
} 


?>
</div>





<p align="center"><b>AVAILABILITY</b>


<?PHP

$data = "SELECT plantsdb.name, plantsdb.type, plantsdb.size, plantsdb.yard, plantsdb.field, plantsdb.total FROM plantsdb LEFT JOIN findplantsdb ON plantsdb.name=findplantsdb.Name WHERE findplantsdb.Color = $FlowerList AND findplantsdb.Lastword = $LastwordList AND findplantsdb.Type LIKE 'ROSE'";  

$result = mysql_query ($data); //run the query.


Print "<table border=0 cellspacing=2 cellpadding=0 align='center'>";
Print "<font size=2 face='Arial'>";


$num_of_rows = mysql_num_rows($result);
if($num_of_rows > 0)

{
 
while($row = mysql_fetch_array($result)) {
 

Print "<tr>"; 
Print "<td>".$row['name'] . "</td>";
Print "<td>-Size: ".$row['size'] . "</td>";
Print "<td>-Type: ".$row['type'] . "</td>";
Print "<td>-Yd: ".$row['yard'] . "</td>";
Print "<td>-Fld: ".$row['field'] . "</td></tr>";

} 


Print "</table>"; 

} 

else

{ 

print "no results found";

}
 
 
?>
0

What happens if you use this query?

$data = "SELECT plantsdb.name, plantsdb.type, plantsdb.size, plantsdb.yard, plantsdb.field, plantsdb.total FROM plantsdb LEFT JOIN findplantsdb ON plantsdb.name=findplantsdb.Name WHERE findplantsdb.Color in($FlowerList) AND findplantsdb.Lastword in($LastwordList) AND findplantsdb.Type LIKE 'ROSE'";

Check what happens on line 199 and 207:

# this could be wrong
$FlowerList = "'".join("','",$_POST['Flower'])."'";

# for single checkbox will output: 'blue' and that's ok
# for multiple checkbox: 'blue,red,yellow,white' instead of 'blue','red','yellow','white'

# so try:
$FlowerList = join(",",$_POST['Flower']);
0

Boy, I thought we had it, but unfortunately I get "no results found" no matter what I try to pull. :/

0

Try to change line 251 with this:

$result = mysql_query ($data) or die(mysql_error());

Just to understand if the query is returning an empty set or if there is an error. I can't think anything else, I'm sorry ;(

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.