Solved

Dropdown Box Groups

Posted on 2013-02-06
31
370 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
  • 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
 

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 42

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 42

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 42

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 42

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now