Selecting a specific value within a field?

[UOTS-Owner]

Anyone know how to select a certain value in this field? For instance the 6th one in which shows as 1

https://ibb.co/wzTtxKG

This is the current setup but it just selects the first number every time.

 

SELECT `usr_id`,SUBSTRING_INDEX(usr_stat,':',6) AS offences
        FROM `usr_tbl` 
        ORDER BY `offences` DESC 
        LIMIT 25

[Spydre452]

6 minutes ago, UOTS-Owner said:

Anyone know how to select a certain value in this field? For instance the 6th one in which shows as 1

https://ibb.co/wzTtxKG

This is the current setup but it just selects the first number every time.

 

SELECT `usr_id`,SUBSTRING_INDEX(usr_stat,':',6) AS offences
        FROM `usr_tbl` 
        ORDER BY `offences` DESC 
        LIMIT 25

Store the results in an array and recall it using an array key

[UOTS-Owner]

4 minutes ago, Spydre452 said:

Store the results in an array and recall it using an array key

Yeah this is in here but I dont know how to select it 

 

https://ibb.co/P5gJFGp

[KyleMassacre]

I would have personally serialized it but I guess you could explode it and get the index number you want 

[UOTS-Owner]

12 hours ago, KyleMassacre said:

I would have personally serialized it but I guess you could explode it and get the index number you want 

Could I get an example please to work off say for replacing the original one?

 

SELECT `usr_id`,SUBSTRING_INDEX(usr_stat,':',6) AS offences
        FROM `usr_tbl` 
        ORDER BY `offences` DESC 
        LIMIT 25

[KyleMassacre]

$query = $mysql->query("SELECT `usr_id`, `usr_stat` AS offenses
        FROM `usr_tbl` 
        ORDER BY `offenses` DESC 
        LIMIT 25");
$result = $mysql->fetch($query);

$offenses = explode($result['offenses]);

print $offenses[6];

You can't really do what you want to do in MySQL unless you create a function and I don't know if that is possible for everyone to do depending on your hosting.

But if it is something you would want to look into you can look here

[Maz]

If all the number values are from 0 to 9, this is valid:
 

SELECT usr_id, SUBSTRING(usr_stat, 11, 1) AS offences
FROM `usr_tbl` 
ORDER BY `offences` DESC 
LIMIT 25;
         

Edited October 4 by Maz

[smith]

??