Avatar of Robert Granlund
Robert Granlund
Flag for United States of America asked on

Query Issue

I what the php to print this form and have the selections be in a specific order.  However, the following php prints the options in a random order.  The "Other" option, always seems to be printed as the second or third choice instead of the last choice.  Any ideas?

<?php
	$q = "SELECT exp_weblog_data.entry_id, exp_weblog_titles.entry_id, exp_weblog_data.field_id_308, exp_weblog_titles.entry_date, exp_weblog_data.field_id_195 FROM exp_weblog_data, exp_weblog_titles WHERE (exp_weblog_data.field_id_308 IS NOT NULL and length(exp_weblog_data.field_id_308) >= 3) AND exp_weblog_data.entry_id=exp_weblog_titles.entry_id AND exp_weblog_data.field_id_195='ARCHIVE' GROUP BY exp_weblog_data.field_id_308 ORDER BY
CASE
WHEN exp_weblog_data.field_id_308='red' THEN 1
WHEN exp_weblog_data.field_id_308='white' THEN 2
WHEN exp_weblog_data.field_id_308='bluer' THEN 3
WHEN exp_weblog_data.field_id_308='other' THEN 4 END";
	$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
	if (mysqli_num_rows($r)) {  //  MAIN IF  //
?>

<form method="GET" action="MY ACTION/">
<h5>SEARCH BY COLOR</h5> <select name="guitar" style="width:100px;border:solid 1px #717171;background-color:#343333;color:#717171;">
<?php
		while ($row=mysqli_fetch_assoc($r)) { //  WHILE  
			$guitar = $row['field_id_308'];


				if($guitar=="red") { 
					echo "<option name=\"guitar\" value=\"red\">red</option>";
				}
		
				if($guitar=="white") { 
					echo "<option name=\"guitar\" value=\"white\">white</option>";
				}
		
				if($guitar=="blue") { 
					echo "<option name=\"guitar\" value=\"blue\">blue</option>";
				}
		
		
				if($guitar=="Other") { 
				echo "<option name=\"guitar\" value=\"Other\">Other</option>";
				}

			}  //  END WHILE
		}
?>
</select>
<br />
<input type="submit" name="submit" value="go" />
</form>

Open in new window

PHPHTML

Avatar of undefined
Last Comment
Francisco Igor

8/22/2022 - Mon
haloexpertsexchange

in your sql case statement on line 6 you have

WHEN exp_weblog_data.field_id_308='bluer' THEN 3

did you mean 'blue' instead of 'bluer' if so that could cause some unexpected actions.
Robert Granlund

ASKER
no, that is just a typo here, not in the script.
haloexpertsexchange

what happens when you take out the case statement?
Normally what happens that you will get things ordered in the order that they were entered into the database but I have never seen an order by statement like yours before.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Francisco Igor

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.