Jump to content


Photo

Cannot Parse Query

mysql php

  • This topic is locked This topic is locked
27 replies to this topic

#1 DSTR3

DSTR3

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 09 February 2013 - 11:35 AM

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


#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 09 February 2013 - 12:34 PM

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

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 09 February 2013 - 12:48 PM

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.

My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#4 DSTR3

DSTR3

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 09 February 2013 - 01:19 PM

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.


#5 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 09 February 2013 - 01:31 PM

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/
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#6 DSTR3

DSTR3

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 09 February 2013 - 01:43 PM

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?

#7 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 09 February 2013 - 01:46 PM

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


My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#8 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 09 February 2013 - 01:47 PM

As to why your query is failing:
 AND tblDetails.DetailName = '$_POST('$selections')
There are so many things wrong with that...
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#9 DSTR3

DSTR3

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 09 February 2013 - 02:00 PM

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.


#10 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 09 February 2013 - 02:03 PM

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.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#11 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 09 February 2013 - 02:04 PM

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

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#12 DSTR3

DSTR3

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 09 February 2013 - 02:22 PM

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.



#13 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 09 February 2013 - 02:33 PM

Good luck
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#14 DSTR3

DSTR3

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 09 February 2013 - 02:34 PM

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.


#15 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 09 February 2013 - 02:43 PM

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. 
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#16 DSTR3

DSTR3

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 09 February 2013 - 02:49 PM

You are one NASTY person!

#17 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 09 February 2013 - 03:18 PM

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.

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#18 DSTR3

DSTR3

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 09 February 2013 - 04:09 PM

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.

#19 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,679 posts
  • LocationBonita, FL

Posted 09 February 2013 - 05:20 PM

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.


Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#20 DSTR3

DSTR3

    Advanced Member

  • Members
  • PipPipPip
  • 54 posts

Posted 09 February 2013 - 06:04 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com