count row with different condition

[Boskor]

I have another problem with mysql.

I have 3 tables (Users, User_status and Analysis). Users and User_status are in relation (Users.User_status_name=User_status.user_status) and Users and Analysis are in relation (Users.Username=Analysis.Tipster).

I want to have table in php:

Tipster  Form
boskor        1W 1V 0L
slopart       0W 1W 2L

In table Analysis I have row (Status_name with these options (Won, Void, Lost)). Now I want to have how many rows have status Won, how many rows status Void and status Lost.I also want to limit this (won+void+lost<=20) and ORDER BY Status_name.

I have this table:

$tipster = mysql_query("SELECT Users.id, Users.Username, User_status.id_status
FROM Users
INNER JOIN User_status 
ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis 
ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
GROUP BY Tipster"); 
while ($row = mysql_fetch_array($tipster)) {
	$sel_tipster=$row['Username'];
	$sel_tipster_id=$row['id'];
	echo "<tr>";
	echo "<td>";
	echo $sel_tipster;
	echo "</td>";

I don't know how to add count or mysql_num_rows code into this query.

$tipster = mysql_query("SELECT Users.id, Users.Username, User_status.id_status
FROM Users
INNER JOIN User_status 
ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis 
ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
GROUP BY Tipster"); 
echo mysql_num_rows($tipster);
if (mysql_num_rows($tipster) > 0) { 
while ($row = mysql_fetch_row($tipster)) {
	$sel_tipster=$row[0];
	$sel_tipster_id=$row[1];
	echo "<tr>";
	echo "<td>";
	echo $sel_tipster;
	echo "</td>";
 } 
}

[Boskor]

$tipster = mysql_query("SELECT Users.id, Users.Username, User_status.id_status
FROM Users
INNER JOIN User_status 
ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis 
ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
GROUP BY Tipster"); 
while ($row = mysql_fetch_array($tipster)) {
	$sel_tipster=$row['Username'];
	echo "<tr>";
	echo "<td>";
	echo $sel_tipster;
	echo "</td>";

This first part is OK and with this I have list of tipsters in php table. I need to write number of rows where Analysis.Status_name=Won, Analysis.Status_name=Void, Analysis.Status_name=Lost. LIMIT all on 20 (won+void+lost<=20) and ORDER BY Status_name=Won.

So I have to add code to have second <td></td> in php table.

I only know to write query for each other, so I have 3 queries then + query for tipsters and I can't limit all together and I don't know hor to ORDER BY Status_name=Won.

So it is example:

$won=mysql_query("SELECT Status_name FROM Analysis WHERE Tipster='$sel_tipster' AND Status_name='Won'"

and than $win=mysql_num_rows($won);

First check whether you query returns the proper result as you expected from your phpadmin. If not try to solve the query in phpmyadmin. Ensure first the query is right or wrong?

[Boskor]

First check whether you query returns the proper result as you expected from your phpadmin. If not try to solve the query in phpmyadmin. Ensure first the query is right or wrong?

First query is right. I only have to add number of rows for different conditions, but I don't know how...

do you post the result of rows returned by sql

[Boskor]

do you post the result of rows returned by sql

Your code returned me number of tipster. I wrote in previous posts than I need something like that:

Tipster  Form
boskor   1W 1V 0L
slopart  0W 1W 2L

1W, 1V and 0L are rows of Analysis.Status_name. Where Status_name has these values: Won, Void and Lost.

I know how to write query for each of these values:

$won=mysql_query("SELECT Status_name FROM Analysis WHERE Tipster='$sel_tipster' AND Status_name='Won'");

But if I write such way, than I can't limit (Won+Void+Lost) and I can't ORDER BY Status_name='Won'

So I asked if it is possible to write such code in one query and how can I limit it and order it.

I don't have much idea of forming queries in sql. but we can make it a loop and perform the query with different Status_name;

I mean store the status whatever it is in an array
$arr=new array("Won","Void","Lost");
One more array to count the number of rows $arr1=new array();

for (i=0;i<sizeof($arr);i++)
{
 $result=mysql_query("SELECT Status_name FROM Analysis WHERE Tipster='$sel_tipster' AND Status_name=$arr[i]");
$arr1[i]=mysql_num_rows($result);
//display your records within a table using while
}

[urtrivedi]

SELECT Users.id, Users.Username, User_status.id_status, 
sum(case when Analysis.status_name='w' then 1 else 0 end) won,
sum(case when Analysis.status_name='v' then 1 else 0 end) void,
sum(case when Analysis.status_name='l' then 1 else 0 end) lost
FROM UsersINNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
GROUP BY Users.id, Users.Username, User_status.id_status
having (won+void+lost)<=20

[Boskor]

SELECT Users.id, Users.Username, User_status.id_status, 
sum(case when Analysis.status_name='w' then 1 else 0 end) won,
sum(case when Analysis.status_name='v' then 1 else 0 end) void,
sum(case when Analysis.status_name='l' then 1 else 0 end) lost
FROM UsersINNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
GROUP BY Users.id, Users.Username, User_status.id_status
having (won+void+lost)<=20

It works now, but if User have more than 20 (won+void+lost), he is not in php table anymore and I don't know how to order by Status_name=won. Is it possible to have last 20 (won+void+lost):
example: boskor have 22 (10w 2v 10l)
in table I will have:

Tipster Form
boskor  9W 1V 10L

The oldest status_name won't be in php table

[urtrivedi]

select Users.id, Users.Username, User_status.id_status,
sum(case when Analysis.status_name='w' then 1 else 0 end) won,
sum(case when Analysis.status_name='v' then 1 else 0 end) void,
sum(case when Analysis.status_name='l' then 1 else 0 end) lost
FROM UsersINNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
GROUP BY Users.id, Users.Username, User_status.id_status
order by won desc

i removed having (won+void+lost)<20. you where asking for limiting it thats why i place that conidtion.

[Boskor]

i removed having (won+void+lost)<20. you where asking for limiting it thats why i place that conidtion.

I will try to explain:

I like to have limit (won+void+lost), but in this case I don't have user in my php table if he has more than 20.

I like to limit:

example:

1 user has 22 status_name (10w 2v 10l), so in php table I will have only 20 status_name (9w 1v 10l), last 2 won't be on site (the oldest status_name).

I hope that I explain litlle better.

[urtrivedi]

You play with where clause of last query i have given. I think you will get it.

I am not able to understand what you are trying to tell.
You simply show sample data, you have and show me the expected output from it.

[Boskor]

You play with where clause of last query i have given. I think you will get it.

I am not able to understand what you are trying to tell.
You simply show sample data, you have and show me the expected output from it.

I will put some examples.

I have this is php table:

Tipster   Form
boskor  10W 0V 12L
slopart 9W  4V 6L

That's all working fine.

But now I want to limit w+v+l<20.

Your code doesn't show user with more than 20 (this is example):

Tipster Form
slopart  9W 4V 6L

boskor doesn't show, because he has more than 20 (w+v+l).

But I want to have both users in php table. If user have more than 20 (w+v+l) than in php table will be only 20 results.

example:

Tipster Form
boskor 10w 0v 10l
slopart 9w 4v 6l

Table Analysis in Mysql:

Date Event       Tipster Result Status_name
27.5 Boston-NJ   boskor  20:25    Won
26.5 xx-yy       slopart 13:14    Lost  

So I want to have last 20 where Status_name is Won or Void or Lost for every tipster.

[urtrivedi]

This is most complex query. I hope this is what you are looking for.

select a.id, a.username, a.id_status,sum(a.won) won, sum(a.void) void, sum(a.lost) lost

(

select Users.id, Users.Username, User_status.id_status, 
case when Analysis.status_name='Won' then 1 else 0 end won,
case when Analysis.status_name='Void' then 1 else 0 end void,
case when Analysis.status_name='Lost' then 1 else 0 end lost,
@row := case when @prevuser=Users.Username then @row + 1 else 1 end as rownum,
@prevuser:=Users.Username
FROM Users,(SELECT @row := 0, @prevuser:=null ) r  
INNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
order by Users.Username, Analysis.Tipster date

) a
where a.rownum <=20
GROUP BY a.id, a.Username, a.id_status

[Boskor]

This is most complex query. I hope this is what you are looking for.

select a.id, a.username, a.id_status,sum(a.won) won, sum(a.void) void, sum(a.lost) lost

(

select Users.id, Users.Username, User_status.id_status, 
case when Analysis.status_name='Won' then 1 else 0 end won,
case when Analysis.status_name='Void' then 1 else 0 end void,
case when Analysis.status_name='Lost' then 1 else 0 end lost,
@row := case when @prevuser=Users.Username then @row + 1 else 1 end as rownum,
@prevuser:=Users.Username
FROM Users,(SELECT @row := 0, @prevuser:=null ) r  
INNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
order by Users.Username, Analysis.Tipster date

) a
where a.rownum <=20
GROUP BY a.id, a.Username, a.id_status

Can you please explain this code? I have problem

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

...

[urtrivedi]

is it working on phpmyadmin directly. please check whether there is a syntax error.

[Boskor]

is it working on phpmyadmin directly. please check whether there is a syntax error.

I tried in phpmyadmin and get errors. First on line 13 (where a.rownum<=20), if I delete this row than on next line (GROUP BY a.id, a.Username, a.id_status) and if delete this line then there is error (unknown tabele a).

I don't understand your code:
1. Why are there a.id, a.Username...
2. FROM Users, (SELECT @row := 0, @prevuser:=null ) r why is here r?

[urtrivedi]

try to run internal query first and have a look. make syntax correct, then try whole query. if you still not able to run then attacht script of database and data for thest two tables i will run it here.

select Users.id, Users.Username, User_status.id_status,
 case when Analysis.status_name='Won' then 1 else 0 end won,
case when Analysis.status_name='Void' then 1 else 0 end void,
case when Analysis.status_name='Lost' then 1 else 0 end lost,
@row := case when @prevuser=Users.Username then @row + 1 else 1 end as rownum,
@prevuser:=Users.Username 
FROM Users,(SELECT @row := 0, @prevuser:=null ) r  
INNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster 
WHERE User_status.id_status>=3
order by Users.Username, Analysis.Tipster date

(SELECT @row := 0, @prevuser:=null ) r
this is used to initialise two variables @row and @prevuser

@row := case when @prevuser=Users.Username then @row + 1 else 1 end as rownum
with every row i m trying to generate sr no for each user of if. its a first row compare my prevauser variable with username in table if both are same then add one to srno. if username in prev row and current row is different then reset srno to 1 for new user.

@prevuser:=Users.Username
save current username to our prevuser variable to compare in next row.

result of inner query will be like following

id, username, id_status, won, void, lost, rownum
1, abc, xyz, 1,0,0, 1
1, abc, xyz, 0,1,0, 2
1, abc, xyz, 1,0,0, 3
1, abc, xyz, 0,0,1, 4
1, abc, xyz, 0,1,0, 5
2, pqr, xyz, 0,1,0, 1
2, pqr, xyz, 0,1,0, 2
2, pqr, xyz, 0,0,1, 3
2, pqr, xyz, 0,1,0, 4

here first row is the latest row for abc its ordered by date

now above result set is again summed using ( ) a where

so if you want top 3 rows for each user then u may write following query

select a.id, a.username, a.id_status,sum(a.won) won, sum(a.void) void, sum(a.lost) lost
from (innerquery) a
where a.rownum<=3
GROUP BY a.id, a.Username, a.id_status

result will be

id, username, id_status, won, void, lost
1, abc, xyz, 2,1,0
2, pqr, xyz, 0,2,1

4th and 5th for abc ignored
4th for pqr is ignored

I hope you have got it

[Boskor]

try to run internal query first and have a look. make syntax correct, then try whole query. if you still not able to run then attacht script of database and data for thest two tables i will run it here.

select Users.id, Users.Username, User_status.id_status,
 case when Analysis.status_name='Won' then 1 else 0 end won,
case when Analysis.status_name='Void' then 1 else 0 end void,
case when Analysis.status_name='Lost' then 1 else 0 end lost,
@row := case when @prevuser=Users.Username then @row + 1 else 1 end as rownum,
@prevuser:=Users.Username 
FROM Users,(SELECT @row := 0, @prevuser:=null ) r  
INNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster 
WHERE User_status.id_status>=3
order by Users.Username, Analysis.Tipster date

(SELECT @row := 0, @prevuser:=null ) r
this is used to initialise two variables @row and @prevuser

@row := case when @prevuser=Users.Username then @row + 1 else 1 end as rownum
with every row i m trying to generate sr no for each user of if. its a first row compare my prevauser variable with username in table if both are same then add one to srno. if username in prev row and current row is different then reset srno to 1 for new user.

@prevuser:=Users.Username
save current username to our prevuser variable to compare in next row.

result of inner query will be like following

id, username, id_status, won, void, lost, rownum
1, abc, xyz, 1,0,0, 1
1, abc, xyz, 0,1,0, 2
1, abc, xyz, 1,0,0, 3
1, abc, xyz, 0,0,1, 4
1, abc, xyz, 0,1,0, 5
2, pqr, xyz, 0,1,0, 1
2, pqr, xyz, 0,1,0, 2
2, pqr, xyz, 0,0,1, 3
2, pqr, xyz, 0,1,0, 4

here first row is the latest row for abc its ordered by date

now above result set is again summed using ( ) a where

so if you want top 3 rows for each user then u may write following query

select a.id, a.username, a.id_status,sum(a.won) won, sum(a.void) void, sum(a.lost) lost
from (innerquery) a
where a.rownum<=3
GROUP BY a.id, a.Username, a.id_status

result will be

id, username, id_status, won, void, lost
1, abc, xyz, 2,1,0
2, pqr, xyz, 0,2,1

4th and 5th for abc ignored
4th for pqr is ignored

I hope you have got it

I still have a problems (#1054 - Unknown column 'Users.User_status_name' in 'on clause'). But this line is good (I checked names).

I attached 2 tzables (Analysis and Users) in pdf.

[urtrivedi]

I was expecting sql script not a pdf file. so that i can create same environment as you are having. to find the syntax error. also send 3rd tables that u use in your query.

[Boskor]

I was expecting sql script not a pdf file. so that i can create same environment as you are having. to find the syntax error. also send 3rd tables that u use in your query.

Table structure for table `Analysis`
--

CREATE TABLE IF NOT EXISTS `Analysis` (
`an_id` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Pick` text NOT NULL,
`Date` date NOT NULL,
`Time` time NOT NULL,
`Sport` varchar(20) NOT NULL,
`Tipster` varchar(50) NOT NULL,
`League` varchar(50) NOT NULL,
`Odds` float NOT NULL,
`Stake` int(2) NOT NULL,
`Bookie` varchar(50) NOT NULL,
`Comment` text NOT NULL,
`Result` varchar(20) NOT NULL,
`Status_name` varchar(40) NOT NULL,
`Profit` varchar(5) NOT NULL,
PRIMARY KEY (`an_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `Analysis`
--

INSERT INTO `Analysis` (`an_id`, `Name`, `Pick`, `Date`, `Time`, `Sport`, `Tipster`, `League`, `Odds`, `Stake`, `Bookie`, `Comment`, `Result`, `Status_name`, `Profit`) VALUES
(1, 'Hrvaška - Bosna', '2.05', '2010-06-01', '22:00:00', 'Handball', 'Sminker', 'International', 2, 3, 'Pinnaclesports', '" \r\n" \r\n" \r\n', '25:20', 'Won', '3'),
(3, 'Olimpija - Zlatorog', '1.9', '2010-05-12', '20:00:00', 'Basketball', 'Sminker', 'Belgium', 0, 7, 'Bet365', 'a)" \r\n', '20:20', 'Half Won', '3.15'),
(5, 'Barcelona - Real', '1.95', '2010-05-17', '20:00:00', 'Handball', 'Bostjan', 'France', 2, 4, '888sport', 'aaaaaaaaaaaaaaaaaaaaaaa \r\n', '22:26', 'Half Lost', '-2'),
(6, 'Slovakia - Estonia', '5.5', '2010-05-25', '20:00:00', 'Ice Hockey', 'Karas', 'International', 5.5, 3, 'BetCris', 'asasas" \r\n', '2:3', 'Won', '13.5'),
(7, 'Barcelona - Unicaja', 'under 148.5', '2010-06-01', '20:00:00', 'Basketball', 'Sminker', 'Spain', 1.98, 7, 'SportingBet', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" \r\n" \r\n', '96:84', 'Void', '0'),
(8, 'Roanne - Le Mans', '2', '2010-06-01', '20:30:00', 'Basketball', 'Karas', 'France', 2.2, 5, 'Bwin', 'a" \r\n" \r\n', '99:94', 'Lost', '-5');

Table structure for table `Users`
--

CREATE TABLE IF NOT EXISTS `Users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Username` varchar(50) NOT NULL,
`Password` varchar(40) NOT NULL,
`Email` varchar(30) NOT NULL,
`Country_name` varchar(50) NOT NULL,
`User_status_name` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `Username` (`Username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

--
-- Dumping data for table `Users`
--

INSERT INTO `Users` (`id`, `Username`, `Password`, `Email`, `Country_name`, `User_status_name`) VALUES
(1, 'Sminker', 'd5967071f308773d5fe599267b1c2a1f03e80169', '[e-mail]', 'Slovenia', 'Admin'),
(14, 'Bostjan', '9a900f538965a426994e1e90600920aff0b4e8d2', '[e-mail]', 'Slovenia', 'Tipster'),
(19, 'Karas', 'slos', '[e-mail]', 'Morocco', 'Tipster'),
(20, 'Probens', 'sss', 'sss', 'New Zealand', 'Tipster');

CREATE TABLE IF NOT EXISTS `User_status` (
`id_status` int(11) NOT NULL AUTO_INCREMENT,
`user_status` varchar(100) NOT NULL,
PRIMARY KEY (`id_status`),
UNIQUE KEY `user_status` (`user_status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `User_status`
--

INSERT INTO `User_status` (`id_status`, `user_status`) VALUES
(1, 'Public'),
(2, 'Paid'),
(3, 'Tipster'),
(4, 'Admin');

I hope that is it (I am still learning mysql :/)

[urtrivedi]

this is the final one.

select a.id, a.username, a.id_status,sum(a.won) won, sum(a.void) void, sum(a.lost) lost
from (
select Users.id, Users.Username, User_status.id_status,
case when Analysis.status_name='Won' then 1 else 0 end won,
case when Analysis.status_name='Void' then 1 else 0 end void,
case when Analysis.status_name='Lost' then 1 else 0 end lost,
@row := case when @prevuser=Users.Username then @row + 1 else 1 end as rownum,
@prevuser:=Users.Username FROM Users left outer join (SELECT @row := 0, @prevuser:=null ) r on @row=@row
INNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
order by Users.Username, Analysis.date
)  a where a.rownum <=20 GROUP BY a.id, a.Username, a.id_status;

[Boskor]

this is the final one.

select a.id, a.username, a.id_status,sum(a.won) won, sum(a.void) void, sum(a.lost) lost
from (
select Users.id, Users.Username, User_status.id_status,
case when Analysis.status_name='Won' then 1 else 0 end won,
case when Analysis.status_name='Void' then 1 else 0 end void,
case when Analysis.status_name='Lost' then 1 else 0 end lost,
@row := case when @prevuser=Users.Username then @row + 1 else 1 end as rownum,
@prevuser:=Users.Username FROM Users left outer join (SELECT @row := 0, @prevuser:=null ) r on @row=@row
INNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
order by Users.Username, Analysis.date
)  a where a.rownum <=20 GROUP BY a.id, a.Username, a.id_status;

Thank you very much.It is worknig now.