Concatinate GROUP BY/ORDER BY
Posted: Mon May 23, 2011 7:54 pm
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?
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>";
}
?>