ViperSBT

That is exactly what I am saying my problem is, how do I get the appropriate s.event?

I don't understand what you are saying... Not being well versed on using subselects I am not sure why it is needed there... My problem is still that the results I get from the GROUPING and the MIN() do not match line to line...

s.event refers to t.enumber which can be used to look up specific information about the event that the time was earned, in this case I am wanting the date of the event.

OK, maybe I am doing this INNER JOIN wrong, but here is the query I put together: SELECT d.cname, c.cname AS club, c.cnumber, speed.event, speed.time, d.cname, CONCAT(h.fname, ' ', h.lname) AS owner, d.height, date_format(t.date, '%m-%d-%y') as date FROM dogs d INNER JOIN (SELECT s.dog, s.event, MIN(s.time) AS time FROM singles s GROUP BY s.dog) AS speed ON d.dnumber = speed.dog JOIN clubs c ON d.club = c.cnumber JOIN handler h ON d.owner = h.hnumber JOIN tournament t ON speed.event = t.enumber WHERE d.breed = 45 ORDER BY speed.time All of the results are as desired, but I am still not getting the right 'speed.event' to lookup the tournament number to get the date for the tournament... The 'speed.time' is correct as I am getting the MIN(s.time) for the desired competitor, but the 's.event' that is being returned is the first that it finds for the competitor... As an example, here is the actual table information for one of the competitors: ID dog time event 67 15 3.768 4 233 15 3.835 32 1086 15 3.693 73 And this is what the Query returns for that competitor: Sage Touch N Go 2 4 3.693 Sage Stephanie Franks 12 06-12-05 So, I am getting the MIN(time) of 3.693 but it thinks that was done on 06-12-05 which was event 4, not 73... Arrgghhhh

Table: speed field1: id field2: date field3: time field4: competitor Now using the following example for the Speed Table: Field1 Field2 Field3 Field4 104/04/0612.0001 205/04/0611.0001 308/04/0612.5001 410/04/0610.0001 I want to know the fastest time and the date that the competitor ran and have a rather extensive query that this is a subset out of. But what is happening is that when I use the MIN() and GROUP BY I get the correct 'time' but I don't get the correct date. It returns the '10.00' as expected but for the date I am getting '04/04/06' which is just the first entry in that series of the GROUP... How do I get the appropriate date?

OK, I tried for awhile to come up with a reasonable Subject that explained what I was looking to do, but couldn't. So here it is: I have a table that is loaded with competition results. These results are fairly simplistic being that the table consists primarily of the competitor number and the points the competitor earned at a particular event. So the four fields are 'id', 'competitor', 'event', 'points'. A competitor will be listed multiple times in the this table, based upon its entries in multiple events and the points earned for each of those events. Now for the tricky part. When doing reporting, I query this table by competitor to get a listing of all events the competitor entered as well as the points earned for each event, and I can sort this by the event so it shows chronologically the points the competitor earned. The thing that I am trying to figure out, is how to identify which event a competitor crossed a certain point level. As an example, I want to know which event a competitor earned their 5,000th point. I would like to be able to do this as part of the query, is it possible, or will it have to be done in PHP after the query is returned?

Thank you!

SELECT d.dnumber, SUM( p.points ) AS points FROM dogs d JOIN points p ON d.dnumber = p.dog WHERE points >=20000 GROUP BY p.dog This should be fairly obvious, I am summing all of the points for each dog in my table. In doing so I only want to have results returned for dogs that have more than 20,000 points. For some reason when I use this, I get no lines returned. If I take out the WHERE I get everything... And, yes, there are lines that have well more than 20,000 points returned.

Nope, I tried it before, but thought I would again and LEFT JOIN doesn't make a difference in this case...

Thanks, but that didn't seem to change anything.

OK, I have a table that has multiple entries for a contestant, each entry has the contestants number, speed, and event # where they earned that speed. I want to do a query that shows me the fastest time for each participant and include that date of the tournament by pulling it from the table that is referenced by the event #. I have this mostly down, but when I do my grouping the event number doesn't come across correctly. Here is my current query: SELECT s.participant, MIN(s.time) as time, date_format(t.date, '%m-%d-%y') AS date FROM singles s JOIN tournament t ON s.event = t.enumber GROUP BY s.participant ORDER BY time This works in that I get all the fields, but the dates do not correlate appropriatly... If in the table I have something like: Record1 Participant1 Time2 Event1 Record2 Participant1 Time1 Event2 I would want to see Time1 as it is the faster of the times and I would want the date for Event 2. I am getting Time1 but I am getting the date for Event1. What am I doing wrong?

The $states array is bein gbuilt from a Mutli-Seelction List field on a prior page.  The select which states they want to see a report for.[code]<select multiple name='states[]' size='10'> <option value='All' SELECTED>All States/Provinces</option> <? foreach ($statelist as $key => $value) { echo "<option value='" .$key."'>$statelist[$key]</option>\n"; } ?></select>[/code]

$states = Array ( [0] => Array ( [0] => 5 [1] => 30 ) ) As for the series of ArrayArray...  The foreach is inside a while loop...

OK, I pass an array from another page, I then use the following code to handle the array:[code]foreach($states as $state){ echo $state;}[/code] I would expect to get each of the states in the array returned, instead what I get is: [quote]ArrayArrayArrayArray[/quote] So, why isn't the array being parsed?  If I do print_r($states) I can see each of the elements in the array and they hold the correct value...  So it seems the foreach isn't doing what it is supposed.

This shouldn't be this difficult... [quote]$statesql = "SELECT id, state FROM state ORDER BY state"; $stateresult = mysql_query($statesql); while ($temp = mysql_fetch_array($stateresult)){ $sid = $temp["id"]; $sst = $temp["state"]; echo "id = ".$sid."<br>state = ".$sst."<br>"; $statelist[$sid] = $sst; }[/quote] I have run the SELECT statement directly from the DB and get the expected return, but trying to parse it and create an array called statelist is going nowhere....  I included the echo line to see what it was doing, and I am getting a blank page...  So, it would seem to me that it isn't even performning the WHILE statement.

That would require that I provide the UserID to count for, I want it to return all UserIDs in the table.  Also this provides two separate columns for each UserID, I need one column that is the UserID and another column that is the counts for the number of times that UserID exists in either of the fields of that table.

I have two fields in a table that both may contain a userid.  I would like to find the number of rows for each userid occurance, no matter which field it is in....  Something like: So if the two fields were user1 and user2 and the data looked like this: user1  user2   2        4   1        2   3        5   1        2 I would want my result to be: 1 = 2 Counts 2 = 3 Counts 3 = 1 Counts 4 = 1 Counts 5 = 1 Counts

A dad is a single entry in the 'dads' table. A dad MAY have multiple entries in both the 'singles' and 'points' tables. So what I am wanting to do is find out what the smallest number is for a dad in the 'singles' table as well as the total number of points the dad has earned by summing all of his results from the 'points' table...

OK, so it is the most complex query that I have put together to date... The query looks like this: [code]SELECT d.dnumber, d.cname, MIN( s.time ) AS singles, SUM( p.points ) AS points FROM dads d LEFT JOIN singles s ON s.dad = d.dnumber LEFT JOIN points p ON p.dad = d.dnumber WHERE d.cat =45 && d.payment != 'pending' GROUP BY d.dnumber ORDER BY cname[/code] The result shows all the rows I am expecting, and everything is in order except for the 'points' result. It appears that the sum of all the points entries in the points table for a particular dad are being multiplied by the number of entries that dad is showing up in the singles table. Does that make sense? So a dad that has 100 points and is in the singles table once = 100, which would be correct. A dad that has 100 points and in the singles table 3 times = 300, which is incorrect and should only be the 100.

OK, here is the scenario. I run a SQL query that generates 32 rows of results containing id, name, image. What I need to do with this is to create and array that contains each row of data and includes an "array key" for each row. example SQL return: 123, fred, fred.jpg 145, wilma, wilma.jpg I want that to be an array: 1, 123, fred, fred.jpg 2, 145, wilma, wilma.jpg I have tried several ways and have had nothing but frustration... Once the array is built how do I access the information, say I just want to pull Wilma's row and skip Fred?

That the ORDER BY RND() worked perfect! Thanks.... The other ORDER BY was when I was trying to do something different, didn't need to be there...

Using the following code: [code]$sql = "SELECT m.id, m.name, m.picture, AVG(s.score) as average FROM models m LEFT JOIN scoring s ON s.id = m.id WHERE m.category = '$game' GROUP BY m.id ORDER BY average DESC"; $result = mysql_query($sql); $count = mysql_num_rows($result); $row = mysql_fetch_array($result); $contestants = array_rand($row, 10);[/code] I am getting an error: [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Warning: array_rand(): Second argument has to be between 1 and the number of elements in the array...[/quote] I guess what I am trying to do may be a little different than what array_rand() is for. I rant to take 10 random rows and put them in the new array. The array $row has 30 rows of elements, and I want to take 10 on those.

Dude, I couldn't feel more stupid! Thank you, once again PHP Freaks saves the day!!!!

OK, I am trying to do the following query: [code]SELECT d.dnumber AS RUN, SUM(p.points) AS Points FROM dogs d JOIN points p ON p.dog = d.dnumber GROUP BY d.dnumber[/code] This returns a list of RUNs with associated Points, but it only recognizes RUNs that exist in both the dogs and points tables. I need all dogs in the dogs table regardless is they exist in the points table. If they don't exist in the points table I am expecting a null or 0 for the Points result for that RUN.