• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Dropdown Box Groups

I am trying to Group my options in a dropdown box.  This is the table..

tblDetails
DetailID
DetailType
DetailName

This is the PHP code thats is returning no results at the moment.

<select name="DETAILS">
<?php
include "config.php";
$sql = "SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|') 
 AS DetailName FROM $tblDetails GROUP BY DetailType"; 
$result = mysql_query($q)or die(mysql_error()); 
while ($row = mysql_fetch_assoc($result)) { 
   echo "<optgroup label='{$row['type']}'>\n"; 
   $DetailNames = explode('|', $row['DetailName']); 
   foreach($DetailNames as $DetailName) { 
      echo "<option value='$DetailName'>$DetailName</option>\n"; 
   } 
   echo "</optgroup>\n"; 
}  
?>
</select> 

Open in new window


Any suggestions on what is wrong?
0
DS928
Asked:
DS928
  • 14
  • 12
  • 4
  • +1
1 Solution
 
Mark BradyPrincipal Data EngineerCommented:
The first thing I would do is to add this line on line 7 (before the while loop)

echo mysql_num_rows($result);

and run it to see if you have any results returning. Sounds like you don't so the next thing is to try and run that query directly in the database.

SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|') 
 AS DetailName FROM $tblDetails GROUP BY DetailType

Open in new window


replace any php variables with the actual name (ie: table name)

run that query and see if you get resuilts. Chances are there is an error in your query somewhere
0
 
Lukasz ChmielewskiCommented:
$sql = "SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|')
 AS DetailName FROM $tblDetails GROUP BY DetailType";
$result = mysql_query($sql)or die(mysql_error());
0
 
Lukasz ChmielewskiCommented:
You have $q as the query
// this is incorrect - $result = mysql_query($q)or die(mysql_error());
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DS928Author Commented:
All changes made.  Still no result.

<select name="DETAILS">
<?php
include("config.php");
$sql = "SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|') 
AS DetailName FROM $tblDetails GROUP BY DetailType"; 
$result = mysql_query($sql)or die(mysql_error());  
echo mysql_num_rows($result);

while ($row = mysql_fetch_assoc($result)) { 
   echo "<optgroup label='{$row['type']}'>\n"; 
   $DetailNames = explode('|', $row['DetailName']); 
   foreach($DetailNames as $DetailName) { 
      echo "<option value='$DetailName'>$DetailName</option>\n"; 
   } 
   echo "</optgroup>\n"; 
}  
?>

Open in new window


I am getting this error.
[SQL] <?php
$sql = "SELECT DetailType, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|') 
AS DetailName FROM $tblDetails GROUP BY DetailType";
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?php
$sql = "SELECT DetailType, GROUP_CONCAT(DISTINCT DetailName ORDER BY Deta' at line 1

Open in new window

0
 
Lukasz ChmielewskiCommented:
Does this work: ?

<?php
include("config.php");
$sql = "SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|') AS DetailName FROM ".$tblDetails." GROUP BY DetailType"; 
$result = mysql_query($sql) or die(mysql_error());  
?>

<select name="DETAILS">
<?php
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>"; 
}  
?>

Open in new window

0
 
DS928Author Commented:
Same error, Line one.
0
 
Lukasz ChmielewskiCommented:
Take a deep look into your config.php file - I assume it contains database connection info - doesn't it have <?php tag open at the end ?
0
 
Lukasz ChmielewskiCommented:
And get rid of those \n after options it does nothing.
0
 
DS928Author Commented:
Config file is fine.  I'm using your code suggestion.  I have Navicat and I am testing the query in it, still an error on line one.
0
 
Lukasz ChmielewskiCommented:
Does the query run ok on phpmyadmin or anything more direct to mysql server ?
0
 
Lukasz ChmielewskiCommented:
I was able to reproduce your environment on my localhost and the query runs fine as well as the select creation, can you post the table data and the create statement ?
0
 
Chris StanyonCommented:
Your SQL error is indicating that <?php $sql = ' is being sent as part of the query!!!
0
 
DS928Author Commented:
Does not run in PHPAdmin.  Here is the table info.
DetailID   DetailName    DetailType
1      Buffet            Feature
2      Fireplace            Feature
3      Great Views      Feature
4      Outdoor Dining      Feature
5      Private Room(s)      Feature
6      Raw Bar             Feature
7      Waterfront      Feature
8      Wheelchair Access      Feature
9      Wi-Fi             Feature
10      Breakfast      Meal Period
11      Brunch      Meal Period
12      Dinner      Meal Period
13      Happy Hour      Meal Period
14      Late-Night      Meal Period
15      Lunch      Meal Period
16      Open 24 Hours      Meal Period
17      Pre /Post Theatre      Meal Period
18      BYOB      Service
19      Catering      Service
20      Credit Cards      Service
21      Delivery      Service
22      Food Truck / Cart      Service
23      On-Line Ordering      Service
24      On-Line Reservations      Service
25      Reservations      Service
26      Take-Out      Service
27      Text Orders      Service
28      Bar Scene      Type
29      Business Dining      Type
30      Cheap Eats      Type
31      Group Dining      Type
32      Kid Friendly      Type
33      Live Entertainment      Type
34      People Watching      Type
35      Romantic      Type
36      Specials      Type
37      Tasting Menu      Type
38      Trendy      Type
39      Diner      Type
40      Sports Bar      Type
41      Theme      Type
42      Gastro-Pub      Type
43      Hotel Dining      Type
0
 
Lukasz ChmielewskiCommented:
Your sql statement seems to start with this:

<?php
$sql = "SELECT

something's wrong with this open tag - do you have short_open_tags turned on in php_ini ?
0
 
Lukasz ChmielewskiCommented:
in phpmyadmin:

SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|') AS DetailName FROM tblDetails GROUP BY DetailType

what is the error ?
0
 
DS928Author Commented:
No Errors.  It returns


Feature

Wi-Fi|Wheelchair Access|Waterfront|Raw Bar|Private...
 


Meal Period

Pre /Post Theatre|Open 24 Hours|Lunch|Late-Night|H...
 


Service

Text Orders|Take-Out|Reservations|On-Line Reservat...
 


Type

Trendy|Theme|Tasting Menu|Sports Bar|Specials|Roma...
0
 
Lukasz ChmielewskiCommented:
Be so kind and mask all the password etc. and show us the config.php file
0
 
Lukasz ChmielewskiCommented:
How about this:
(you have to replace your* data strings)

<?php
mysql_connect("yourhost","yourusername","yourpassword");
mysql_select_db("yourdatabase");
$tblDetails = "tblDetails";

$sql = "SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|') AS DetailName FROM ".$tblDetails." GROUP BY DetailType"; 
$result = mysql_query($sql) or die(mysql_error());  
?>

<select name="DETAILS">
<?php
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>"; 
}  
?>

Open in new window

0
 
DS928Author Commented:
This works.  I pasted your SELECT into the statement.

<select name="DETAILS">
<?php
include("config.php");



$sql = "SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|') AS DetailName FROM tblDetails GROUP BY DetailType"; 
$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> 

What was different?

Open in new window

0
 
Lukasz ChmielewskiCommented:
Then: is the $tblDetails defined in the config.php file ?
0
 
DS928Author Commented:
No, not at all.

<?php
$con = mysql_connect("MyHead.db.99999.hostce.com", "MyStuff", "MyStuff@2013") or die("Failed to connect to MySql.");
mysql_select_db("MyHead", $con) or die("Failed to connect to database");
?>

Open in new window

0
 
DS928Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for DS928's comment #a38861033

for the following reason:

Roads, Go to my other question.  "Option Group in PHP" and put your solution there as well.  That'll give you a total of 1,000 points.  Shazzzzzam!  Thank you!
0
 
Lukasz ChmielewskiCommented:
$sql = "SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|') AS DetailName FROM ".$tblDetails." GROUP BY DetailType";

$sql = "SELECT DetailType AS type, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|') AS DetailName FROM tblDetails GROUP BY DetailType";

Then you need to use the tblDetails without the $ variable indicator, or define it in the config.php file like:
$tblDetails = "tblDetails";
0
 
Chris StanyonCommented:
Before you get too far into your project, you should NOT be using mysql_query anymore. It's deprecated and insecure. Look into using PDO instead!
0
 
DS928Author Commented:
Hey, what is that?  I want Roads to get the 500 points he solved it!  I don't understand that comment about 0 points.  Moderator!
0
 
DS928Author Commented:
Chris, any good links on PDO?
0
 
Lukasz ChmielewskiCommented:
I got that points. What's with the other question ? (I think I'll get to it tomorrow - post the link pls) Thanks.
0
 
Chris StanyonCommented:
Have a look at this one - it will show you how to do the old mysql_* stuff with PDO:

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers
0
 
DS928Author Commented:
Thank you, Chris.
0
 
DS928Author Commented:
Roads, The Link.

http://www.experts-exchange.com/Database/MySQL/Q_28018427.html

Do a quick cut and paste.  500 points!
0
 
Chris StanyonCommented:
Here's your code using PDO ;)

<?php 
$conn = new PDO('mysql:host=localhost;dbname=yourDB', 'yourUser', 'yourPass');
$query = $conn->query("SELECT DetailType, GROUP_CONCAT(DISTINCT DetailName ORDER BY DetailName DESC SEPARATOR '|') AS DetailName FROM tblDetails GROUP BY DetailType"); 

if ($query->rowCount()):
	echo "<select>";
	while($details = $query->fetch(PDO::FETCH_OBJ)) {
		printf("<optgroup label='%s'>",$details->DetailType);
		foreach(explode('|', $details->DetailName) as $DetailName) {
			printf("<option value='%s'>%s</option>",$DetailName, $DetailName); 
		}
		echo "</optgroup>"; 
	}
	echo "</select>";
endif;
?>

Open in new window

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 14
  • 12
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now