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?

mgallforever, Can you point to a site that uses the method you suggested so I can better understand what you mean, and maybe a slightly more detailed explanation. Work with me.

Eagle, I was hoping to do it in a way that maybe they have to solve a Captcha or something first but when someone clicks on the link it will present the proper email address? mgallforever, Huh? Did you see the note about being a beginner That one seems way above my pay grade just yet as I haven't a clue.

I'm not worried about spam on the forms, I'm trying to find a way to make it hard for the spam bots to gather email information from name, state, email tables that users generate from the database. I would like to have an encryption method for the displayed email addresses in those generated tables on the public web pages.

I have a table with a standard email column which is inserted into the database via a form and an encryptedemail column which I have to manually complete by using the reCAPTCHA mail hide method. I'm looking for a method to automatically generate an encrypted email to display on the HTML page like the reCAPTCHA one and save me the work! Open to all ideas as there is probably a completely different and better way to fight the spam bots and I'm new to php.

I tried working with the " and \ but can't get it right. Anyone?

I tried a simple cut & paste with Zanus' code but I doesn't work as I used it. It works separately when tested so I know it is the implementation not the code Here is the complete code which will probably make it easier to figure out. $sql = "SELECT * FROM BeagleClub AS B, ClubOrganization AS C, Association AS A WHERE B.clubID = C.clubID AND C.organizationID = A.associationID AND C.organizationID = '1' ORDER BY B.clubName"; // Database Query $result = mysql_query("$sql") or die(mysql_error()); // Database Query result $num_rows = mysql_num_rows($result) or die(mysql_error()); echo "<h3 align='center'>There are $num_rows IBGA clubs\n</h3>"; // Starts the table echo "<table bgcolor=#0469B3 border=0 cellpadding=10 cellspacing=0 align='center' font-size=10px;>\n <tr> <th>Club Name</th> <th>Website</th> <th>Street Address</th> <th>City</th> <th>State</th> <th>Zipcode</th> <th>GPS Lattitude</th> <th>GPS Longitude</th> <th>Weather Forcast</th> </tr>"; // Create the contents of the table. for( $i = 0; $i < $row = mysql_fetch_array($result); $i++){ echo "<TR>\n" ."<TD bgcolor=".row_color($i).">".$row["clubName"]."</TD>\n" ."<TD bgcolor=".row_color($i).">".$row["website"]."</TD>\n" ."<TD bgcolor=".row_color($i).">".$row["clubAddress"]."</TD>\n" ."<TD bgcolor=".row_color($i).">".$row["clubCity"]."</TD>\n" ."<TD bgcolor=".row_color($i).">".$row["clubState"]."</TD>\n" ."<TD bgcolor=".row_color($i).">".$row["clubZipcode"]."</TD>\n" ."<TD bgcolor=".row_color($i).">".$row["clubLattitude"]."</TD>\n" ."<TD bgcolor=".row_color($i).">".$row["clubLongitude"]."</TD>\n" ."<TD bgcolor=".row_color($i).">".$row["clubWeather"]."</TD>\n" ."</TR>"; } echo "</TABLE>"; mysql_close($connection); ?> When I changed the last <TD> line to ."<TD bgcolor=".row_color($i).">'<a href="http://www.weather.com/weather/tenday/' .$row["clubZipcode"]. '" target="_blank"> Weather</a>';</TD>\n" This is the error message: Parse error: syntax error, unexpected T_STRING, expecting ',' or ';' in /home/beagle5/public_html/ibgany.org/ibga_club_list.php on line 145 Hoping to learn best practices not just have passable code so critics on all the code happily accepted.

I created weather links by hand for several zipcodes like this and put them in a column in the database, but that seems to be poor use of the database <a href="http://www.weather.com/weather/tenday/07461" target="_blank"> Weather</a> I would like have a self generating link using the zipcode column from the database and here is the code from the already created zip table cell ".$row["clubZipcode"]." Can someone please show me how to combine them? I can't seem to figure out all the " "'s and //'s.

Thank You

Reading the responses to questions on this forum and having a few posts answered has been a fantastic learning tool! I still know almost nothing but I'm making more progress than I was from the manuals. After reading several posts and experimenting I had a big after figuring out how to left join multiple tables together create a list from more normalized tables. Anyhow that works fine if I want to create a list of people who belong to each club, but not if I want to list the people and which club(s) they are members. In simple terms: I have Person table P with P.personid, P.first name, etc. I have Club table C with C.clubid, C.name, etc. I have clubMember table CM with CM.clubMemberID, CM.personid , CM.clubID Each person can belong to more than one club so how do I write a query to give me the Name info from the Person table once, but return all C.name's that match the P.personID so I can display it as Name Clubs Joe Whatever Club A, Club B, Club C NOT Name Clubs Joe Whatever Club A Joe Whatever Club B Joe Whatever Club C Do I need a sub-query or is this solved with PHP?

I have a check box group and would like to include a All check box, but do not now how to code the value to have the multiple values in one check box passed into the array? <label> <strong> <input type="checkbox" name="association[]" value="1" checked id="association_4" /> IBGA</strong></label> <strong> <label> <input type="checkbox" name="association[]" value="3" id="association_5" /> PBGA</label> <label> <input type="checkbox" name="association[]" value="2" id="association_6" /> NEHBA</label> Here is the PHP code(My thanks to Cags for the implode statement). I would like to set the Else to be the All check box value. IF (!empty($_POST['association'])) $association = $association = "'" . implode("','", $_POST['association']) . "'"; ELSE $association = "'1'"; Presently the check boxes names and values are hand coded, but they match names & values in a database table. I'd like to see the quick fix to learn how it is done, but assume the better way long term to handle it is by pulling the Names & Values from the database. While that is above my knowledge level, I wouldn't mind seeing how that is done to try to understand that method as well.