0o0o0 Posted June 22, 2015 Share Posted June 22, 2015 example... http://www.e-ponies.com/races/california-free-horse-racing-picks.htmJust an example of what this would look like below.. SELECT t1.Horse, t1.Track, t1.Race, t2.Speed, t2.Class, t2.Form, t2.Last, t2.Conn, t2.Line, t2.win, t2.Total FROM racestoday as t1 LEFT JOIN horses as t2 ON t1.HorseRef = t2.HorseRef WHERE t1.Date = CURDATE() ORDER BY t1.Date ASC, t1.track ASC, t1.Race ASC It would list them perfectly liek the example link above.. but how do you get all the highest in bold??.. and how they bold each race for each track and Speed, Class, Form,Conn, and Total? Is this Group by? when I try group by I only get one row from track race 1, track race 2, track race 3.. but how do you get all?Any help would be great! Each Track by Each race with "all" horses listed and just ONE example of how to bold the highest in a list. Not order, but highlight or bold.Am I going in the wrong direction here with mysql? is it in php coding?? or mysql or both??Thanks for any replies. Quote Link to comment Share on other sites More sharing options...
fastsol Posted June 23, 2015 Share Posted June 23, 2015 You could try using MAX() on the specific columns you want to get the highest number of. You may need to also use GROUP BY on something, but I'm not totally sure cause I don't really use this much. SELECT t1.Horse, t1.Track, t1.Race, MAX(t2.speed) AS Highest_Speed, t2.Speed, t2.Class, t2.Form, t2.Last, t2.Conn, t2.Line, t2.win, t2.Total FROM racestoday as t1 LEFT JOIN horses as t2 ON t1.HorseRef = t2.HorseRef WHERE t1.Date = CURDATE() ORDER BY t1.Date ASC, t1.track ASC, t1.Race ASC Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted June 23, 2015 Author Share Posted June 23, 2015 that just leaves one row. so like say race 1.. it leaves horse number 8 but not horse 7 6 5 4 3 2 1I just need to to be $speed so I can php <td><?php echo ($row['speed'] == $max['speed']) ? "<strong>{$row['speed']}</strong>" : $row['speed']; ?></td> but its not workin out Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted June 23, 2015 Share Posted June 23, 2015 From Fastol's example did $row['Highest_Speed'] not work? In your loop compare them and highlight speed if it matches. <td> <?php if ($row['Highest_Speed'] == $row['speed']) { echo "<strong>".$row['speed']."</strong>"; }else{ echo $row['speed']; } ?> </td> Quote Link to comment Share on other sites More sharing options...
Barand Posted June 23, 2015 Share Posted June 23, 2015 Create a table subquery to get the max values for each track/race and join to that. Where speed matches maxspeed then output as bold (not tested) SELECT t1.Horse , t1.Track , t1.Race , t2.Speed , t2.Class , t2.Form , t2.Last , t2.Conn , t2.Line , t2.win , t2.Total , maxspeed , maxclass , maxform , maxconn , maxtotal FROM racestoday as t1 LEFT JOIN horses as t2 ON t1.HorseRef = t2.HorseRef LEFT JOIN ( SELECT track , race , MAX(Speed) as maxspeed , MAX(Class) as maxclass , MAX(Form) as maxform , MAX(Conn) as maxconn , MAX(Total) as maxtotal FROM racestoday WHERE date=CURDATE() JOIN horses USING (HorseRef) GROUP BY track, race ) as maxvals USING (track, race) WHERE t1.Date = CURDATE() ORDER BY t1.Date ASC, t1.track ASC, t1.Race ASC Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted July 11, 2015 Author Share Posted July 11, 2015 (edited) k here we go, been workin on this for a long time.. lol.. for me anyhow.. you guys probably would take 4 mins. this below gives me all the races, all the horses in each race.. and stats.. SELECT t1.Horse, t1.Track, t1.Race, t1.Jockey, t1.Trainer, t1.Owner, t1.Date, t2.speedFigure, t2.rank, t2.perStart, t2.topThree, t2.topThreePercentage, t2.winPercentage, t2.starts, t2.win, t2.place, t2.show, t2.earnings, t3.speedFigure2014, t3.rank2014, t3.perStart2014, t3.topThree2014, t3.topThreePercentage2014, t3.winPercentage2014, t3.starts2014, t3.win2014, t3.place2014, t3.show2014, t3.earnings2014, (t2.speedFigure + t3.speedFigure2014) AS figtote, (t2.rank + t3.rank2014) AS ranktote, (t2.perStart + t3.perStart2014) AS avgtote, (t2.topThree + t3.topThree2014) AS pronum, (t2.topThreePercentage + t3.topThreePercentage2014) AS pronumpercent FROM champsintoday as t1 LEFT JOIN champshorses as t2 ON t1.HorseRef = t2.referenceNumber LEFT JOIN champshorses2014 as t3 ON t1.HorseRef = t3.referenceNumber2014 WHERE t1.Date = CURDATE() ORDER BY t1.Date ASC, t1.track ASC, t1.Race ASC, t2.speedFigure DESC small example Race 1 horse 1 stat stat stat Race 1 horse 2 stat stat stat Race 1 horse 3 stat stat stat Race 1 horse 4 stat stat stat Race 2 horse 1 stat stat stat Race 2 horse 2 stat stat stat Race 2 horse 3 stat stat stat Race 2 horse 4 stat stat stat all good... now this below gives me all the "highest" numbers for each race.. SELECT t1.Track, t1.Race, MAX(t2.speedFigure) AS maxf2015, MAX(t3.speedFigure2014) AS maxf2014, MIN(t2.rank) AS maxr2015, MIN(t3.rank2014) AS maxr2014, MAX(t2.perStart) AS maxa2015, MAX(t3.perStart2014) AS maxa2014, MAX(t2.speedFigure + t3.speedFigure2014) AS maxtoprating, MIN(t2.rank + t3.rank2014) AS maxtoprank, MAX(t2.perStart + t3.perStart2014) AS maxtopavg, MAX(t2.starts) as maxstarts, MAX(t2.winPercentage) as maxwinPercentage, MAX(t2.topThreePercentage) as maxtopThreePercentage, MAX(t2.win) as maxwin, MAX(t2.place) as maxplace, MAX(t2.show) as maxshow,MAX(t2.topThree) as maxtopThree,MAX(t2.earnings) as maxearnings, MAX(t3.starts2014) as maxstarts2014, MAX(t3.winPercentage2014) as maxwinPercentage2014, MAX(t3.topThreePercentage2014) as maxtopThreePercentage2014, MAX(t3.win2014) as maxwin2014, MAX(t3.place2014) as maxplace2014,MAX(t3.show2014) as maxshow2014, MAX(t3.topThree2014) as maxtopThree2014,MAX(t3.earnings2014) as maxearnings2014, MAX(t2.topThree + t3.topThree2014) AS maxpronum, MAX(t2.topThreePercentage + t3.topThreePercentage2014) AS maxpronumpercent,(t2.speedFigure + t3.speedFigure2014) AS maxfigtote, (t2.rank + t3.rank2014) AS maxranktote, (t2.perStart + t3.perStart2014) AS maxavgtote FROM champsintoday as t1 LEFT JOIN champshorses as t2 ON t1.HorseRef = t2.referenceNumber LEFT JOIN champshorses2014 as t3 ON t1.HorseRef = t3.referenceNumber2014 WHERE t1.Date = CURDATE() GROUP BY t1.Date, t1.track, t1.Race ORDER BY t1.Date ASC, t1.track ASC, t1.Race ASC, t2.speedFigure DESC Now I have all horses all races.. and the other code I have all the MAX's for each race..the idea is to show all horses all races.. and in php whichever matches those max numbers it highlights. That parts easy I can figure that out.. My question is .. instead of making the second code just goto a mysql table.. is there such thing as an "imaginary, or temporary invisible table???Basically mashing the two above to be all in one select line..????i just dont have a clue how to put both above together to make say... race 1 horse 1 stat stat stat maxstat, maxstat, maxstat.. or even if statement .. if ($Track = $t4.Track and $Race = 4t.Race And $rank = $maxrank) $col14 = "#006600"; etc.. through out. basically how does one mash the above together and create a temp table for the second Select code. thanks to anyone who can give me a suggestion.. its a lot of code ( i think) even if you cut it down and show me a simple example not using all the code I have.. lemme know. been stuck here trying endless attempts and getting errors for .... checking first post ...holy s**T!! an hour a day for 3 weeks now.. lol blush.Thanks to anyone.This is personal project, not commercial or for any client, just a hobby thing.. so any shoe string suggestions welcome also lol speed doesnt matter, nor efficiency.. its personal if it takes 60 seconds+ to load no biggie. Its only to load a page once and leave it in a browser, on my screen for the entire day. Edited July 11, 2015 by 0o0o0 Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted July 11, 2015 Author Share Posted July 11, 2015 correction the if statement would probably look like. if ($t1.Track = $t4.Track AND $t1.Race = 4t.Race AND $t2.rank = $t4.maxrank) $rankcolumn = "#006600"; doesnt matter..just looking for how to mash the two Selects together with sql.. I can figure out the php code after. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.