How to highlight mysql results

[0o0o0]

example...  http://www.e-ponies.com/races/california-free-horse-racing-picks.htm

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

[fastsol]

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

[0o0o0]

that just leaves one row. so like say race 1.. it leaves horse number 8  but not horse 7 6 5 4 3 2 1

I 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

[QuickOldCar]

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>

[Barand]

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

[0o0o0]

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

[0o0o0]

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.