Solved

Dropdown Box Groups

Posted on 2013-02-06
31
378 Views
Last Modified: 2013-02-06
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
Comment
Question by:DS928
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 12
  • 4
  • +1
31 Comments
 
LVL 20

Expert Comment

by:Mark Brady
ID: 38860592
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38860621
$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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38860626
You have $q as the query
// this is incorrect - $result = mysql_query($q)or die(mysql_error());
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:DS928
ID: 38860721
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38860787
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
 

Author Comment

by:DS928
ID: 38860814
Same error, Line one.
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38860872
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38860881
And get rid of those \n after options it does nothing.
0
 

Author Comment

by:DS928
ID: 38860909
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38860926
Does the query run ok on phpmyadmin or anything more direct to mysql server ?
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38860934
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
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 38860957
Your SQL error is indicating that <?php $sql = ' is being sent as part of the query!!!
0
 

Author Comment

by:DS928
ID: 38860973
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38860976
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38860991
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
 

Author Comment

by:DS928
ID: 38861014
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38861015
Be so kind and mask all the password etc. and show us the config.php file
0
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 500 total points
ID: 38861030
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
 

Author Comment

by:DS928
ID: 38861033
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38861040
Then: is the $tblDetails defined in the config.php file ?
0
 

Author Comment

by:DS928
ID: 38861053
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
 

Author Comment

by:DS928
ID: 38861091
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38861094
$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
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 38861104
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
 

Author Comment

by:DS928
ID: 38861106
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
 

Author Comment

by:DS928
ID: 38861118
Chris, any good links on PDO?
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38861136
I got that points. What's with the other question ? (I think I'll get to it tomorrow - post the link pls) Thanks.
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 38861155
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
 

Author Comment

by:DS928
ID: 38861196
Thank you, Chris.
0
 

Author Comment

by:DS928
ID: 38861207
Roads, The Link.

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

Do a quick cut and paste.  500 points!
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 38861248
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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question