DISTINCT Column with similarities

Using DISTINCT clause in SQL query with column fields that are no exact

[coyoteRick]

Howdy,

I've been pullin' out my hair trying to figure out how to accomplish something. Let me explain as concisely as possible.

Using: HTML/PHP/mySQL

The problem lies in presenting a Vehicle Inventory Search Form, created with HTML, using <select> "option" values that are being populated via a PHP loop that source the "Model" column in the inventory table like so:

##SQL Statement##
$quer = mysql_query("SELECT DISTINCT Model FROM inventory ORDER BY Model")

##Population of select's <option>##
while($row = mysql_fetch_array($quer)) {
echo "<option value=\"$row[Model]\">$row[Model]</option>";
}

This is great and all, vehicle models are combined to distinct values, allowing the user to correctly search by unique models. Unfortunately, some vehicle models are similar but different. For example:

Accord EX Coupe 2D
Accord EX Sedan 4D
Accord LX Coupe 4D
Accord LX Sedan 4D
Accord SE Sedan 4D

I would like to take it further and combine vehicle models that are distinct but similar. Essentially, I would love to have all of the above models be present as simply: Accord

It would be ideal to accomplish this within the SQL statement, but unfortunately, my experience here is less so than I would like.

If anyone could direct me down a proper path to accomplishing this, I would be greatly appreciative. Also, if any further details are required to help me, I would be much obliged to provide.

Thanks in advance!

[LifeinAsia]

It sounds to me like it is more of a database design issue than a SQL issue. If the DB doesn't already have it, make a field for "Make" and try to parse it out (or populat it manually). Then just select the Make field instead of the Model field.

Usually better to do all the parsing once instead of each time the query is run.

Actually, it might be better DB design to have a separate Make table instead of adding an extra field.

[coyoteRick]

Thank you much, LifeinAsia, I appreciate your input.

I finally developed a workaround that's quite a different alternative than redesigning the DB structure and/or changing the parsing method prior to inserting the feed into the database.

Instead of condensing the models into broad model categories, I've opted to show "suggested" search results below the original search results. That way, if only one model is the result of a search, I have another SQL statement using a LIKE clause with wildcards for both the make and model.

Sheesh, confusing stuff, I tell you. Just when I think things get easier, the just end up getting a lot more complicated.

Cheers! And thanks again for the input.

... I'll be back. Heh