Re: Need help with Select statement in Perl
Well, this is a basic db design problem. The way your data is stored, you'll need to either use the LIKE thing, or maybe a RegExp. The problem in doing that is that your query won't be able to use any index. Depending on the size of your db, that could take a long time.
Your Table_Items has at least the following:
Table_Items.Title
Table_Items.Image
Table_Items.ItemNumbers
You likely have some sort of auto-increment id as well (Table_Items.ItemsId). It sounds like a lot of work, but it will probably payoff to create another table as a child of Table_Items, say Table_Items_Details that would carry the unique id from Table_Items (Table_Itemd.id) and a single ItemNumber that can be indexed. Something like this:
Table_Items_Details.Id
Table_Items_Details.ItemsId
Table_Items_Details.ItemNumber
Table_Items_Details.Option (color, size, etc)
It's more data than you are storing now, but will also give you more detail about the customer's purchases/interests. Your select statement would then be something like this:
SELECT Table$User.baseNum,
Table$User.Specs,
Table$User.PricePaid,
Table_Items.Title,
Table_Items.Image
FROM Table$User
JOIN Table_Items_Details ON Table_Items_Details.ItemNumber =
Table$User.OrderNumber
JOIN Table_Items ON Table_Items.ItemId = Table_Items_Details.ItemId
If you have indexes on Table_Items_Details.ItemNumber, Table$User.OrderNumber, Table_Items.ItemId and Table_Items_Details.ItemId it should be fully optimizable.
The thing is you have more than one piece of information in the ItemNumbers column, which means you (mySql) have to do more work to get the data back out--using the "LIKE" operator has a significant cost in a large database.
One other comment. Do you seriously maintain a separate table for each user's purchase history? That must really make it hard to pull sales stats totals.
Depending on the number of other applications you have developed that use the current structure, it's probably well worth your effort to fix the db design by normalizing your forms now. It's just going to get worse later on.