Query Issue

Robert Granlund
Robert Granlund used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
haloexpertsexchange

Commented:
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

Author

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

Commented:
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.
Developer
Commented:
Check the data if the field values are the same you use in the sql and PHP conditions

"other" or "Other" are trated as different.

Check the values returned by the folowing query:

SELECT exp_weblog_data.field_id_308,
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 as ordernum
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


And check if the "ordernum" column returned has the values you are expecting:
red | 1
white | 2
blue | 3
other | 4



and check the condition

if($guitar=="Other") {  ...

that may be

if($guitar=="other") {  ...


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial