[How to Mysql count only higest values per row]

Super cool and clean how you write that

Thank you very much sir

[How to Mysql count only higest values per row]

3 hours ago, stav said:

There was typo

Desired result set:

Country | V1| V2 | V3 | V4 | V5 | V6
-------- ---- --- ---- ---- ---- ----
USA     | 0 | 0  | 0  |  0 | 0  |  1
FRANCE  | 0 | 0  | 2  |  0 | 0  |  0

 

[How to Mysql count only higest values per row]

I would greatly appreciate it if you could help me solve this oneI would be grateful if you could/would write a simple example on how to acomplish that

Thaks sir

[How to Mysql count only higest values per row]

I have 2 tables and I want to count highest version per country for all users

Example:

John is from the USA, and he has versions 1,2,3,4,5 and 6

Bob is from France, and he has versions 1,2 and 3

Mary is from France too, and she has versions 1,2 and 3

I want to count just the last versions from anny of them

Becouse :

John has higher version 6 he won't be counted in versions lower than 6

Bob has higher version 3 he won't be counted in versions lower than 3

Mary has higher version 3 she won't be counted in versions lower than 3

So the final result will be as the Desired result set at bottom

Table1
ID | USER  |  COUNTRY
---- ----- ----------
1  |  John |  USA
2  |  Bob  |  FRANCE
3  |  Mary |  FRANCE



Table2
ID |  USER  | VERSION
---- ------- ---------
1  |  John  |  1
2  |  Bob   |  1 
3  |  John  |  2 
4  |  Mary  |  1 
5  |  Mary  |  2 
6  |  John  |  3 
7  |  John  |  4 
8  |  Bob   |  2 
9  |  John  |  5 
10 |  Bob   |  3 
11 |  Mary  |  3 
12 |  John  |  6 

 

Desired result set:

Country | V1| V2 | V3 | V4 | V5 | V6
-------- ---- --- ---- ---- ---- ----
USA     | 0 | 0  | 0  |  1 | 1  |  1
FRANCE  | 0 | 0  | 2  |  0 | 0  |  0

 

Please help me , thanks.

[Prev Next No pagination Help]

I have this query

 
$sql = "SELECT vid,title,rating FROM video WHERE  approved=1 ORDER BY rating DESC LIMIT 10";

and this is the result video page

 

vid  |  title    | rating

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

1    |  title1  |  10

5    |  title5  |  12

34  |  title34 |  18

6    |  title6  |  20

12  |  title12 |  20

8    |  title8  |  20

54  |  title54 |  20

23  |  title23 |  43

37  |  title37 |  44

3    |  title3  |  48

 

now i have a link in the video result page when user click on video he can see that video in video preview page

there i want to ad <<prev and next>> button so user can go to next or prev video from the video resut page

 

 

 

 

if the user watch video vid 12  next will be video with vid  8 and prev the video with vid 6

 

 

$nextsql  = "SELECT vid,title,rating FROM video WHERE rating >= (SELECT rating from video WHERE vid=12) AND vid!=12 AND approved=1 ORDER BY rating DESC LIMIT 1";
$prevsql  = "SELECT vid,title,rating FROM video WHERE rating <= (SELECT rating from video WHERE vid=12) AND vid!=12 AND approved=1 ORDER BY rating ASC LIMIT 1";

 

but i have problem when there are videos with same rating values

 

Can some one help me to find prev and next vid  order by rating in this example

 

Thank you!

[[SOLVED] how find value from mysql array record]

Thank you !!

[[SOLVED] how find value from mysql array record]

I have MySql db_table with column 'days_in_week' with values like

 

record_id1 = weekly|99|MO|TU|WE|TH|FR

record_id2 = weekly|99|TU|TH|FR

record_id3 = weekly|99|MO|WE

record_id4 = weekly|99|SA

 

etc..

How can i find which record has e.g. WE in the array separated by |.

 

 

e.g.

 

$Today = substr(strtoupper(date('l')),0,2);

 

//$Today='WE';

 

 

I was trying REGEXPthis but i have one wrong result back.

 

 

 

$mysql_Query = "SELECT from db_table where days_in_week REGEXP '".$Today."' ";

 

 

How can i do that something like PHP  in_array() function.

 

Thanks.