I am trying to query a table which has a column called "filename".
The actual filename is made up of "productcode-category.jpg"
If i have the product code posted to a php page, how could i then take the value and search in the filename column where it equals the product code?
It would be great if it wasnt case sensitive as well.
mysql searches are case insensitive by default. You have to specifically request case sensitivty by doing SELECT ... WHERE BINARY somefield = 'CaseSEnSITive'. As for searching as you want, you could do:
SELECT blah, blah FROM table WHERE filename LIKE '%product_code_here%'
But this could produce many false positives if your product codes are variable length (e.g. a product has code '1' and many others have code '11', '12', '13', etc...). You would save yourself hassle if you created a seperate product code field and could do direct comparisons on that, rather than a substring match as the 'LIKE' comparison does.
Don't you have the product code defined in the same table as the one which has the "filename" column ? Can you show us your tables ?
Case sensitivity is most probably defined in your database. (see your charset : ex latin1_swedish_ci : the 'ci' means case insensitive). This is the default value, you may want to override this.
It sounds like your table structure may be a little less than efficient if you want to do product code lookups by parsing a filename. However, you probably want a query something like this:
SELECT filename FROM mytable WHERE filename LIKE 'somestring-%';
If you stored your product codes and categories in separate columns, the query could be much faster:
SELECT filename FROM mytable WHERE product_code = 'somestring';
You could use something like:
$sql = 'select * from table where strtoupper(filename) like "'.strtoupper($productcode).'%";