KitCarl

Query works except when adding the @rank :=IF(@prev_val != AKC, @rownum, @rank )AS rank, section which returns error code 1054 - Unknown column 'AKC' in 'field list' The AKC field is a calculated field, is this the problem? How can I create a rank column where if the AKC field is equal the rows share the same rank. SET @rownum = 0, @rank = 0, @prev_val = NULL; SELECT @rownum := @rownum + 1 AS ROW, @rank :=IF( @prev_val != AKC, @rownum, @rank )AS rank, TrialListing.qualifier, CONCAT_WS( ' ', pedigree.pretitle, pedigree.`Name` )AS NAME, SUM( CASE WHEN placement.place_id < 5 THEN TRUNCATE( TrialClass.number_of_entrants / placement.place_id, 2 ) WHEN placement.place_id = 5 THEN '' ELSE 0 END )AS AKC, SUM( CASE WHEN placement.place_id = 1 THEN 30 WHEN placement.place_id = 2 THEN 15 WHEN placement.place_id = 3 THEN 10 WHEN placement.place_id = 4 THEN 5 END )AS 'Better Beagling', SUM( CASE WHEN placement.place_id = 1 THEN 10 WHEN placement.place_id = 2 THEN 8 WHEN placement.place_id = 3 THEN 6 WHEN placement.place_id = 4 THEN 4 WHEN placement.place_id = 0 THEN 2 END )AS ibga_licensed FROM TrialClass LEFT JOIN TrialListing ON TrialListing.listingID = TrialClass.listingID JOIN placement ON placement.event_id = TrialClass.trialClassID JOIN pedigree ON pedigree.PedigreeId = placement.hound_id LEFT JOIN place ON place.place_id = placement.place_id WHERE TrialListing.qualifier = '1' AND TrialClass.classID IN('2', '5') AND pedigree.Sex = 'F' GROUP BY NAME ORDER BY AKC DESC, NAME

OK, Here is the code $sql = "SELECT TrialListing.listingID AS Trial, TrialClass.classID AS Class, place.place_name AS Place, CONCAT_WS( ' ', pedigree.pretitle, pedigree.`Name`) AS Hound, CONCAT_WS( ' ', ped2.pretitle, ped2. NAME )AS Sire, CONCAT_WS( ' ', ped3.pretitle, ped3. NAME )AS Dam, pedigree.Breeder, pedigree.`Owner`, CASE WHEN placement.place_id < 5 THEN TRUNCATE(TrialClass.number_of_entrants / placement.place_id,2) WHEN placement.place_id = 5 THEN '' ELSE 0 END AS Score FROM TrialListing Left Join TrialClass ON TrialListing.listingID = TrialClass.listingID JOIN placement ON placement.event_id = TrialClass.trialClassID JOIN pedigree ON pedigree.PedigreeId = placement.hound_id LEFT OUTER JOIN pedigree AS ped2 ON pedigree.SireId = ped2.PedigreeId LEFT OUTER JOIN pedigree AS ped3 ON pedigree.DamId = ped3.PedigreeId LEFT JOIN place ON place.place_id = placement.place_id WHERE TrialListing.listingID = 11 ORDER BY Class, FIELD(place.place_id, '1', '2', '3', '4', '0') "; // Database Query $result = mysql_query("$sql"); // Database Query result $num_rows = mysql_num_rows($result); // Starts the table echo "<table class=\"clubList\">\n <tr> <th>trialID</th> <th>ClassID</th> <th>Place</th> <th>Hound</th> <th>Sire</th> <th>Dam</th> <th>Score</th> </tr>"; // Create the contents of the table. for( $i = 0; $i < $row = mysql_fetch_array($result); $i++){ echo "<tr>\n" ."<td>".$row["Trial"]."</td>\n" ."<td>".$row["Class"]."</td>\n" ."<td>".$row["Place"]."</td>\n" ."<td>".$row["Hound"]."</td>\n" ."<td>".$row["Sire"]."</td>\n" ."<td>".$row["Dam"]."</td>\n" ."<td>".$row["Score"]."</td>\n" ."</tr>";} echo "</TABLE>"; Here is the output, I added the TrialID & ClassID for informational purposes, they do not need to be displayed in the live table. trialID ClassID Place Hound Sire Dam Score 11 1 1st Eaton Brook Tug Hill Tatonka Eaton Brook Hickety Hawk Eaton Brook Gunner's Beulah [/td] 43.00 11 1 2nd FC North Bend Igloo FC DFJ Murphy White IFC Brad-Ju's Bella Donna 21.50 11 1 3rd FC North Bend Igloo FC DFJ Murphy White IFC Brad-Ju's Bella Donna 14.33 11 1 4th Rail Road Spike VI Elwell's Mike Elwell's Hannah 10.75 11 1 NBQ FC Fish Creek Spike Fish Creek Bull II Fish Creek Susie [H849395] 11 2 1st Enman Hill Sweet Poppy FTCH Straight Arrow Lucky of Coos 32.00 11 2 2nd Fishflakes Penny At Harehaven FTCH Jill's Fair-Isle Spud FTCH Millbridge Brownie 16.00 11 2 3rd Line Elm Flakers IFC Flakers Rex IFC Line Elm Ginger 10.66 11 2 4th FTCH Fareast Mookie FTCH Mellowrun Sly FTCH Cape Breton Maude 8.00 11 2 NBQ FTCH Mellowrun Sly Mellowrun Skylighter FTCH Mellowrun Becka 11 3 1st Bojangle V Lee Otworth Half Acre's Cocoa Candy 23.00 11 3 2nd Gay Doll Gay Roll II Gay Idol 11.50 11 3 3rd Bruce's Blue Lady FC Kilsock's Blue Creek Bart Bishopville's Zippy 7.66 11 3 4th FC Pearson Creek Barbin FC Pearson Creek Barbarian FC B-Line Stubby 5.75 11 3 NBQ Sims Creek Cricket Ronnie Joe Sims Creek Tiny 11 4 1st FTCH Fareast Mookie FTCH Mellowrun Sly FTCH Cape Breton Maude 26.00 11 4 2nd FTCH Mellowrun Sly Mellowrun Skylighter FTCH Mellowrun Becka 13.00 11 4 3rd Fishflakes Penny At Harehaven FTCH Jill's Fair-Isle Spud FTCH Millbridge Brownie 8.66 11 4 4th Enman Hill Sweet Poppy FTCH Straight Arrow Lucky of Coos 6.50 11 4 NBQ Line Elm Flakers IFC Flakers Rex IFC Line Elm Ginger Below is what I would like to generate. How do I word or nest the proper PHP code/loops to accomplish this? ClassID Place Hound Sire Dam Score 1st Eaton Brook Tug Hill Tatonka Eaton Brook Hickety Hawk Eaton Brook Gunner's Beulah 43.00 2nd FC North Bend Igloo FC DFJ Murphy White IFC Brad-Ju's Bella Donna 21.50 3rd FC North Bend Igloo FC DFJ Murphy White IFC Brad-Ju's Bella Donna 14.33 4th Rail Road Spike VI Elwell's Mike Elwell's Hannah 10.75 NBQ FC Fish Creek Spike Fish Creek Bull II Fish Creek Susie [H849395] ClassID Place Hound Sire Dam Score 1st Enman Hill Sweet Poppy FTCH Straight Arrow Lucky of Coos 32.00 2nd Fishflakes Penny At Harehaven FTCH Jill's Fair-Isle Spud FTCH Millbridge Brownie 16.00 3rd Line Elm Flakers IFC Flakers Rex IFC Line Elm Ginger 10.66 4th FTCH Fareast Mookie FTCH Mellowrun Sly FTCH Cape Breton Maude 8.00 NBQ FTCH Mellowrun Sly Mellowrun Skylighter FTCH Mellowrun Becka ClassID Place Hound Sire Dam Score 1st Bojangle V Lee Otworth Half Acre's Cocoa Candy 23.00 2nd Gay Doll Gay Roll II Gay Idol 11.50 3rd Bruce's Blue Lady FC Kilsock's Blue Creek Bart Bishopville's Zippy 7.66 4th FC Pearson Creek Barbin FC Pearson Creek Barbarian FC B-Line Stubby 5.75 NBQ Sims Creek Cricket Ronnie Joe Sims Creek Tiny ClassID Place Hound Sire Dam Score 1st FTCH Fareast Mookie FTCH Mellowrun Sly FTCH Cape Breton Maude 26.00 2nd FTCH Mellowrun Sly Mellowrun Skylighter FTCH Mellowrun Becka 13.00 3rd Fishflakes Penny At Harehaven FTCH Jill's Fair-Isle Spud FTCH Millbridge Brownie 8.66 4th Enman Hill Sweet Poppy FTCH Straight Arrow Lucky of Coos 6.50 NBQ Line Elm Flakers IFC Flakers Rex IFC Line Elm Ginger [td]

Thank You!!!

I'm sure you can if you understand them, but after reading the MYSQL doc's I haven't a clue how it would work for my need. That last select statement was already pushing the envelope of my MYSQL knowledge. If someone could please show me how to use them with the query in the post above I'd appreciate it. I find it much easier to grasp a new subject if I can tinker with a working model to see how/why it works. C'mon Fenway, can I please, please get more than a short sentence.

Not sure of the terminology, but I need to be able to return a result where as an example if the number_of_entrants is 48 for the event, it will return a score of 48 for 1st(placement=1), 24 for 2nd(placement=2), 16 for 3rd(placement=3) and 12 for 4th(placement=4) Guessing something like below, but I don't have actual tables to test yet. Score is to two decimal places and is not rounded. SELECT person.person_name AS Name, placement.placement AS Place, TRUNCATE((event.number_of_entrants/placement.placement), 2) AS Score FROM event JOIN placement ON event.event_id = placement.event_id JOIN person ON person.person_id = placement.person_id Where event.event_id = ? And just as an extra twist 5 places are awarded but 5th does not receive points? How do I make the score 0 instead of placement/5 for this?

Example data for this table event_id | person_id | placement(integer) 1|34|1 1|23|2 1|53|4 1|12|3 2|117|1 My thought was to record the actual place in the table and calculate the points based on a factor of the number_of_entries field when I create the report. This way if I find an error in the entries number and need to change it, score will automatically recalculate when the report is run. I just have no idea how to create a query to calculate the scores, or if this is better done via PHP? If you could get me started or let me know if I should use a different method it will be much appreciated.

I need to calculate points for placements in an event based on the number of entrants. 1st is a multiple of 1, 2nd is a multiple of 1/2, 3rd is a multiple of 1/3, fourth is a multiple of 1/4. I have an person table (person_id | person_name) , event table (event_id | event_name | number_of_entrants ) and a placement table (event_id | person_id | placement(integer)) How do I create a result that would give the proper score for each place 1st - 4th ?

Kickstart, Thank you so very much I had spent hours on this one to no avail.

Kickstart, I tried adding the line "LEFT Join ClubOfficers AS t4 ON t4.clubID = t3.clubID" but that just adds every possible office to each name. I Probably didn't add it in the way that you meant? SELECT CONCAT_WS(' ', t1.firstName, LEFT(t1.middlename, 1), t1.nickName, t1.lastname, t1.suffix) AS name, Group_Concat(t5.officeName ORDER BY t5.officeID SEPARATOR '<br />') AS office FROM People t1 Left Join PeopleClub t3 ON t1.peopleID=t3.peopleID Left Join ClubOfficers AS CO ON t3.peopleID=CO.peopleID LEFT Join ClubOfficers AS t4 ON t4.clubID = t3.clubID Left Join Office t5 ON t5.officeID=CO.officeID Where t3.clubID = 12 Group BY name Order by t1.lastName, t1.firstName This one returns the Id and proper offices for only members who are officers of the club SELECT t1.peopleID, CONCAT_WS(' ', t1.firstName, LEFT(t1.middlename, 1), t1.nickName, t1.lastname, t1.suffix) AS name, Group_Concat(t5.officeName ORDER BY t5.officeID SEPARATOR '<br />') AS office FROM People t1 Left Join PeopleClub t3 ON t1.peopleID=t3.peopleID Left Join ClubOfficers AS CO ON t3.peopleID=CO.peopleID Left Join Office t5 ON t5.officeID=CO.officeID Where t3.clubID = 12 AND CO.clubID = 12 Group BY name Order by t1.lastName, t1.firstName This one returns All members of the club SELECT t1.peopleID, CONCAT_WS(' ', t1.firstName, LEFT(t1.middlename, 1), t1.nickName, t1.lastname, t1.suffix) AS name FROM People t1 Left Join PeopleClub t3 ON t1.peopleID=t3.peopleID Where t3.clubID = 12 Is there a way to join the two queries as one table on the t1.peopleID field? Or is there just a completely better way to write the query?

I'm at a loss as to how to change this query to generate the proper data. I need to generate the member names and offices they hold for a specific club. This query gives me the correct list of names for the specific club, but lists offices they hold in all clubs. How do I select only those offices held for that specific club. I hope I supplied enough info but if not please tell me what to add. SELECT CONCAT_WS(' ', t1.firstName, LEFT(t1.middlename, 1), t1.nickName, t1.lastname, t1.suffix) AS name, Group_Concat(t5.officeName ORDER BY t5.officeID SEPARATOR '<br />') AS office FROM People t1 Left Join PeopleClub t3 ON t1.peopleID=t3.peopleID Left Join ClubOfficers AS CO ON t3.peopleID=CO.peopleID Left Join Office t5 ON t5.officeID=CO.officeID Where t3.clubID = 12 Group BY name Order by t1.lastName, t1.firstName ClubOfficers Table clubOfficerID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, peopleID INT(6) UNSIGNED NOT NULL, clubID INT(6) UNSIGNED NOT NULL, officeID INT(11) UNSIGNED DEFAULT NULL, startDate DATE DEFAULT NULL, leaveDate DATE DEFAULT NULL, PRIMARY KEY (clubOfficerID) Office Table officeID INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, officeName VARCHAR(255) NOT NULL, PRIMARY KEY (officeID)

I need to track ownership of animals that can have up to four owners at a time which can change as they are bought/sold. I have a person table with all their info and an animal table with their respective info. Is there a better way to store this info than using a table with the fields listed below. What problems do you forward thinkers for see for this down the road? PersonAnimalID | AnimalID | PersonID | DateBought | DateSold

This will be my first attempt at adding AJAX/javascript, any chance I could get a code example that will lead me in the right direction?

A open source php software package I use has a boolean variable set in the config file that depending on its setting calls a block of code to be used on all pages. I would like to use a check box so the user will be able to toggle the function on and off for the current page by resetting the variable and reloading the page upon the onclick event. Can someone please explain how to go about this using javascript.

Eagle, That is what I am looking to do, now if I only knew enough to implement it. I read the link and it is above my knowledge. If I had a td cell that presently was ".$row["email"]." any idea how I would go about recoding it to reach out to reCaptcha and return the mailhide capability using the info on that link you suppied?

I can grasp the theory of why it defeats spam bots. How would I go about converting the email string into an image, then convert it back to a usable email link when the upon onclick of the image on the web page?