Coding Forum

**Sydpix** · 01-12-2010, 09:41 AM

I need to display the order results are queried in order by points to display 1st place, 2nd place, 3rd place ect..

Will I need to write a php array to handle some sort of re-write?

This is my query:

$query=" SELECT * FROM anglers ORDER BY Points+0 DESC,Fish_Length+0 DESC limit $eu, $limit ";

Display:

echo "<tr border='1'style='border-color:#95918a;width:100%;border-collapse:collapse;' onclick='toggle(this);' >";
echo "<td class='heading2 width='1%'>$data[id]</td>";
echo "<td width='25%'>$data[Angler_Name]</td>";
echo "<td width='5%'>$data[Points]</td>";
echo "<td width='14%'>$data[Location]</td>";
echo "<td width='17%'>$$data[Payout]</td>";
echo "</tr>";

I need [id] to display in order even though the query is ordering by another field.

Thanks!

**tripy** · 10:47 AM

What you want is a running total.
Look there for a viable mysql solution:
http://stackoverflow.com/questions/6...total-in-mysql

I don't know if this can be called like this from PHP though, as PHP fires only 1 statement at a time.
You may need to create a stored procedure on mysql to use it.

Look there for a reference on how to create a stored procedure:
http://dev.mysql.com/doc/refman/5.1/...procedure.html

**Sydpix** · 10:55 PM

Running totals seems to add to the prior totals. I'm not necessarily looking to carry running totals as I am just numbering queried results in order in the first row.

1
2
3
4
5

Even though my first row are id's which are random based on my queried results, I want to (for lack of terminology) "overwrite" or "re-write" them to display as above no matter what order the query is calling.

In other words, if I want to query data "SELECT * FROM foo ORDER BY Points" I want the first column to display in chronological numbered order.

Maybe I'm confusing your reply?

**tripy** · 03:13 AM

Quote:

Maybe I'm confusing your reply?

Yep...
What I meant was:
create a running total column in your select that will be the count of previous rows, that you will use as the "new order id".

Though, personally, I'd be creating a temporary table, populate it with the result of the select, and return that temporary table, with an auto-incremented column.

Try to run this from a mysql prompt to see it in action:

Code:

drop table IF EXISTS run;

create table run (
    id integer auto_increment primary key,
    val varchar(100)
);

insert into run (val) values ('I am line number 1');
insert into run (val) values ('I am line number 2');
insert into run (val) values ('I am line number 3');
insert into run (val) values ('I am line number 4');
insert into run (val) values ('I am line number 5');
insert into run (val) values ('I am line number 6');
insert into run (val) values ('I am line number 7');
insert into run (val) values ('I am line number 8');
insert into run (val) values ('I am line number 9');
insert into run (val) values ('I am line number 10');
insert into run (val) values ('I am line number 11');
insert into run (val) values ('I am line number 12');
insert into run (val) values ('I am line number 13');
insert into run (val) values ('I am line number 14');
insert into run (val) values ('I am line number 15');
insert into run (val) values ('I am line number 16');
insert into run (val) values ('I am line number 17');
insert into run (val) values ('I am line number 18');
insert into run (val) values ('I am line number 19');
insert into run (val) values ('I am line number 20');
insert into run (val) values ('I am line number 21');
insert into run (val) values ('I am line number 22');
insert into run (val) values ('I am line number 23');
insert into run (val) values ('I am line number 24');
insert into run (val) values ('I am line number 25');
insert into run (val) values ('I am line number 26');
insert into run (val) values ('I am line number 27');
insert into run (val) values ('I am line number 28');
insert into run (val) values ('I am line number 29');
insert into run (val) values ('I am line number 30');
insert into run (val) values ('I am line number 31');
insert into run (val) values ('I am line number 32');
insert into run (val) values ('I am line number 33');
insert into run (val) values ('I am line number 34');
insert into run (val) values ('I am line number 35');
insert into run (val) values ('I am line number 36');
insert into run (val) values ('I am line number 37');
insert into run (val) values ('I am line number 38');
insert into run (val) values ('I am line number 39');
insert into run (val) values ('I am line number 40');
insert into run (val) values ('I am line number 41');
insert into run (val) values ('I am line number 42');
insert into run (val) values ('I am line number 43');
insert into run (val) values ('I am line number 44');
insert into run (val) values ('I am line number 45');
insert into run (val) values ('I am line number 46');
insert into run (val) values ('I am line number 47');
insert into run (val) values ('I am line number 48');
insert into run (val) values ('I am line number 49');
insert into run (val) values ('I am line number 50');
insert into run (val) values ('I am line number 51');
insert into run (val) values ('I am line number 52');
insert into run (val) values ('I am line number 53');
insert into run (val) values ('I am line number 54');
insert into run (val) values ('I am line number 55');
insert into run (val) values ('I am line number 56');
insert into run (val) values ('I am line number 57');
insert into run (val) values ('I am line number 58');
insert into run (val) values ('I am line number 59');
insert into run (val) values ('I am line number 60');
insert into run (val) values ('I am line number 61');
insert into run (val) values ('I am line number 62');
insert into run (val) values ('I am line number 63');
insert into run (val) values ('I am line number 64');
insert into run (val) values ('I am line number 65');
insert into run (val) values ('I am line number 66');
insert into run (val) values ('I am line number 67');
insert into run (val) values ('I am line number 68');
insert into run (val) values ('I am line number 69');
insert into run (val) values ('I am line number 70');
insert into run (val) values ('I am line number 71');
insert into run (val) values ('I am line number 72');
insert into run (val) values ('I am line number 73');
insert into run (val) values ('I am line number 74');
insert into run (val) values ('I am line number 75');
insert into run (val) values ('I am line number 76');
insert into run (val) values ('I am line number 77');
insert into run (val) values ('I am line number 78');
insert into run (val) values ('I am line number 79');
insert into run (val) values ('I am line number 80');
insert into run (val) values ('I am line number 81');
insert into run (val) values ('I am line number 82');
insert into run (val) values ('I am line number 83');
insert into run (val) values ('I am line number 84');
insert into run (val) values ('I am line number 85');
insert into run (val) values ('I am line number 86');
insert into run (val) values ('I am line number 87');
insert into run (val) values ('I am line number 88');
insert into run (val) values ('I am line number 89');
insert into run (val) values ('I am line number 90');
insert into run (val) values ('I am line number 91');
insert into run (val) values ('I am line number 92');
insert into run (val) values ('I am line number 93');
insert into run (val) values ('I am line number 94');
insert into run (val) values ('I am line number 95');
insert into run (val) values ('I am line number 96');
insert into run (val) values ('I am line number 97');
insert into run (val) values ('I am line number 98');
insert into run (val) values ('I am line number 99');

drop temporary table IF EXISTS tmpRes;

create temporary table tmpRes(
  newId integer auto_increment primary key,
  oldId integer,
  val varchar(100)
);

insert into tmpRes(
    oldId,
    val
)
select id, val
from run
order by rand();

select * from tmpRes;

drop temporary table IF EXISTS tmpRes;

The result is something like

Code:

+-------+-------+---------------------+
| newId | oldId | val                 |
+-------+-------+---------------------+
|     1 |     8 | I am line number 8  |
|     2 |    74 | I am line number 74 |
|     3 |     3 | I am line number 3  |
|     4 |    95 | I am line number 95 |
|     5 |     7 | I am line number 7  |
|     6 |    67 | I am line number 67 |
|     7 |    77 | I am line number 77 |
|     8 |    20 | I am line number 20 |
|     9 |    57 | I am line number 57 |
|    10 |    21 | I am line number 21 |
|    11 |    79 | I am line number 79 |
|    12 |    59 | I am line number 59 |
|    13 |    33 | I am line number 33 |
|    14 |    55 | I am line number 55 |
|    15 |    51 | I am line number 51 |
|    16 |    83 | I am line number 83 |
|    17 |    63 | I am line number 63 |
|    18 |    47 | I am line number 47 |
|    19 |    82 | I am line number 82 |
|    20 |    36 | I am line number 36 |
|    21 |    22 | I am line number 22 |
|    22 |     4 | I am line number 4  |
|    23 |    76 | I am line number 76 |
|    24 |    38 | I am line number 38 |
|    25 |    93 | I am line number 93 |
|    26 |    29 | I am line number 29 |
|    27 |    54 | I am line number 54 |
|    28 |    84 | I am line number 84 |
|    29 |    11 | I am line number 11 |
|    30 |    43 | I am line number 43 |
|    31 |    96 | I am line number 96 |
|    32 |    40 | I am line number 40 |
|    33 |    69 | I am line number 69 |
|    34 |    42 | I am line number 42 |
|    35 |    98 | I am line number 98 |
|    36 |    34 | I am line number 34 |
|    37 |    99 | I am line number 99 |
|    38 |    45 | I am line number 45 |
|    39 |    32 | I am line number 32 |
|    40 |    39 | I am line number 39 |
|    41 |    53 | I am line number 53 |
|    42 |    60 | I am line number 60 |
|    43 |    80 | I am line number 80 |
|    44 |    92 | I am line number 92 |
|    45 |    35 | I am line number 35 |
|    46 |    44 | I am line number 44 |
|    47 |    46 | I am line number 46 |
|    48 |    13 | I am line number 13 |
|    49 |    97 | I am line number 97 |
|    50 |    48 | I am line number 48 |
|    51 |    41 | I am line number 41 |
|    52 |    65 | I am line number 65 |
|    53 |    66 | I am line number 66 |
|    54 |    87 | I am line number 87 |
|    55 |    25 | I am line number 25 |
|    56 |    18 | I am line number 18 |
|    57 |    37 | I am line number 37 |
|    58 |    52 | I am line number 52 |
|    59 |    70 | I am line number 70 |
|    60 |    31 | I am line number 31 |
|    61 |    86 | I am line number 86 |
|    62 |    78 | I am line number 78 |
|    63 |    75 | I am line number 75 |
|    64 |    58 | I am line number 58 |
|    65 |    17 | I am line number 17 |
|    66 |    14 | I am line number 14 |
|    67 |    68 | I am line number 68 |
|    68 |    23 | I am line number 23 |
|    69 |    72 | I am line number 72 |
|    70 |    88 | I am line number 88 |
|    71 |    50 | I am line number 50 |
|    72 |     6 | I am line number 6  |
|    73 |    19 | I am line number 19 |
|    74 |    90 | I am line number 90 |
|    75 |    28 | I am line number 28 |
|    76 |    49 | I am line number 49 |
|    77 |    73 | I am line number 73 |
|    78 |    61 | I am line number 61 |
|    79 |    10 | I am line number 10 |
|    80 |    12 | I am line number 12 |
|    81 |     5 | I am line number 5  |
|    82 |    30 | I am line number 30 |
|    83 |    81 | I am line number 81 |
|    84 |    71 | I am line number 71 |
|    85 |    85 | I am line number 85 |
|    86 |    56 | I am line number 56 |
|    87 |    27 | I am line number 27 |
|    88 |    26 | I am line number 26 |
|    89 |    16 | I am line number 16 |
|    90 |    24 | I am line number 24 |
|    91 |    62 | I am line number 62 |
|    92 |    94 | I am line number 94 |
|    93 |    91 | I am line number 91 |
|    94 |     1 | I am line number 1  |
|    95 |     9 | I am line number 9  |
|    96 |    89 | I am line number 89 |
|    97 |    15 | I am line number 15 |
|    98 |     2 | I am line number 2  |
|    99 |    64 | I am line number 64 |
+-------+-------+---------------------+
99 rows in set (0.00 sec)

The advantage of this, is that you don't declare a variable in mysql.
Thus, if you use a "real" table instead of a temporary table to hold your datas, you can simply send each query from php in that order, and drop your pseudo-temp table after that.

**chrishirst** · 01-13-2010, 05:23 AM

Quote:

In other words, if I want to query data "SELECT * FROM foo ORDER BY Points" I want the first column to display in chronological numbered order.

"chronological"?? there doesn't seem to be any mention of dates or times in your query.

Is it that you are wanting to show a list of members in ID order and show where they are placed in the competition?

**Sydpix** · 01-13-2010, 08:04 AM

Nope, I guess I confused the definition of "chronological" I should have used numerical order.

Sorry.

**Sydpix** · 01-13-2010, 08:14 AM

Thanks tripy, I'll give that a run,

**chrishirst** · 01-13-2010, 08:18 AM

No problem. Just like to make sure we are all on the "same page".

So what is the purpose of the query?

**Sydpix** · 01-13-2010, 09:45 AM

The purpose is to display standings for AOY (Angler of the Year). Points are accumulated all year long. At the end of the year, the one with the most points wins.

I want to display the individual standings ORDERed by points but show 1st place, 2nd place, 3rd place in numerical order.

**chrishirst** · 01-13-2010, 10:22 AM

Two queries:

One to pull the IDs (only the IDs) of the top three in points order.
The second to pull the rows in ID order. (all the fields you need)

As you display the records from the full query, loop through the top3 query and if the ID matches, use the array index to display the placing.