Solved

Dropdown Box Groups

Posted on 2013-02-06
31
375 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

770 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