Member Avatar for shamsidah 
<?php
	include 'connection.php';
	$data = mysql_query( "SELECT Date,color_code, COUNT(color_code) FROM paintshop WHERE Date BETWEEN '$startDate' AND '$endDate'") or die('error');
	while($rw = mysql_fetch_array($data)){ 
	?>
    <td width="59" height="25" class="style5Copy"><?php echo $rw['Date']; }?></td>

i want display the dates between i post from the form..
eg: start date = 1-12-2011 until end date 7-12-2011
(meaning 1-12-2011,2-12-2011,3-12-2011,4-12-2011,5-12-2011,6-12-2011,7-12-2011):-/

  • 4 Contributors
  • 3 Replies
  • 104 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by HITMANOF44th

Dani AI

Generated

The goal here is usually to list every calendar day between the two form dates and show the number of paintshop entries for each date (including zeros). The original code snippets on this thread use the old mysql_* functions and a raw aggregate without grouping, and one reply incorrectly calls the query resource a second time. Those issues cause wrong results or errors and are insecure.

As asked for a date sequence, note two reliable approaches: (1) run one grouped query to get counts per date, then iterate a PHP date range and fill in missing dates with zero; or (2) generate the date range in PHP and query per-day counts (simpler but slower for long ranges). was right that correct inputs matter; ’s example incorrectly passes a query resource back to mysqlquery, which will fail. Also avoid deprecated mysql* functions — use PDO or mysqli and prepared statements.

A concise, safe pattern (preferred for performance) is: parse and normalize the posted dates with DateTime, fetch grouped counts with a prepared statement, then loop a DatePeriod and print each date using the results array. Example:

<?php
// parse inputs (e.g. '01-12-2011'), normalize to Y-m-d
$start = DateTime::createFromFormat('d-m-Y', $startInput);
$end   = DateTime::createFromFormat('d-m-Y', $endInput);
$end->modify('+1 day'); // make end inclusive

$pdo = new PDO('mysql:host=...;dbname=...;charset=utf8', 'user','pass',[PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);

$stmt = $pdo->prepare('SELECT `Date` AS d, COUNT(*) AS cnt FROM paintshop WHERE `Date` BETWEEN :s AND :e GROUP BY `Date`');
$stmt->execute([':s'=>$start->format('Y-m-d'), ':e'=>$end->format('Y-m-d')]);
$counts = [];
foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) $counts[$r['d']] = (int)$r['cnt'];

$period = new DatePeriod($start, new DateInterval('P1D'), $end);
foreach($period as $day) {
  $d = $day->format('Y-m-d');
  echo "<tr><td>{$d}</td><td>".(isset($counts[$d]) ? $counts[$d] : 0)."</td></tr>";
}
?>

If your table stores DATETIME, use DATE(Date) in the SELECT and GROUP BY. Validate inputs, handle parse errors, and limit long ranges to avoid heavy queries. See PHP docs for parsing and ranges: DateTime::createFromFormat, DatePeriod, and PDO prepared statements.

Recommended Answers

All 3 Replies

Member Avatar for pritaeas

So what's the problem, what do you need help with ?

Member Avatar for rotten69

Your query looks fine and should display dates (if they're selected as inputs in your form). Unless, you have fixed dates. For example, startDate begins from 1-12-2011 and endDate is only specified by your clients.

So What's up with your piece of code? Is it doing what it is expected to do or you have problems with it?

Member Avatar for HITMANOF44th 
<?php

    include 'connection.php';
    $data = mysql_query( "SELECT Date,color_code, COUNT(color_code) FROM paintshop WHERE Date BETWEEN '$startDate' AND '$endDate'") or die('error');
    $result = mysql_query($data) or die(mysql_error());
 
echo "<table>
<tr>
	<td>row1</td>
	<td>row2</td>
	<td>row3</td>
	<td>row4</td>
</tr>"; 
    
    while($row = mysql_fetch_array($result, MYSQL_BOTH)){
 echo "<tr>
	<td>".$row['somerow']."</td>
	<td>".$row['anotherrow']."</td>
	<td>".$row['otherrow']."</td>
	<td>".$row['month']."</td>
</tr>";
}
echo "</table>"
?>
Edited by HITMANOF44th because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.