[SOLVED] help with this mysql

[corillo181]

i have this code

SELECT * FROM `clicks`
WHERE YEARWEEK( `date` , 1 ) = YEARWEEK( DATE_SUB( CURRENT_DATE, INTERVAL 1 WEEK ) , 1 )

what this code do, it selects anything that has been clicked last week.

what i want to also add to this query is to sum into one column whatever has been click more than once.

 

example.

if the query return something with a id of 10

i want it to add all the 10s

 

so 10 might be return 100 times because that is how many times it has been click.

so i want to bring those 100 times into 1. which will equal to id 10 been click 100 times.

 

simple? i hope so.

thanks for any help.

 

[kratsg]

Easily done, you're looking for the number of results:

 

<?php

$query = "SELECT * FROM `clicks`
WHERE YEARWEEK( `date` , 1 ) = YEARWEEK( DATE_SUB( CURRENT_DATE, INTERVAL 1 WEEK ) , 1 )";

$result = mysql_query($query) or die('Error: could not process query');

$count = mysql_num_rows($result) or die ('Error: could not count rows.');

?>

[corillo181]

no not at all, if i wanted the num rows, i wouldn't be posting here at all..

 

the query result might return..

 

20 clicks from id number 10.

which would look like.

id | date

10 | 08-29-08

10 | 08-29-08

10 | 08-28-08

10 | 08-29-08

10 | 08-27-08

ect.

 

30 clicks from id number 11.

11 | 08-26-08

11 | 08-29-08

ect.

 

so you see i want to add how many id 10 there is in the query and add it, and i want to know how many id 11 there is in the query and add them.

 

so it would be something like, if the id is more than once, add it to a column of total rows for that id.

 

so what im looking for is a total number of the same id return by the query.

 

no the total number of rows return by the query.

[kratsg]

You want to count all ids at the same time? Exactly what purpose will that serve for you?

 

Anyway, this will work:

 

<?php
// Make a MySQL Connection

$query = "SELECT id, COUNT(date) FROM your_table GROUP BY id"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(date)'] ." ". $row['id'] ."s";
echo "<br />";
}
?>

[corillo181]

so is not that man, is so hard to explain..

 

this is the query

SELECT * FROM `clicks`
WHERE YEARWEEK( `date` , 1 ) = YEARWEEK( DATE_SUB( CURRENT_DATE, INTERVAL 1 WEEK ) , 1 )

 

this is the result

id 	artist_id 	song_id 	counter 	date
24 	13 	       28 	        1	       2008-08-31
50 	24 	       57 	        1 	       2008-08-25
78 	18 	       73 	        1 	       2008-08-30
79  	18  	       73  	        1  	       2008-08-30

 

see where song id is 73 twice, instead of 73 twice i just want song_id 73 to be counter = 2

 

[corillo181]

oh no worries i got it

SELECT *,SUM(counter) `total` FROM `song_clicks`
WHERE YEARWEEK( `date` , 1 ) = YEARWEEK( DATE_SUB( CURRENT_DATE, INTERVAL 1 WEEK ) , 1 ) GROUP BY song_id

 

just had to add the SUM and the GROUP BY

yes!, thanks for the help guys.