[eluser]jedd[/eluser]
Hi Tim,
[quote author="tim1965" date="1251827052"]
I now need to add search capability for master_charges (see schema above), Which holds one row per id, but with 10 sets of repeating fields (start date, end date, price_per_week) with 1 thru 10 appended to the field name to differentiate them. I need to essentially group id, start-date1, end_date1, price_per_week1 into a row (so 10 rows per id) to allow me to properly search against all 3 crieria at the same time and iteriate over the rows by id.
So i have extracted these 4 fields from master_charges into a temp table via unions (see UNION query above), which generates a 4 column table (id, start-date1, end_date1, price_per_week1) , with 10 rows per id. So i now only have to search on 4 fields by id. However my problem is i cannot incorporate this into my original query.
[/quote]
Okay, that's cleared up what's happening, and kind of describes what's led you to this point.
I really reckon your best bet really is to clean up your schema. I understand this is non-trivial. If the current problem is the absolutely last thing you need to resolve before the entire project is finished, then, yeah, I'd concede that it's better to push through with this approach .. but if you're going to do more work / maintenance on the thing, you'll be buying yourself a whole lotta love later, if you do some re-design now.
Ack'ing that you're not about to redesign, no matter how sensible that step is .. here's some brain farts.
Going back one step, do you need to union these things in the first place? Sure, the query would be longer, but you can always select/OR against a given date and 10 x fields in one query. I'm kind of guessing that you're not too fussed about performance (given the schema design, and the consecutive non-trivial queries you're looking at here already).
Would views work better for what you're trying to do? I've not used them much at all, and to be candid I'm still having trouble wrapping my head around your design and possible solutions - but maybe a few minutes reading the MySQL doco about views (given you obviously have a much better understanding of your tables than I do) might provide an alternative?
Finally, don't be afraid to iterate your queries from the PHP side. Everyone thinks it's hugely expensive - and of course it usually is more expensive than one very long SQL query - but to reduce complexity, and for infrequently executed SQL queries, it's worth experimenting with a more 'programmatic' approach .. even if only to do some algorithm honing and performance comparisons.
