MySQL DISTINCT & TRIM problem

[dennismonsewicz]

I am trying to use DISTINCT and TRIM at the same time with a MySQL Query and it is borking out on me..

 

EX: "SELECT DISTINCT TRIM(col_1) FROM tbl"

 

I get no mysql errors when I run the code just no results

[PFMaBiSmAd]

There's nothing technically wrong with that query. It would taking seeing your code from the point where you form the query though to the code that is determining that there is no results in order to determine what is wrong. Also, post a sample of your data that you expect the query to return.

[dennismonsewicz]

here is my query:

$man_db = mysql_query("SELECT DISTINCT TRIM(manufacturer) FROM portal_deductible WHERE carrier = 'carrier2'")or die(mysql_error());

 

My results are being populated like so:

 

<select name="manufacturer" onchange="this.form.submit();">
	<option>-- Select Manufacturer --</option>
	<?php
		while($row = mysql_fetch_object($man_db)) {
			if($_POST['manufacturer'] == $row->manufacturer) {
				$s = " SELECTED";
			} else {
				$s = "";
			}
			echo '<option value="' . $row->manufacturer . '" ' . $s . '>' . $row->manufacturer . '</option>';
		}
	?>
</select>

 

I don't get a mysql error or anything, just no results are displayed

[PFMaBiSmAd]

If you do a "view source" of the page in your browser, you will likely see a php error that $row->manufacturer does not exist.

 

The expression you put into the SELECT term in a query is exactly what you get in the result set. You would actually get something like $row->TRIM(manufacturer), but since that is not valid object notation, you would not be able to access the value unless you use an alias name in the query.

 

Use the following -

 

"SELECT DISTINCT TRIM(manufacturer) as manufacturer FROM portal_deductible WHERE carrier = 'carrier2'"

 

You should also check how many rows there are in the result set and take an appropriate action, like displaying a user message "There are no matching entries" instead of displaying an empty select menu.