Inteligent query, howto?

Elhombrebala

I have a table with a list of apartments and their prices to be book divided in weeks for every month:
jan1, jan2, jan3, jan4, jan5, feb1...
This weeks they have a price:
jan1=233, etc...
I would like to search for the 6 cheaper weeks of every apartment, but in different cities. For example:
If the 6 cheaper prices are all in the same city, it should change and find another 5 in different locations.
I'm trying to develope a logic system but I only get bullshit!
Any hints?

Merry Christmas to everyone!

Pig

I'm unclear on your db structure. Can you post up a couple example rows?

Elhombrebala

Last day I wrote a query that was unclear.
Here I give more feedback about the problem:

I have a table like this:

Ref |jan1 |jan2 |jan3 |jan4 |jan5 |feb1|feb2|feb3|feb4|

Sit234 |100 |134 |154 |678 |100 |134 |154 |678 |789 |

Ben334 |122 |233 |145 |100 |134 |154 |678 |100 |134 |

In the table I have a list of apartments, the apartments are named under a reference number or code, and the prices are in weeks. I have developed a table which has 5 weeks for everymonth and everyweek has a price. (Some of them are 0 because the month is shorter than 5 weeks) I want to make a query that shows the 6 cheapest prices of July and August.
(jul1, jul2, jul3, jul4, jul5 and aug1, aug 2...)
How can I do that??

leatherback

Hi,

Start off with redesigning your database!

make just one colum for the week, one for app and one for price:
week | App | Price | location
1 | sit234 | 2334 | Netherlands
1 | ben334 | 2365 | USA
etc

Or better: Use numbers: 1-..num of app, and 1-53 weeks. THen use a lookup table for the actual names: This will save a lot of space if you have a lot of app.

Then say..

Select * from appartments order by prices desc limit 5

Where you may have to loop through the data a bit if you only want different locations. So select one, and then get the next app, where location <> row[location] or something.

Elhombrebala

I realize changing database can make the things easier but the problem us that changing database is imposible right now!

leatherback

Hi,

Hm.. THen I am afrais that what you are trying to can only result in a lot of messy coding!
SQL was made to post query across records, not across colums. So you can never select the cheapest week for a certain appartment, without selecting one apartment, and placing it into an array or something, and then sorting the array.

you'd end up with a system like this: (UNTESTED!):

$appartment = mysql_query("select 8 from table where ref = 'Sit234'"); 
// this will give you one record, with 52 or 53 items?
while($row=(mysql_fetch_array($appartment))
  {
   for($i=0, $<count($appartment)
     {
      $ThisArr[] = $row[$i];
     }
  }
sort($ThisArr);
$lowest = $ThisArr[0];

ultraslacker

Changing a design is not impossible, but perhaps technically imposing and resource demanding. But if it is easier to keep the design, then you are stuck with ugly queries.