count the result

[anujgarg]

I have a table:

 

vote_id article_id user_id vote sink vote_date vote_time

2 1 177 0 1 0000-00-00 00:00:00 1225452550

11 2 66 1 0 0000-00-00 00:00:00 1226485071

26 1 66 1 0 0000-00-00 00:00:00 1230025189

32 6 66 1 0 2008-12-31 10:05:45 1230717945

33 5 66 1 0 2008-12-31 10:10:40 1230718240

34 4 66 1 0 2009-01-05 09:33:53 1231148033

35 6 178 1 0 2009-01-08 09:40:30 1231407630

36 5 178 1 0 2009-01-08 12:30:45 1231417845

 

 

Now I want to extract total no. of votes (whose value is 1) and sinks (whose value is 1) given for article_id = 5 from a single query.

 

I ran following query for this:

 

SELECT count(vote)

FROM biz_community_article_vote

WHERE article_id = '1' and vote = '1'

LIMIT 0 , 30

 

But in this case, I have to run two separate queries (for extracting no. of vote and sink), how can I do this by running a single query.

 

TIA

[chronister]

I believe that you would need to run 2 queries to do it.

 

I mean, you can certainly run a query like this....

SELECT count(vote) as vote, count(sink) as sink
FROM biz_community_article_vote
WHERE article_id = '1' and vote = '1'
LIMIT 0 , 30

 

but I am not sure that it would yield the results you want. It will count the fields that match both pieces of the where clause.

 

Try the example I gave and see if it gives you what your looking for.

 

Nate

[anujgarg]

It didn't give me the desired result, Nate. It means, I would have to run two separate queries only for this....

[redarrow]

That interesting can you do multiple counts in mysql...

 

thort you couldnt..

 

 

 

 

[redarrow]

does this give you correct data.

 

<?php
$sql="SELECT * form biz_community_article_vote
WHERE article_id = '1' and vote = '1'
LIMIT 0 , 30";

$sql_res=mysql_query($sql)or die(mysql_error());

while($res=mysql_fetch_assoc($sql_res)){

echo " ".count($res['sink'])." <br> ".count($res['vote']."";

}
?>

[chronister]

@redarrow

 

I was not sure that you could do multiple count queries either, so I pulled up phpmyadmin on my local db server and tried it... it worked, but did not give the proper results as it is returning the same number of results for both items.

 

It looks like your suggestion would echo the same number of times as there are results... but I think your code could be modified a little to get the desired results...

 

<?php
$sql="SELECT * form biz_community_article_vote
WHERE article_id = '1' and vote = '1'
LIMIT 0 , 30";

$sql_res=mysql_query($sql)or die(mysql_error());
$x = 0;
while($res=mysql_fetch_assoc($sql_res)){

$vote[$x] = $res['vote'];
$sink[$x] = $res['sink'];
$x++;
}
echo 'vote: '. count($vote) .' sink:'. count($sink);
?>

 

Run the query, then loop over the results placing them in 2 arrays. I don't think the $x var is necessary, but it is there just in case, then after the looping is done, echo the count of each array.

 

@anujgarg

Not sure if this gets the desired result, but you can try.

 

When I think of it, the count of each will be the same as the query is returning X number of rows, so that means that the number of items in the array will be the same as X.

 

Try and see if this gets what your looking for, but I think you may need to do this in 2 queries to get the proper results. I think if you do it with 1 query, then your going to always end up with the same number of items for each column and I don't think that is what your after.

 

Nate

 

[redarrow]

 

i think sink needs it own databse field to get the desired effect he wants.

 

How many sink and votes are there then,

 

didn't no you needed to use a array for what, Only guessing there many slink and votes for all diffrent users then

 

this means he got multiple sink and votes in the database.

 

if there was one sink and vote in the database then my code should work i hope.

 

ps thanks for the database tips was interesting theo.

 

he also got the query set to 0,30 so that makes a difference i guess .

 

if there more then 30 rows wont make a proper reading anyway.

[redarrow]

 

i think sink needs it own database field to get the desired effect he wants.

 

How many sink and votes are there then,

 

didn't no you needed to use a array for what, Only guessing there many slink and votes for all diffrent users then

 

this means he got multiple sink and votes in the database.

 

if there was one sink and vote in the database then my code should work i hope.

 

ps thanks for the database tips was interesting theo.

 

he also got the query set to 0,30 so that makes a difference i guess .

 

if there more then 30 rows wont make a proper reading anyway.

 

[redarrow]

are we sure it not sum he needs becouse he set the sink to a 0 and the vote to a 1

 

very odd.

[redarrow]

he got me lol (notice the id set to 1 aswell

 

now it should read correctly  sink is 1 and vote is 1

<?php
$sql="SELECT * form biz_community_article_vote
WHERE vote = '1' and sink='1' ";

$sql_res=mysql_query($sql)or die(mysql_error());

while($res=mysql_fetch_assoc($sql_res)){

echo " ".count($res['sink'])." <br> ".count($res['vote']."";

}
?>

 

1 way

$sql="SELECT * form biz_community_article_vote
WHERE vote = '1' and sink='1' ";

 

2 way

$sql="SELECT * form biz_community_article_vote
WHERE vote = '0' and sink='1' ";

 

3 way

$sql="SELECT * form biz_community_article_vote
WHERE vote = '1' and sink='0' ";

 

4 way

$sql="SELECT * form biz_community_article_vote
WHERE vote = '0' and sink='0' ";

[redarrow]

he not updating the votes he inserting everything the database do sent make seance.

[redarrow]

show me he inserting not updating

 

35    6    178    1    0    2009-01-08 09:40:30    1231407630

 

and this

 

  32    6    66    1    0    2008-12-31 10:05:45    1230717945

 

he trying to add up the sum off the votes via the users id and and article id

he got it all wrong lol.

 

 

[anujgarg]

Finally, what did you conclude, redarrow??

[redarrow]

<?php
SELECT sum(vote)
FROM biz_community_article_vote
WHERE article_id = '5' 
LIMIT 0 , 30
?>

result is 2

 

i think you was trying to add the votes up.

 

am i wright.

[anujgarg]

No redarrow, I was not trying to add the votes. Finally, I have run two separate queries for vote and sink....