Limiting from a mySQL select statement

Anon

Hi,

I currently have a table that stores wine names and I want to try to remove the first 4 (+ one whitespace = 5) characters from the products_name field (eg: 1999 Cabernet Sauvignon - remove the 1999 ). The reason for this is that it contains the vintage date and I wish to sort by products_name WITHOUT vintage date being the sort criteria. Thusly, I think that it has to be removed in the sql statement itself. I was thinking of using something similar to LEFT() AS shortened_name or RIGHT() shortened_name but am not sure what the actual statement is. Here is my statement so far:

select $f_db, p.products_id, p.products_name, p.products_model, m.manufacturers_name, m.manufacturers_location, p.products_price where p.products_status = '1' and $f_db = '" . $sort ."' and p.products_id = p2m.products_id and p2m.manufacturers_id = m.manufacturers_id
and p.products_id = p2c.products_id and p2c.categories_id = " . $current_category_id . " order by $vert_order

Thanks,
Jacob

jcarver

Hello,

This is much more a database problem than a PHP problem. That field breaks a key rule of good database design called "multi-part fields". The vintage date and the name of the wine should be stored in different fields. The reason is exactly why you are having problems, wanting to sort and use the data differently can really cause problems later on.

So my advice is to add a new Vintage field and use a substr statement to populate the new fields. Then this problem all but goes away...

You'll curse me now but thank me later 🙂.

Jason

Anon

The database was designed properly (there IS a vintage field). The problem was with the raw data and the data entry personnel... they entered all the data before I noticed it contained vintage information (was away on vacation). Maybe I'll just script something to remove vintage from product_name and be done with it.

Anon

You could seperate all the words in the name quickly using explode ie
$array = explode($vintagename, " "); this will create an array of all strings that are delimited by the whitespace.

Jason is right, Not only should they be seperated in the database, but the user should be confined to input the data in the desired format. I am not a very acomplished programmer and I run into this kind of problem alot myself.

Hope this will help you clean things up.

Alfred