Page 1 of 1

Concatinate GROUP BY/ORDER BY

Posted: Mon May 23, 2011 7:54 pm
by cjkeane
Hi Everyone.

I have an issue which i hope someone can help me with. I have 5 dropdown lists which i assign variables to ($DataDisplay_1, $DataDisplay_2, etc. I have another 5 dropdown lists which i assign other variables to ($GroupSort_1, GroupSort_2, etc) in order to sort/group those fields. I'm running into difficulty when multiple sorting/grouping values are selected. I've included an example of one of the option groups i'm using for the sort dropdown list.

When sorts are performed on two or more fields, the query displays:
SELECT NameFirst_1 AS Column1, NameLast_1 AS Column2 FROM records ORDER BY NameFirst_1 ASC, ORDER BY NameLast_1 DESC

Based on the option group values, the query displays the values I've assigned. however, the sql generated clearly isn't correct.
Anyone have any ideas on how to change either the dropdown list values or change the way i join them all together in the query?

Code: Select all

<select name="GroupSort_1" >
<option value="" selected="selected"></option>
<option value="ORDER BY <?php echo $DataDisplay_1 ?> ASC"<?php if($GroupSort_1=="ORDER BY $DataDisplay_1 ASC") { echo " selected";} ?>>Sort Ascending</option>
<option value="ORDER BY <?php echo $DataDisplay_1 ?> DESC"<?php if($GroupSort_1=="ORDER BY $DataDisplay_1 DESC") { echo " selected";} ?>>Sort Descending</option>
<option value="GROUP BY <?php echo $DataDisplay_1 ?> ORDER BY <?php echo $DataDisplay_1 ?> ASC" <?php if($GroupSort_1=="GROUP BY $DataDisplay_1 ORDER BY $DataDisplay_1 ASC") { echo " selected";} ?>>Group Ascending</option>
<option value="GROUP BY <?php echo $DataDisplay_1 ?> ORDER BY <?php echo $DataDisplay_1 ?> DESC" <?php if($GroupSort_1=="GROUP BY $DataDisplay_1 ORDER BY $DataDisplay_1 DESC") { echo " selected";} ?>>Group Descending</option>
</select>

<?php
// run/display data
if(isset($_POST['Run'])) {
	// select fields
	$strSELECT[] = "";
	if(!empty($DataDisplay_1)) {$strSELECT[] = $DataDisplay_1 . " AS Column1";}
	if(!empty($DataDisplay_2)) {$strSELECT[] = ", ". $DataDisplay_2 . " AS Column2";}
	if(!empty($DataDisplay_3)) {$strSELECT[] = ", " . $DataDisplay_3 . " AS Column3";}
	if(!empty($DataDisplay_4)) {$strSELECT[] = ", " . $DataDisplay_4 . " AS Column4";}
	if(!empty($DataDisplay_5)) {$strSELECT[] = ", " . $DataDisplay_5 . " AS Column5";}
		
	// apply sorting/grouping
	$strSortGroup[] = "";
	if(!empty($GroupSort_1)) {$strSortGroup[] = $GroupSort_1;}
	if(!empty($GroupSort_2)) {$strSortGroup[] = ", ". $GroupSort_2;}
	if(!empty($GroupSort_3)) {$strSortGroup[] = ", " . $GroupSort_3;}
	if(!empty($GroupSort_4)) {$strSortGroup[] = ", " . $GroupSort_4;}
	if(!empty($GroupSort_5)) {$strSortGroup[] = ", " . $GroupSort_5;}

	// join strings and compose select statement
	$strJOIN = " " . join('', $strSELECT);
	$strJOIN2 = " " . join('', $strSortGroup);
	$string = "SELECT " . $strJOIN . "  FROM records " . $strJOIN2 ;
	$query = mysql_query($string) ;
	$result = mysql_fetch_array($query);
	echo $string ;
	
	echo "<table class='sortable' border='0' cellpadding='5'>";
    echo "<tr> <th nowrap>$DataDisplay_1</th> <th nowrap>$DataDisplay_2</th><th nowrap>$DataDisplay_3</th><th nowrap>$DataDisplay_4</th><th nowrap>$DataDisplay_5</th></tr>";

	if($result==true)
	{ 
	do 
		{ 
		
		echo "<tr>";
		echo '<td nowrap>' . $result['Column1'] . '</td>';
		echo '<td nowrap>' . $result['Column2'] . '</td>';
		echo '<td nowrap>' . $result['Column3'] . '</td>';
		echo '<td nowrap>' . $result['Column4'] . '</td>';
		echo '<td nowrap>' . $result['Column5'] . '</td>';
		echo "</tr>"; 
		}
		while($result = mysql_fetch_array($query));
	}                
		 // close table>
		 echo "</table><hr>"; 
}
?>

Re: Concatinate GROUP BY/ORDER BY

Posted: Tue May 24, 2011 1:30 pm
by Jade
The problem is that you're repeating the GROUP BY and ORDER BY keywords. The correct syntax is SELECT [fields] FROM [table] WHERE [conditions] GROUP BY [fields] ORDER BY [fields].

Re: Concatinate GROUP BY/ORDER BY

Posted: Tue May 24, 2011 2:24 pm
by cjkeane
I know what the correct syntax is.
i can write the query as
$string = "SELECT " . $strJOIN . " FROM records GROUP BY " . $strJOIN2 " ORDER BY " . $strJOIN2;
but if just the ASC or DESC choices are chosen from any of the $DataDisplay dropdown lists, the query displayed would appear as:
SELECT [fieldname] FROM records GROUP BY [fieldname] ORDER BY [fieldname]
but in that case, no group by needed.

if only one $DataDisplay dropdown list is chosen, there are no issues.

Issues only arise when selecting multiple sorts or group by options from the $DataDisplay lists. i hope this is clearer.

Re: Concatinate GROUP BY/ORDER BY

Posted: Wed Jun 01, 2011 4:53 pm
by Jade
No sorry that's not any clearer. If you don't need the GROUP BY option then don't include it in your query. If you want to group multiple options then you need to separate them by a comma not by repeating the word GROUP BY. Your current code includes the word GROUP BY for each item you want to add to your group. Your code is doing:

Code: Select all

SELECT * FROM table GROUP BY field1 GROUP BY field2 GROUP BY field3 ORDER BY field1 ORDER BY field2 ORDER BY field3
It SHOULD be doing:

Code: Select all

SELECT * FROM table GROUP BY field1, field2, field3 ORDER BY field1, field2, field3