I've been trying to figure this out for a few days now so would appreciate a nudge in the right direction for solving it I have a select/option dropdown populated with colours from a database, each colour has a unique ID.

    <form method="get" action="index.php" enctype="multipart/form-data">
         <select name="colour" id="colour">
            <option value="0">Search By Colour</option>
            <option value="1">Blue</option>
            <option value="2">Green</option>
            <option value="3">Red</option>
          </select>
     </form>

The search query works as follows:

if ( isset($_GET['colour'] )
{
$query="SELECT C.*, I.* FROM colour_ids AS C INNER JOIN products_inventory AS I ON C.productId=I.productId where C.Colour_Id=".intval($_GET['colour'])." GROUP BY C.productId";
}

If the URL contains a colour ID it displays all products of that color.

The above works great, but what I would like to do is pass the colour name to the URL, instead of the ID. When I change the form as follows:

<option value="Blue">Blue</option>

The colour name is successfully passed to the URL, however the search fails because the query is looking for an ID in isset($_GET['colour']) - bold in above query.

I believe I have to pass the value as value="{NAME}" (like above), instead of using {ID}. If so, just prior to the query executing how do I go about changing {NAME} back to its equivelent {ID} for the query to be successful?

Any pointers or help appreciated Thanks (above code clipped for easier reading)

Would this work?

WHERE C.Color_Name LIKE '" . mysql_real_escape_string($_GET['colour']) . "'

What you suggested is essentially what I believed to be the solution, only the Colour_Names are stored in a seperate table and I don't understand how I can use a Join within the same query. The DB format is like below:

colour_ids
| ID | Product_ID | Colour_ID
----------------------
| 1 | 10123 | 3
| 2 | 10123 | 6
| 3 | 10124 | 3
| 4 | 10124 | 3

colours
| ID | Colour_Name
----------------------
| 1 | Blue
| 2 | Green
| 3 | Red

$query="SELECT O.*, C.*, I.* FROM colours AS O INNER JOIN colour_ids AS C ON O.Id=C.Colour_Id Inner Join products_inventory AS I ON C.productId=I.productId WHERE O.Name=".mysql_real_escape_string($_GET['colour'])." GROUP BY C.productId";

The above doesn't work, though am I on the right track in using another Join?

Out of curiosity, how does that one works ?

PHP Code:

$colour=mysql_real_escape_string($_GET['colour']);
$query=<<<SQL
select *
from products_inventory
where Product_ID in (
  select i.Product_ID
  from products_inventory i
    inner join colour_ids cid
      on cid.Product_ID=i.Product_ID
    inner join colours c
      on c.ID=cid.Colour_ID
      and c.colour_Name='{$colour}'
    group by i.Product_ID
)
SQL;