help with ORDER BY LEAST()

[aebstract]

What you just posted really got me confused. Advanced queries do that. If I go any sort of route that requires me changing the DB, then I'm also looking at having to change a lot of code for stuff that already exists. I could MAYBE do the 3 tables just for qualifying and just for the classes that require this special query, and leave the rest alone. *shrugs*

[aebstract]

If anything, I'd move the times to separate table

 

table results

----------------------

result_id, participant_id, run, time

1,  1,  1,  4.76

2,  1,  2,  4.78

3,  1,  3,  4.72

4,  2,  1,  4.68

5,  2,  2,  4.73

6,  2,  3,  4.75

....

 

Then

 

SELECT 
  p.participant_id, IF(above.time IS NOT NULL, above.time, below.time)
FROM
  participants AS p
LEFT JOIN
  (SELECT participant_id, MIN(time) AS time WHERE time >= 4.7 GROUP BY particpant_id) AS above
USING (participant_id)
LEFT JOIN
  (SELECT participant_id, MAX(time) AS time WHERE time < 4.7 GROUP BY particpant_id) AS below
USING (participant_id)

 

(not tested)

 

Could you explain this? It looks like you're calling to 2 new tables, but said to add 1 and show 1 at the top. I think I can work with something like this as a workaround if I understand it a little more. Thanks

[Mchl]

The other table `participants` would just be a list of all the participants (together with their ID of course).

[aebstract]

Which is what I have already, I just have the information stored on 1 row instead of multiple. So instead of making a new table like you said, just change the method to 1 row per round per user?

 

id | eventid | userid | round# | rt | et | speed

 

have a unique id, event id, user id, round number, reaction time, et, and speed for each row.

What does above. and below. refer to?

[Mchl]

'above' and 'below' are aliases for two subqueries used here.

(SELECT participant_id, MIN(time) AS time WHERE time >= 4.7 GROUP BY particpant_id) AS above
...
(SELECT participant_id, MAX(time) AS time WHERE time < 4.7 GROUP BY particpant_id) AS below

[aebstract]

The code you posted, won't return a result like this:

123

4.764.784.72

4.68 4.734.75

 

I can see it checking each one or whatever but don't see how it brings the results based on the three rounds for each. I might be looking at it wrongly. I set up a separate table just for these that are done in this way. I have id, regid, eventid, round, et, rt, mph.

 

regid = registered users id

eventid = event id

round = which round, possible options being: q1, q2, q3

et = decimal

rt = decimal

mph = decimal

 

The round time should actually be et+rt, but that shouldn't be hard to add in the end if I need to.

 

I just did this:

$query = mysql_query("SELECT
  p.regid, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  participants AS p
LEFT JOIN
  (SELECT regid, MIN(et) AS et WHERE et >= 4.7 GROUP BY regid) AS above
USING (regid)
LEFT JOIN
  (SELECT regid, MAX(et) AS et WHERE et < 4.7 GROUP BY regid) AS below
USING (participant_id)") or DIE(mysql_error());

while($row=mysql_fetch_array($query)){

echo "$row[et]";

}

 

just to see what I get, this query is going to have to be more specific as in class and event id, but with just this I got an error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE et >= 4.7 GROUP BY regid) AS above USING (regid) LEFT JOIN (SELECT regid' at line 6

 

[Mchl]

Yeah, this query only returns a time that should be taken into consideration for ranking, it does not do any ranking though.

As for the syntax error, I see nothing. Make sure there is no unnecessary comma (,) before WHERE

[aebstract]

Well, the code that I just pasted is the exact copy of what I ran to get the error.

[Mchl]

Oh wait... there's no FROM clause in these subqueries

$query = mysql_query("SELECT
  p.regid, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  participants AS p
LEFT JOIN
  (SELECT regid, MIN(et) AS et FROM results WHERE et >= 4.7 GROUP BY regid) AS above
USING (regid)
LEFT JOIN
  (SELECT regid, MAX(et) AS et FROM results WHERE et < 4.7 GROUP BY regid) AS below
USING (participant_id)") or DIE(mysql_error());

 

how come I made a msitake like this?

[aebstract]

Okay, I've got this query working sort of:

$query = mysql_query("SELECT
  i.regid, i.eventid, i.et, r.firstname, r.lastname, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  index_q as i
LEFT JOIN
  (SELECT regid, MIN(et) AS et FROM index_q WHERE et >= 4.7 GROUP BY regid) AS above
USING (regid)
LEFT JOIN
  (SELECT regid, MAX(et) AS et FROM index_q WHERE et < 4.7 GROUP BY regid) AS below
USING (regid)
LEFT JOIN registrations AS r  ON r.id = i.regid
WHERE r.class = '$var' && i.eventid = '$_GET[event]'") or DIE(mysql_error());

if(mysql_num_rows($query)!=0){

	while($r=mysql_fetch_array($query))
	{
$content .= "$r[firstname] $r[lastname] $r[et]<br />";
	}
						}

 

With an output of this:

Kenny Acree 4.730

Blake Wilder 4.685

Jeremy Ellis 5.044

Randy Alexander 4.822

Scott Underwood 4.730

Isreal Deraney 4.794

Jody Voyles 0.000

Mike Ghee 0.000

Mike Herring 4.616

Jeff Wilkerson 5.026

Kenny Acree 4.730

Blake Wilder 5.161

Jeremy Ellis 5.044

Randy Alexander 4.755

Scott Underwood 4.706

Isreal Deraney 4.732

Jody Voyles 4.973

Mike Ghee 5.045

Mike Herring 4.669

Jeff Wilkerson 4.902

Kenny Acree 4.730

Blake Wilder 5.023

Jeremy Ellis 5.044

Randy Alexander 4.755

Scott Underwood 4.706

Isreal Deraney 4.732

Jody Voyles 4.973

Mike Ghee 5.045

Mike Herring 4.686

Jeff Wilkerson 4.902

 

This doesn't seem to have any ordering to it though, and is there any way to loop through each person individually so I can put their times beside eachother when I loop through?

 

firstname lastname et1 et2 et3

 

 

[Mchl]

First tell us, if the times selected with this query are those relevant for ranking.

[aebstract]

[quote[Kenny Acree 4.730

Blake Wilder 4.685

Jeremy Ellis 5.044

Randy Alexander 4.822

Scott Underwood 4.730

Isreal Deraney 4.794

Jody Voyles 0.000

Mike Ghee 0.000

Mike Herring 4.616

Jeff Wilkerson 5.026

 

Kenny Acree 4.730

Blake Wilder 5.161

Jeremy Ellis 5.044

Randy Alexander 4.755

Scott Underwood 4.706

Isreal Deraney 4.732

Jody Voyles 4.973

Mike Ghee 5.045

Mike Herring 4.669

Jeff Wilkerson 4.902

 

Kenny Acree 4.730

Blake Wilder 5.023

Jeremy Ellis 5.044

Randy Alexander 4.755

Scott Underwood 4.706

Isreal Deraney 4.732

Jody Voyles 4.973

Mike Ghee 5.045

Mike Herring 4.686

Jeff Wilkerson 4.902

 

It's just showing their q1, q2, q3 times. So it's showing all the times not just the one needed for ordering, it's just clumping them in groups.

[Mchl]

That's not what I was execting it to do :/

 

how about this:

 

SELECT
  r.firstname, r.lastname, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  registrations as r
LEFT JOIN
  (SELECT regid, MIN(et) AS et FROM index_q WHERE et >= 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS above
USING (ON r.id = above.regid)
LEFT JOIN
  (SELECT regid, MAX(et) AS et FROM index_q WHERE et < 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS below
USING (ON r.id = below.regid)
WHERE r.class = '$var'}

 

Again, this is supposed to return only contestant names, and time used for ranking. Nothing else at this moment.

[aebstract]

$query = mysql_query("SELECT
  r.firstname, r.lastname, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  registrations as r
LEFT JOIN
  (SELECT regid, MIN(et) AS et FROM index_q WHERE et >= 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS above
USING (ON r.id = above.regid)
LEFT JOIN
  (SELECT regid, MAX(et) AS et FROM index_q WHERE et < 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS below
USING (ON r.id = below.regid)
WHERE r.class = '$var'") or DIE(mysql_error());

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON r.id = above.regid) LEFT JOIN (SELECT regid, MAX(et) AS et FROM index_q WHE' at line 7

[Mchl]

Lol... too fast...

 

$query = mysql_query("SELECT
  r.firstname, r.lastname, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  registrations as r
LEFT JOIN
  (SELECT regid, MIN(et) AS et FROM index_q WHERE et >= 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS above
ON r.id = above.regid
LEFT JOIN
  (SELECT regid, MAX(et) AS et FROM index_q WHERE et < 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS below
ON r.id = below.regid
WHERE r.class = '$var'") or DIE(mysql_error());