asked on

How do I return a count of records in a table by date and then span a table row with the count?

I need to count the number of records that match a field called ActivityDate from a MySQL table using PHP and then return a table column that spans the row numbers in the count result.

$ActivityDate = $row_rsAccountingInfo['ActivityDate'];
$DateRows=date("Y-m-d", strtotime($ActivityDate));											
$sql = sprintf("SELECT COUNT(*) as count, 'ActivityDate' FROM Accounts WHERE Accounts.CustomerID='$CustomerID' AND ActivityDate='$DateRows' GROUP by 'ActivityDate'");
       			$result = mysql_query($sql);
        		$row = mysql_fetch_array($result);
				$count = $row['count'];
				echo "<td rowspan=\"" . $count . "\">"; ?>

Open in new window

hernst42

That should do it, maybe you need to explain al little bit more, I'm not getting you problem. Doesn't the code work you posted?

microlink24

ASKER

It counts the records but I am getting this as a result....

https://www.majets.com/testChris.php

I need it to combine the count by date to only show one "View" button per each date. So if I have 4 July 8, 2008 records I would only get one "View" button.

hernst42

ok, seems to need a login, but You sql might be rong. Check is the codesnippet is more what you need. No ' around the ActivityDate, which would be otherwise a string.


$sql = sprintf("SELECT COUNT(*) as count, ActivityDate FROM Accounts WHERE Accounts.CustomerID='$CustomerID' AND ActivityDate='$DateRows' GROUP by ActivityDate");
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$count = $row['count'];
$date = $row['ActicityDate'];
echo "<td rowspan=\"" . $count . "\">$date";

Open in new window

microlink24

ASKER

Please have a look here to see the issue. Thanks!

https://www.majets.com/testChris.php
User: dbh67@hotmail.com
Pass: 12345

ASKER CERTIFIED SOLUTION

hernst42

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

microlink24

ASKER

Thank you!