Web Development

Hi all,

I have a bit of an issue, I am working on a bit of a CMDB for a friend, it's to do with real estate.

Anyway, this is my situation.

I have a table which contains all the properties (forsale, sold, etc) in the DB named "property".

Now, this is what I want to achieve, I wish to create a output table ordered by the age of the property in our database. Each row has these important fields "id" (INT auto row id) "pid" (property id) "saved" (datetime of data) "active" (INT 0 or 1) and "name" (VARCHAR).

Now each property (pid) will have multiple rows a new created and date stamped (saved) for every update made to the property in the database.

So, the tech, I need to select only unique properties (pid's) and get their newest and oldest save times (saved), then output:
Their age (oldest "saved" compared to curtime())
Name of property ("name", but coming from the newest save "saved")

AND... only pids with the active flag = 1, should be outputted and I want the output sorted by newest to oldest.

THEN.. if this really was not enough, can I please have only the details for the 25 newest pids (for page one), then in my next query (26-50 newest) so on and so fourth.

Sorry guys, but I really just can't get my head around where to start, so thought I would present to you what I am overall trying to achieve and see if you can help piece things together.

Thanks, big thanks, in advance,
Aaron.

---------- Post updated at 02:11 PM ---------- Previous update was at 12:19 PM ----------

Here is some sample data:

Code:

+--+------+---------+---------+------------------------------+
| id | pid | name  | active |                  saved                             |
+--+------+---------+---------+------------------------------+
| 1  |   1     |    foo      |    1          | 2010-09-01 13:54:30 |
| 2  |   1     |    foz       |    1          | 2010-09-06 14:51:10 |
| 3  |   1     |    foe      |    1          | 2010-09-13 20:01:14 |
| 4  |   2    |    bar       |    1          | 2010-07-04 10:12:55 |
| 5  |   2     |    bar       |    1          | 2010-09-01 14:31:34 |
| 6  |   3     |    zzz     |    0          | 2010-09-16 11:11:27 |
+---+-----+---------+---------+--------------------------------+

OUTPUT SHOULD BE:

Code:

ID       PID       NAME          DAYS-OLD
3        1          foe               15
5         2         bar              74

---------- Post updated at 02:37 PM ---------- Previous update was at 02:11 PM ----------

This is my latest creation....

Code:

SELECT DISTINCT pid, ****name,**** (SELECT max(saved) WHERE pid = 2.pid) FROM property WHERE active = 1 ORDER BY DESC ****NEWEST OF THE FIRST RECORDS****;

where:
saved is my datestamped field.
name is my records data.
pid is the distinct id.


Problem that I see is this, if I go ORDER BY DESC saved;

then, this will just print them in order of the records "newest" saved datestamp (where I want the name from) but not the records "oldest" saved timestamp.

Code:

SELECT DISTINCT pid, *****name,***** (SELECT max(saved) WHERE pid = 2.pid) FROM property WHERE active = 1 ORDER BY DESC (SELECT min(saved) WHERE pid = 2.pid);

Is this possible...?
and on top of this I still can't see I extract my other data like name...

Sometimes it is easier to simply read the table into an array and perform array operations after the MySQL query versus trying to do all the processing with a MySQL query.

I advise you to simplify the query, read the data into an array, and get the data you need by operating on the array, presumably in PHP.

Are you programming this in PHP?

How about this:

Code:

select id, pid, name (SELECT max(saved) WHERE pid = 2.pid) AS max_date, (SELECT min(saved) WHERE pid = 2.pid) as min_date  
	from property property1
	where saved = (select max(saved) from property where pid = property1.pid)
	ORDER BY max_date;

Does this sound better...?

Bear in mind this is from an oracle guru...
Anyway, will check this out when I get home and have a db to play around with.

@ NEO
I am sure I could do that, but really wanted to package it neatly into a query.
Programming is being done in PERL

Hi Stoie,

I understand completely!

IMHO, the query is 'neater' if smaller and you simply read into an array and use PERL to organize and extract the data you need.

Of course, you can program anyway you like!

I can tell you, from experience with a busy forum driven by PHP and MySQL, that complex MySQL queries cause performance (slow query) problems, are hard to debug, and generally do not perform well. Here, where we do millions and millions of MySQL queries a day, the only queries that cause problems (slow queries) are those written by programmers who embed the logic in the actual query and not in the PHP processing. Based on years of experience with MySQL in these forums, I like simple queries and logic in actual code... just me of course.

Hmmm.

Well that definitely gets me thinking, from everything I have been taught is do as much in the query as possible, "your database will always be faster than your cgi".

So, this is pretty much the main reasoning for doing so.

Anyway, we will see how this goes...

We have billions of MySQL queries per month in these forums.

When the logic is in PHP, all is generally well. When the DB logic is complex, we tend to have problems.

There is a huge difference in theory and practice.

Generally speaking, keeping thing simple and stupid (the KISS principle) wins over complexity and clever most every time.

Of course, "to each his own" as they say. I am only talking from my experience of running a lot of third party MySQL/PHP code and what I find best and easiest to debug when a problem arises.