27 replies to this topic

[DSTR3]

I am running a query that works until I add the variable $selections. This is from a multi-select dropdown box. I am sure my syntax is off on this. I am using MySQL 5.0 Error message is "Cannot Parse Query"

SELECT tblLocations.CityID, tblDetails.DetailName, tblRestaurants.RestName,   
   CONCAT(tblLocations.StreetNumber,' ',tblLocations.Street) Address, tblLocations.Phone, tblLocations.Price,  
tblLocations.Rating, tblRestaurants.RestPage
   FROM (tblRestaurants INNER JOIN tblLocations ON tblRestaurants.RestID = tblLocations.RestID) INNER   JOIN 
(tblLocDet INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID) ON tblLocations.LocationID = 
tblLocDet.LocationID
   GROUP BY tblLocations.CityID, tblLocations.AreaID, tblLocations.CuisineID, tblDetails.DetailName, tblRestaurants.RestName, tblLocations.Street, 
tblLocations.Phone, tblLocations.Price, tblLocations.Rating
   HAVING tblLocations.CityID='16'
   AND tblLocations.AreaID='131'
   AND tblLocations.CuisineID='3'
   AND tblDetails.DetailName='( ' . implode(' AND ', $selections) . ' )'
   ORDER BY tblRestaurants.RestName ASC

[Barand]

Post the query after the $selections has been imploded. It's impossible to see what is actually submitted at present.

[Jessica]

The fact that he's using AND as the delimiter for implode should through a red flag though. AFAIK you cannot do field = (value1 AND value2) - for one, it's not the right syntax, and secondly a single column CANNOT be BOTH value1 and value2. 

OP, you can use IN() and use a comma as the delim.

Edited by Jessica, 09 February 2013 - 12:48 PM.

[DSTR3]

OK, I'm going to reduce the code just to get a better handle on this. I have a multiple select list box. I am selecting multiple items in this box and want to run my query and get the results based on the selections in the box. Such as this...if the user selects Cat, Dog and Pig. I want only the records to be returned that have Cat and Dog and Pig. Not those with Cat or Dog or Pig. Of course this is greatly simplified, I have 42 options, so this is why I need the DetailsName part of the query to be built dynamically.

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" id="Test">
    <select name="p[]" size="10" style="width:170px" multiple="multiple" method="POST">
		    <?php
   error_reporting(E_ALL);
   include("config.php");
   $sql = "SELECT tblDetails.DetailType AS type,
   GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName ASC SEPARATOR '|') AS DetailName
   FROM tblLocations INNER JOIN (tblLocDet INNER JOIN tblDetails
   ON tblLocDet.DetailID = tblDetails.DetailID) ON tblLocations.LocationID = tblLocDet.LocationID
   GROUP BY tblDetails.DetailType,tblLocations.CityID,tblLocations.AreaID,tblLocations.CuisineID
   HAVING (((tblLocations.CityID)='16')
   AND ((tblLocations.AreaID)='131')
   AND ((tblLocations.CuisineID)='3'))";
   $result = mysql_query($sql) or die(mysql_error()); 
   while ($row = mysql_fetch_assoc($result)) {
	  echo "<optgroup label='{$row['type']}'>";
	  $DetailNames = explode('|', $row['DetailName']);
	  foreach($DetailNames as $DetailName) {
	  echo "<option value='".$DetailName."'>".$DetailName."</option>";
	  }
	  echo "</optgroup>";
   } 
   ?>
    </select>
    <input type="submit" name="unused" value="Post Selections" />
</form>
<?php
include("config.php");
  if (!empty($_POST))
  {
	 $selections = ($_POST['p']);
   foreach ($selections as $key => $value)
	  {
		  $selections[$key] = trim($value);
		  if (empty($selections[$key])) unset($selections[$key]);
	    }
	    if (empty($selections)) die('No Selection');
	
   $where = 'WHERE ( ' . implode(' AND ', $selections) . ' )';
	  print_r($where);
  
   ?>
   <?php
    if(!isset($selections))
	 {
	 echo("<p>You didn't select any filters!</p>\n");
	 }
    else
	 {
	 $nselections = count($selections);
  
	 echo("<p>$nselections filter(s) selected:<br>");
	 for($i=0; $i < $nselections; $i++)
	 {
	 echo($selections[$i] . "<br/>");
	 }
	 echo("</p>");
	 $DM = implode(',',$selections);
	 if(!$rs=mysql_query("SELECT tblRestaurants.RestName, tblDetails.DetailName,
	 tblLocations.CityID, tblLocations.AreaID, tblLocations.CuisineID, tblLocations.RestID, 
	 CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) Address,tblRestaurants.RestPage,
	 tblLocations.StreetNumber, tblLocations.Street, tblLocations.Phone, tblLocations.Price, 
	 tblLocations.Rating
	 FROM tblDetails INNER JOIN tblRestaurants INNER JOIN tblLocations ON tblRestaurants.RestID =	  tblLocations.RestID INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID 
	 ON tblDetails.DetailID = tblLocDet.DetailID
	 WHERE tblLocations.tblCityID = '16'
	 AND tblLocations.AreaID ='131'
	 AND tblLocations.CuisineID = '3'
	 AND tblDetails.DetailName = '$_POST('$selections')
	 ORDER BY tblRestaurants.RestName;"))
{
echo "Cannot parse query";
}
elseif(mysql_num_rows($rs) == 0) {
echo "No records found";
}
else {
echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\" cellspacing=\"0\">\n";
echo "<thead>\n<tr>";
echo "<th>PLACE</th>";
echo "<th>ADDRESS</th>";
echo "<th>PHONE</th>";
echo "<th>PRICE</th>";
echo "<th>RATING</th>";
echo "</tr>\n</thead>\n";
while($row = mysql_fetch_array($rs)) {
echo"<tr>
<td><strong><a href='$row[RestPage]'>$row[RestName]</a></strong></td>
<td>$row[Address]</td>
<td>$row[Phone]</td>
<td>$row[Price]</td>
<td>$row[Rating]</td>
</tr>\n";
}
echo "</table><br />\n";
}
	 }
   }
?>

Edited by DSTR3, 09 February 2013 - 01:30 PM.

[Jessica]

I don't see anything in there related to animals.

This sounds very similar to a question I just asked. Read this and see if it helps.
http://forums.phpfre...onship-exactly/

[DSTR3]

Just using the animals as an example! LOL! The listbox has values like "Fireplace", "Buffet", "Waterfront", etc....In any case why am I not getting a result on my query?

[Jessica]

This sounds very similar to a question I just asked. Read this and see if it helps.
http://forums.phpfre...onship-exactly/

[Jessica]

As to why your query is failing:

 AND tblDetails.DetailName = '$_POST('$selections')

There are so many things wrong with that...

[DSTR3]

OK, So how does one "Make It Right?" I did switch WHERE to HAVING as read in your question.
Changed it to this.....
AND tblDetails.DetailName = '$DM')
Still no results...

Edited by DSTR3, 09 February 2013 - 02:02 PM.

[Jessica]

If you want us to be able to help you you need to post your table structure, some sample data, and go read up on MySQL syntax, as well as probably some basic PHP syntax - like how $_POST is an array, not a function.

It says Cannot Parse Query because that's the error message YOU'VE chosen to print when your query fails. You could get a much better error by using the tips in my Debugging SQL post.

But you haven't explained how your data is related so we can't help you. There's a lot more to the post I wrote than just WHERE vs HAVING, you're not doing anything I did.

[Barand]

Still no sign of the actual query that is submitted. Good luck.

[DSTR3]

Barand, Everything is there. The problem is with the multiple variables.

Jessica here are the tables involved.

tblRestaurants

RestID

RestName

RestPage

tblLocations

LocationID

CityID

AreaID

CuisineID

tblLocDet

LocationID

DetailID

tblDetails

DetailID

DetailType

DetailName

The problem is the one line as you say it is. I am aware of that and the message I am getting. I just need to know how to build the last part of the query to accomodate the variables.

[Jessica]

Good luck

[DSTR3]

Really. Why do you bother? If you don't want to help, or can't just say so, or better yet don't even post. Life is so much easier when you help people....but I guess some people don't see it that way.

Edited by DSTR3, 09 February 2013 - 02:38 PM.

[Jessica]

ROTFL. 

Yes, your life would be easier if we just did it for you. Too bad for you this is FREE HELP and we actually expect you to HELP US HELP YOU. I've asked you at least FIVE questions you haven't answered fully, I've given you a thread that describes IN DETAIL how I solved this problem (if it IS the same problem, you haven't given enough INFORMATION for me to be sure), and you refuse to actually do any of the things we suggest. 

Why do *YOU* bother? You clearly don't want to be helped. Why did you bother posting this?

Life is so much easier when PEOPLE HELP THEM GOD DAMN SELVES. I'm so sick of people like you whinging for help and then when it's not handed to you on a silver platter in ONE FUCKING POST you blame US. 

[DSTR3]

You are one NASTY person!

[Barand]

We are not psychic.
We cannot look over your shoulder and see what you are seeing.
To help we often need more information and ask for it.
If we don't get it there is no point in our wasting our time and we move on.

[DSTR3]

You have everything that I have. I gave you all of the code on my page. I gave you the tables. I don't know what else to give you. The problem is:
I am trying to run a query that uses the selected options on a drop down multi-select. The drop down has 42 options. I do not know how many or what will be selected, but I need to run my query based on the selections made in the dropdown. I can get one option working, however: when I select more than one I get bak "No Records found". Here is the code at this point.

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" id="Test">
    <select name="p[]" size="10" style="width:170px" multiple="multiple" method="POST">
		    <?php
   error_reporting(E_ALL);
   include("config.php");
   $sql = "SELECT tblDetails.DetailType AS type,
   GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName ASC SEPARATOR '|') AS DetailName
   FROM tblLocations INNER JOIN (tblLocDet INNER JOIN tblDetails
   ON tblLocDet.DetailID = tblDetails.DetailID) ON tblLocations.LocationID = tblLocDet.LocationID
   GROUP BY tblDetails.DetailType,tblLocations.CityID,tblLocations.AreaID,tblLocations.CuisineID
   HAVING (((tblLocations.CityID)='16')
   AND ((tblLocations.AreaID)='131')
   AND ((tblLocations.CuisineID)='3'))";
   $result = mysql_query($sql) or die(mysql_error()); 
   while ($row = mysql_fetch_assoc($result)) {
	  echo "<optgroup label='{$row['type']}'>";
	  $DetailNames = explode('|', $row['DetailName']);
	  foreach($DetailNames as $DetailName) {
	  echo "<option value='".$DetailName."'>".$DetailName."</option>";
	  }
	  echo "</optgroup>";
   } 
   ?>
    </select>
    <input type="submit" name="unused" value="Post Selections" />
</form>
<?php
include("config.php");
  if (!empty($_POST))
  {
	 $selections = ($_POST['p']);
   foreach ($selections as $key => $value)
	  {
		  $selections[$key] = trim($value);
		  if (empty($selections[$key])) unset($selections[$key]);
	    }
	    if (empty($selections)) die('No Selection');
	
   $where = 'WHERE ( ' . implode(' AND ', $selections) . ' )';
	  //print_r($where);
   //var_dump($_POST)
   ?>
   <?php
    if(!isset($selections))
	 {
	 echo("<p>You didn't select any filters!</p>\n");
	 }
    else
	 {
	 $nselections = count($selections);
  
	 echo("<p>$nselections filter(s) selected:<br>");
	 for($i=0; $i < $nselections; $i++)
	 {
	 echo($selections[$i] . "<br/>");
	 }
	 echo("</p>");
	 $DM = implode(',',$selections);
	 if(!$rs=mysql_query("SELECT
tblRestaurants.RestName,
tblLocations.CityID,
tblLocations.AreaID,
tblLocations.CuisineID,
tblLocations.RestID,
tblRestaurants.RestPage,
CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) AS Address,
tblLocations.Phone,
tblLocations.Price,
tblLocations.Rating,
tblDetails.DetailName
FROM
tblRestaurants
INNER JOIN tblLocations ON tblRestaurants.RestID = tblLocations.RestID
INNER JOIN tblLocDet ON tblLocations.LocationID = tblLocDet.LocationID
INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
WHERE tblLocations.CityID='16'
AND tblLocations.AreaID='131'
AND tblLocations.CuisineID='3'
AND tblDetails.DetailName='$DM'
ORDER BY tblRestaurants.RestName ASC
"))
{
echo "Cannot parse query";
}
elseif(mysql_num_rows($rs) == 0) {
echo "No records found";
}
else {
echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\" cellspacing=\"0\">\n";
echo "<thead>\n<tr>";
echo "<th>PLACE</th>";
echo "<th>ADDRESS</th>";
echo "<th>PHONE</th>";
echo "<th>PRICE</th>";
echo "<th>RATING</th>";
echo "</tr>\n</thead>\n";
while($row = mysql_fetch_array($rs)) {
echo"<tr>
<td><strong><a href='$row[RestPage]'>$row[RestName]</a></strong></td>
<td>$row[Address]</td>
<td>$row[Phone]</td>
<td>$row[Price]</td>
<td>$row[Rating]</td>
</tr>\n";
}
echo "</table><br />\n";
}
	 }
   }
echo '<pre>'; var_dump($_POST)
?>

It should not be this hard to build a WHERE statement based on multiple selections from a list box! Please help. I have been searching for a solution for days now. Thank you.

[kicken]

You have everything that I have. I gave you all of the code on my page. I gave you the tables. I don't know what else to give you.

We don't know what $DM = implode(',',$selections); is, thus we can't get an accurate picture of what exactly your query is.  That is why you were asked to:

Post the query after the $selections has been imploded.

You need to take your query text and echo it out *AFTER* your variables have been substituted into it.  That way you and we can see what the final query being sent to mysql actually looks like.

[DSTR3]

Thank you for your response. I am sorry, but I am new to this, so if you could explain or show me what you mean, I will be happy to do it and get back to you.,