Count id once - no repeat

[harrence]

Hi all,

I'm currently working on having 'artist_id' counted once only
between two dates although they may have visited several
times over that period. The following code works very well
for counting the total number of visits. Any suggestions out there?

Regards harrence

$sql = "SELECT * FROM artists_details LEFT JOIN attendance ON attendance.record_id AND attendance.visit_date >= '".$from_date."' AND attendance.visit_date <= '".$to_date."' WHERE attendance.artist_id=artists_details.artist_id";
echo $sql;
$result6=mysql_query($sql);
$num_rows_ind = mysql_num_rows($result6);

[R0bb0b]

I would say you probably want to do something like this:

select min(visit_date), recordid, fname, lname from artist_details inner join attendance using(recordid) where visit_date < '2008-06-25' and visit_date > '2008-06-23' group by recordid,  fname, lname

[lionaires]

nice codes ha....
pls help me......
i am working with a web based voting system can you help me with it?

[R0bb0b]

If you post a new thread, I will certainly look at it.

[harrence]

Thanks Rob

found two solutions one for one field, and one for another.
Thanks for the DISTINCT tip in the previous thread it didn't work then
but it works in the following php where all I need is artist_id from attendance
where there is no join required,
and in the next solution. (* and DISTINCT I've decided, are not friends).

$sql = "SELECT DISTINCT artist_id FROM attendance WHERE visit_date >='".$from_date."' AND visit_date <='".$to_date."'";
$result6=mysql_query($sql);
$num_rows_ind = mysql_num_rows($result6);

The next query was a bit more difficult and taught me something very important,
thanks to Naveen as well for input regarding matching fields. Amazing the way mysql
and php scan both ways!

$sql = "SELECT DISTINCT attendance.artist_id FROM artists_details LEFT JOIN attendance ON attendance.visit_date >='".$from_date."' AND attendance.visit_date <='".$to_date."' WHERE artists_details.outreach='outreach' AND attendance.artist_id=artists_details.artist_id";
//echo $sql;
$result6=mysql_query($sql);
if ($result6) {
$num_rows_ind = mysql_num_rows($result6);}

RESULT!
Happy day harrence

[harrence]

Still having a look at your way - interesting.

harrence