MYSQL or SQL query experts needed in here now please!


Author Reply
peej
Flag
Posts:14637
Comments:4691
Thread Kills:462(3%)
AATG Pts:400
Star Rating
Gold Medal
Right, here's the scenario.

I've got two tables in a database...

One uses an auto-increment index number, then a bunch of other fields for info

The other contains dates, and a foreign key to match the other table's auto increment index number.

Now, what I wanna do is display the first table's results in blocks of ten (using the good old "do" loop type stuff in PHP) but I also want to display the latest entry from the date table next to this too. I've tried joins and stuff, and that just prints all of the dates rather than the top ones. I've tried linking the index and foreign key data together but again no go.

So it sorta looks like this at the mo:

SELECT * FROM table1
ORDER BY indexnumber DESC

What I want it to do is something like

SELECT * FROM table1
AND latestdate FROM table2
ORDER BY indexnumberDESC

Alas, I cannot seem to do this. Any whizzy way I could do it?

Peej
#1 at 18:08:39 - 13/04/2007
FairgroundTown
Flag
Posts:44
Comments:3
Thread Kills:6(14%)
AATG Pts:0
Star Rating
SELECT field1, field2, Max(DateField)
FROM table1 INNER JOIN table2 ON primary_key = foreign_key
GROUP BY field1, field2

This will give you just the TOP date for each record in table1
#2 at 18:11:14 - 13/04/2007
peej
Flag
Posts:14637
Comments:4691
Thread Kills:462(3%)
AATG Pts:400
Star Rating
Gold Medal
Cheers dude, I'll give that a try.

I think the problem may be partially related to the fact that the muppet that set the database up has the dates IN TEXT FORMAT as a varchar, in the format DD/MM/YYYY which doesn't exactly help.

Peej
#3 at 18:17:38 - 13/04/2007
eviltobz
Flag
Posts:304
Comments:68
Thread Kills:15(5%)
AATG Pts:80
Star Rating
Bronze Medal
heh, gotta love people sticking eny type of data in a text field. fuckin cunts! it's people like that who've caused me the nightmare i have to deal with every day :(

aaaanyways, you can convert data types when you need to. in sql server it's something like:

cast(stupidfuckingstring as datetime)

there's also a way to do it with a differnt syntax and a keyword called convert, not sure if either of these will work directly in mysql or not but i'm sure you can take it from there.
#4 at 18:32:12 - 13/04/2007
peej
Flag
Posts:14637
Comments:4691
Thread Kills:462(3%)
AATG Pts:400
Star Rating
Gold Medal
Worked a treat btw FGT, muchas gracias!

Now all I've got to do is make page after page of catalogue data look "prettier"

Peej
#5 at 14:54:02 - 16/04/2007
Alastair
Flag
Posts:330
Comments:51
Thread Kills:11(3%)
AATG Pts:80
Star Rating
Bronze Medal
peej said:Now all I've got to do is make page after page of catalogue data look "prettier"


Grow some flowers round it?
#6 at 15:37:46 - 16/04/2007
HairyArse
Flag
Posts:6388
Comments:1774
Thread Kills:127(2%)
AATG Pts:350
Star Rating
Gold Medal
How long is too long for an SQL query?

If I have a query that takes almost a full second to run but then also have a nested query that runs for each row the first produces, is that considered inefficient?
#7 at 17:14:14 - 05/12/2007
peej
Flag
Posts:14637
Comments:4691
Thread Kills:462(3%)
AATG Pts:400
Star Rating
Gold Medal
Er, depends...!

You really do some complicated stuff with your queries so taking a second doesn't sound that bad tbh!

Peej
#8 at 17:16:44 - 05/12/2007
Micro_Explosion
Flag
Posts:3361
Comments:83
Thread Kills:129(4%)
AATG Pts:220
Star Rating
Silver Medal
A few seconds is fine - I have some that take over 30 seconds to run, admittedly that is about 5 tables linked over a network connection and very large tables.

When it gets close to a minute you might want to try it a different way.
#9 at 20:35:12 - 05/12/2007
HairyArse
Flag
Posts:6388
Comments:1774
Thread Kills:127(2%)
AATG Pts:350
Star Rating
Gold Medal
Also should have added that I'm talking about web queries here, i.e. a website can't fully load until the query is done.
#10 at 20:53:56 - 05/12/2007
tannerd
Flag
Posts:41
Comments:0
Thread Kills:2(5%)
AATG Pts:10
Star Rating
Pfft!

I've got web reports that take over half an hour to run, and then return an excel document with more than the 65,000 odd rows maximum that excel allows.

Actually I shouldn't brag about that, should I...
#11 at 11:29:44 - 06/12/2007
eviltobz
Flag
Posts:304
Comments:68
Thread Kills:15(5%)
AATG Pts:80
Star Rating
Bronze Medal
HairyArse said:How long is too long for an SQL query?

If I have a query that takes almost a full second to run but then also have a nested query that runs for each row the first produces, is that considered inefficient?

nested queries are definitely inefficient and you should generally see if you can do things with more exotic joins or temp tables where possible, but it all kinda depends really. if the page isn't used toooo much then it's not a big issue, say the user profile page rather than the main forum index for example. also, if you know that the result set will always be of a fixed small size then it's not too bad, but if it will grow over time then it is a disaster waiting to happen.
#12 at 15:38:25 - 06/12/2007
HairyArse
Flag
Posts:6388
Comments:1774
Thread Kills:127(2%)
AATG Pts:350
Star Rating
Gold Medal
It's ok we've come up with an alternative solution that will do the job far more efficiently!
#13 at 15:39:17 - 06/12/2007
HairyArse
Flag
Posts:6388
Comments:1774
Thread Kills:127(2%)
AATG Pts:350
Star Rating
Gold Medal
Has anyone experience problems with trying to select MAX and MIN dates but using the DATE_FORMAT function the date correctly.

Basically if I format the date into dd/mm/yyyy the MIN and MAX options gives me incorrect results, but if I leave the date un-formatted then the results are fine.
#14 at 13:24:30 - 03/03/2009
peej
Flag
Posts:14637
Comments:4691
Thread Kills:462(3%)
AATG Pts:400
Star Rating
Gold Medal
Dunno if it's a factor but I always format dates like:

YYYY-MM-DD

and don't have probs with min max. It's a ballache to get people to put dates in the right format but a bit of form mangling soon seems to sort that out.

#15 at 13:35:10 - 03/03/2009
peej
Flag
Posts:14637
Comments:4691
Thread Kills:462(3%)
AATG Pts:400
Star Rating
Gold Medal
Having another no-brain day. How would I compare a table field against the current year in MYSQL?

I've tried stuff like select * where Year = NOW() but to no avail (Year is my table's Year field)

Anyone?
#16 at 15:54:03 - 23/03/2009
HairyArse
Flag
Posts:6388
Comments:1774
Thread Kills:127(2%)
AATG Pts:350
Star Rating
Gold Medal
SQL only or using PHP too?

If you have PHP then you could just set:

$this_year = "2009/01/02";

SELECT * FROM TABLE WHERE FIELDDATE > $this_year;
#17 at 16:07:34 - 23/03/2009
peej
Flag
Posts:14637
Comments:4691
Thread Kills:462(3%)
AATG Pts:400
Star Rating
Gold Medal
Sort of close to what I'm after but was trying to do the whole thing as a single MYSQL query rather than setting up a php variable first.

Ah well, I'll see if that makes a diff.

Cheers
#18 at 16:09:15 - 23/03/2009
HairyArse
Flag
Posts:6388
Comments:1774
Thread Kills:127(2%)
AATG Pts:350
Star Rating
Gold Medal
Or what about:

SELECT * FROM TABLE WHERE YEAR(your_field) = '2009'
#19 at 16:11:14 - 23/03/2009
Stevas
Flag
Posts:1783
Comments:260
Thread Kills:39(2%)
AATG Pts:195
Star Rating
Gold Medal
Aren't you trying to compare a year field against an exact time there (that is, Now() would return... well, now, down to the second)? Don't you need to compare using the Year function? i.e.

SELECT * FROM Sometable WHERE Sometable.Year = YEAR(Now)

?
#20 at 16:12:50 - 23/03/2009
peej
Flag
Posts:14637
Comments:4691
Thread Kills:462(3%)
AATG Pts:400
Star Rating
Gold Medal
Stevas - you're closer to the mark and that works nicely. Basically that's the sort of thing I want. If I had "year = 2009" I'd have to alter the code every year.

Cheers all!

#21 at 16:23:06 - 23/03/2009

home