I have an online calendar listing upcoming live music. I use a prepared statement to fetch the listings for the next eight days and display them in a table. What I need to do, before displaying the results, is count the number of unique dates ('Date') within the listings. For example, if only five days out of the next eight have events happening, I need to know that number is 5.
Using COUNT(DISTINCT) works for giving me that number, but then it only displays one row of results, so I need another solution
My code is this:
$mysqli = new mysqli("Login Stuff Here");
if ($mysqli->connect_errno){
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$start = strtotime('today midnight');
$stop = strtotime('+1 week');
$start = date('Y-m-d', $start);
$stop = date('Y-m-d', $stop);
$allDates = $mysqli->prepare("SELECT
ID,
Host,
Type,
Bands,
Date,
Time,
Price,
Note,
Zip,
URL
FROM things WHERE (Date >= ? AND Date <= ?) ORDER BY Date, Time, Host");
$allDates->bind_param("ss", $start, $stop);
$allDates->execute();
$allDates->bind_result($ID, $Host, $Type, $Bands, $Date, $Time, $Price, $Note, $Zip, $URL);
while($allDates->fetch()):
// ECHO ALL THE INFO IN A NICE TABLE
endwhile;
$allDates->close();
I need to count the unique values (and maybe even retrieve them) from the 'Date' column. Right now I have it working by doing a separate query, but I'm sure there's a better way.
EDIT: Ultimately, I wound up doing a separate query, which worked out well as I was able to use it for other things as well. I found that using GROUP BY always only returned just one result per date, so it didn't work for displaying the full listings. Maybe I was going at it wrong, but I wound up being good in another way. Thanks!
