SQL query a little bit off topic

[eluser]tim1965[/eluser]
Hi

This is probably a non CI related post but i thought i would post it here for help as this is such a great community for help.
I am struggling with a query i need to put together for searching a range of dates and prices. I already have an existing query that works great but i need to incorporate this functionality and i am going round in circles.
My existing query looks like this
$sql = "SELECT WHATEVER
FROM master_reference
JOIN master_details ON master_reference.id = master_details.id
JOIN master_pictures ON master_reference.id = master_pictures.id and master_pictures.photo_desc = 'main'
JOIN master_charges ON master_reference.id = master_charges.id
WHERE completed ='1'
AND master_details.region LIKE '$region'
AND master_details.country LIKE '$country'";
$query = $this->db->query($sql);
if($query->num_rows() >0)
{
$row=$query->result_array();
return $row;
}
I now want to add the ability to search for a price between a start and end date from this table
See next post
CREATE TABLE `master_charges` (
`id` mediumint(20) NOT NULL,
`chargesdatecreated` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
`start_date1` date default NULL,
`start_date2` date default NULL,
`start_date3` date default NULL,
`start_date4` date default NULL,
`start_date5` date default NULL,
`start_date6` date default NULL,
`start_date7` date default NULL,
`start_date8` date default NULL,
`start_date9` date default NULL,
`start_date10` date default NULL,
`end_date1` date default NULL,
`end_date2` date default NULL,
`end_date3` date default NULL,
`end_date4` date default NULL,
`end_date5` date default NULL,
`end_date6` date default NULL,
`end_date7` date default NULL,
`end_date8` date default NULL,
`end_date9` date default NULL,
`end_date10` date default NULL,
`price_per_week_1` smallint(5) default NULL,
`price_per_week_2` smallint(5) default NULL,
`price_per_week_3` smallint(5) default NULL,
`price_per_week_4` smallint(5) default NULL,
`price_per_week_5` smallint(5) default NULL,
`price_per_week_6` smallint(5) default NULL,
`price_per_week_7` smallint(5) default NULL,
`price_per_week_8` smallint(5) default NULL,
`price_per_week_9` smallint(5) default NULL,
`price_per_week_10` smallint(5) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
So i need to search the start and end date in the table and compare to the POST values and then check the price_per_week value is higher than the POST price per week value. However i need to check all 10 rows (all have different field names). I can run a UNION to flatten this out into a temp table whereby all 10 rows have the same coluumn names. But i cannot figure out how to merge the UNION (see below) into the above query to get i to work.
See next post

[eluser]tim1965[/eluser]
My UNION query looks like this
(
select start_date1,end_date1,price_per_week_1,id
from master_charges
where master_charges.id = 8
)
union
(
select start_date2,end_date2,price_per_week_2,id
from master_charges
where master_charges.id = 8
)
union
(
select start_date3,end_date3,price_per_week_3,id
from master_charges
where master_charges.id = 8
)
union
(
select start_date4,end_date4,price_per_week_4,id
from master_charges
where master_charges.id = 8
)
union
(
select start_date5,end_date5,price_per_week_5,id
from master_charges
where master_charges.id = 8
)
union
(
select start_date6,end_date6,price_per_week_6,id
from master_charges
where master_charges.id = 8
)
union
(
select start_date7,end_date7,price_per_week_7,id
from master_charges
where master_charges.id = 8
)
union
(
select start_date8,end_date8,price_per_week_8,id
from master_charges
where master_charges.id = 8
)
union
(
select start_date9,end_date9,price_per_week_9,id
from master_charges
where master_charges.id = 8
)
union
(
select start_date10,end_date10,price_per_week_10,id
from master_charges
where master_charges.id = 8
)



) as temp_charges_search_table
The complexity of this is the original query returns one row of joined tables, but this table has essentially 10 rows of diff column names, all of which need searching.
This probably all needs to be executed as one statement, but i am completely open to suggestions if my temp table approach is incorrect. However the master_charges table is bigger than the fields i have given you so i dont want to amend the underlying structure of this table if possible as would generate a lot of extra work.
So to recap i need to search across all 10 start, end dates, and price per week as well as the original query)in one query (i think) to find a match based on a user selection for either dates and amount or amount only.
Apologies again for off topic and hope this is clear, and thanks in advance (will also post this in a SQL forum as well). Thnx again.

[eluser]jedd[/eluser]
Hi tim,

Here's a question for you - do you think it's better, when seeking help about constructing very complex SQL queries involving many tables, to a) fully describe your schema, or b) not.

Sure, genuinely competent people could probably look at the various SQL queries you've come up with - which by your own admission don't quite do what you want - and reverse engineer the source tables that you're talking about. Sadly, for both of us, I clearly don't fit within that category.

Btw, [ code ] starts a block and [ /code ] ends a block.

EDIT: From the description of your problems and the one master table described, it looks like your database design needs a re-visit. I know you said you didn't want to hear that, but them's the breaks.

[eluser]jedd[/eluser]

However i need to check all 10 rows (all have different field names).

Btw, when you talk about rows here, do you mean columns?

Otherwise I'm not sure how different rows within a table can have different fields.

[eluser]alboyd[/eluser]
[quote author="jedd" date="1251826555"]

However i need to check all 10 rows (all have different field names).

Btw, when you talk about rows here, do you mean columns?

Otherwise I'm not sure how different rows within a table can have different fields.[/quote]

For such a nice helpful guy you do come across a little abrupt at times!

[eluser]tim1965[/eluser]
Hi Jedd
Apologies i thought i had explained clearly what the issue is and given the sub set of the schema for the table that was causing th issue, but having read for the 4th time its not very clear. So will happily attempt again to describe the issue.
I have a number of tables all linked by id, these are master_reference, master_details,master_pictures as per the existing search query above (these are all large tables of columns, so once again the original search query is a cut down of the important parts). These all contain one row per id. On my existing search i join these by id (still one row per id) and search across based on lots of AND criteria. This works great currently.
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.
I hope this is clear and thnx for your response.

[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.

[eluser]alboyd[/eluser]
Perhaps you need to break it out by first querying the master_charges table and getting all the values into your model.

Then transpose the results into an array where which would like this:

Code:

$master_charges[1][] = array('id' => id, 'start_date' => start_date_value, 'end_date' => end_date_value, 'price' => price_value)

There will be 10 $master_charges. You know what I have no idea where to go from here I just confused myself! As I'm not 100% clear still what you need to do with this - but by cycling through this array you should be able to create a list of row id's which you can then pass into your other query?? farked if i know man...

[eluser]jedd[/eluser]
alboyd - first, tim's a product of the Decade Of Lurv, so I'm sure he politely ignores my rare slightly sarcastic comments, and further, doesn't misinterpret my occasional dalliances with brevity as rudeness.

Secondly, great idea! I meant to ask - Tim - are you talking about bucketloads of data here, aggregating entire tables, or are you typically working with a metric screenful of data at a time?

Pulling a lump of data into a PHP array is a great idea if it's a manageable lump of data and the SQL equivalent is just getting too messy (I count at least 1 out of 2 criteria satisfied there).

[eluser]tim1965[/eluser]
Guys

Thanks for the ideas. I looked at arrays and binned that idea due to large amounts of data,same with views. I´ll go back and look at the and/or as surprisingly i haven´t considered this, due to brian farts around schemas. I will also look at what the damage woudl be on breaking the schema. Thnx again i needed some fresh input at this point(As you can tell from the confusing nature of my first two posts). Feel the virtual hug(obviously in a manly way) from the decade of Luv!!
Thnx