a numbered column

[glendango]

Hi, any one know how to display a simple numeric value next to the results of a select query?   why cant i find the correct search term to do this?? i want the result to be numbered with a simple 1-10 position.=column =   pos

 

 

 

pos           name         id        leads

 

 

1              Williams         1             12

2              bond               9             4

3              brown             5              3

4             williams            3              3

5             you                   6             2

6             eyes                 7             2

etc

 

 

 

 

 

i can only find row_num which gives me the record number of the row...

Edited November 8, 2017 by glendango

[phpmillion]

Could you explain it more (with an example of desired output)?

[requinix]

Use a variable. Start with the number 1, then increment it in your loop.

[glendango]

that is desired output,,,numbers next to each row.    pos

 

thx lazy i will try and make something up from your answer. Unless anyone else got a quick answer. 

[Barand]

If, however, you want to show their rankings then it gets a bit more complicated

 

Pos   Rank     Name            ID            Leads
----+-------+---------------+----------+-------------
1       1      Williams         1             12
2       2      bond             9              4
3       3      brown            5              3
4       3      williams         3              3
5       5      you              6              2
6       5      eyes             7              2

[phpmillion]

Ah, got it now... Then just take requinix's advice it's the easiest solution possible. It only requires a few extra characters in your code, so you won't find an easier/quicker solution.

[glendango]

barand knows about my lack of memory and stupidity.... can i have more of a clue please

barand has posted the result with no code...  he is getting angrier every day.. 

[glendango]

i will go for the increment of 1 answer for now...rankings is just a dream at moment

[Barand]

There is no code because I was trying to ascertain if that is what was required first.

[glendango]

yes very much so...  the rank would be another level if you want to save me another day of stack overflow pain. 

[requinix]

Do you have the code in place that does the simple counting? Post what you've done - you can borrow much of how that works to add in ranking.

[Barand]

Here's how to do it with an SQL query (@seq etc are variables used to store values from one record for use in the next as the records are processed)

SELECT id
     , name
     , @seq := @seq + 1 as seq
     , @rank := CASE WHEN leads = @prev THEN @rank ELSE @seq END as rank
     , @prev := leads as leads
FROM leads
JOIN (SELECT @seq:=0, @rank:=0, @prev:=0 ) as initialize
ORDER BY leads DESC

You would do it same way using PHP. The $seq is always incremented. If the leads value in a row is the same as the previous row then the $rank is the same as the previous $rank. If the leads values are different then the $rank is the same as the $seq

[glendango]

SELECT usr_id , @seq := @seq + 1 as seq FROM firsts JOIN (SELECT @seq:=0, @rank:=0, @prev:=0 ) as initialize GROUP BY usr_id DESC

​

 

 

this is great...

simplified it to spit out the sequence on MySQL for just the sequence.cheers....  i changed to group as well so it kept all users to 1 entry. 

 

no wi will work on the rank part . 

 

 

 

another annoying question for you...

 

 

every time i try an do something new on my app it takes 1 day and at the end of the day i ask for help and you help me....this cant be correct...  ( bearing in mind iam not a dev and only been coding a few months..)  

 

i know the answer to this is to employ a pro app creater , but what do they use??  do they actually sit there all day working this stuff out for their custoemrs or does symphony, laraval frameworks etc do it all for you???  i cant see how they would do it all for you as iam asking for quite specific things to make my app niche.. would love some inside info as to best practice // give up ( which i wont lol) 

Edited November 8, 2017 by glendango

[Barand]

, but what do they use?? 

 

Experience (although there are plenty of "developers" out there who have little or none).

[requinix]

Frameworks can make the work go much faster, if one has the support you need. Like I've spent a long (and I cannot emphasize that enough) time working on my own framework for my own code, but with most of the important work now in place I can crank out the more interesting "app" stuff in a fraction of the time it would take without.

 

Nearly every framework out there helps with database access so you don't have to do the same tedious work over and over every time you want to run a query. For example, this

$results = db()->prepare("SELECT * FROM table WHERE column = :value")->query(["value" => $_GET["value"]]);

code written with a framework would correspond to... uh... I figure at least a minimum of a dozen lines without a framework. And it took seconds as opposed to a few minutes of finding similar code somewhere else, copying and pasting it, then adjusting the code as needed.

 

The rest is definitely experience and other learned knowledge.

[glendango]

thats good to hear!  

i will only blueprint mine then get it written properly.

 

i need to work through your code tomoorow when awake but the bit ive done :

 

 

SELECT usr_id ,count(date_made)as da, @seq := @seq + 1 as seq FROM firsts JOIN (SELECT @seq:=0, @rank:=0, @prev:=0 ) as initialize GROUP BY usr_id order by da DESC

 

 

does this...  the seq seems to be acting like row_num which keeps latching onto the row number rather then just give me a straight count. da in this case is the amount of leads,,, so rather then 

1,2,6,3,5,4

 

i want 

 

1,2,3,4,5,6  

 

usr_id     da       seq  

1             12        1

3              5         2

9              4         6

5              2         3

7              2          5

6              1           4

 

this is the real select i am working with: on main app if it helps :  

 

$result = mysqli_query($conn, "SELECT u.name , u.surname, f.usr_id, COUNT(f.usr_id) AS totalfirstsever FROM users u JOIN firsts f on u.id  = f.usr_id  group by f.usr_id order by totalfirstsever DESC limit 10 ");

Edited November 8, 2017 by glendango

[glendango]

this works well - gives me numbers by the side of users :

 

set @rownum := 0;
select usr_id, count(date_made) as date , @rownum := @rownum + 1 as row_number from firsts group by usr_id 

 

when i add 'order by date' at the end of the select, the  numbers follow the usr_id around and don't stay in sequence i.e 1,2,3,4,5

 

this is what all answers do on stack as well... 

.   

Edited November 9, 2017 by glendango

[requinix]

I'm pretty sure that's because of when MySQL evaluates the variables and assignments: when the rows are pulled from the table. If you ORDER BY an indexed column then MySQL will use that and the numbers will be in order, but when you sort by something else the numbers will be out of order.

 

Keeping this approach, do a subquery. Converting Barand's original query,

SELECT id
     , name
       /* the ranking happens outside */
     , @seq := @seq + 1 as seq
     , @rank := CASE WHEN leads = @prev THEN @rank ELSE @seq END as rank
     , @prev := leads as leads
FROM (
	/* moved the main bulk of the query inside */
	SELECT *
	FROM leads
	ORDER BY leads DESC
) as leads
JOIN (SELECT @seq:=0, @rank:=0, @prev:=0 ) as initialize
/* sorting happened inside */

[Barand]

You are trying to allocate the rownum before the count totals are known. You need to put your ducks in a row before you can allocate the correct sequence or ranking. To do this you need a temporary table of the counts for each user which you can do by creating a table subquery. I also use a subquery to initialize the @ values (it saves have to do SET queries first).