Display No. of Records per day With Code

[imrankhan]

Hi,

I use a MySQL db where one of the fields in a table is 'advertise_date'  is date time and other is "tender_title" it is Varchar becasue i have to enter their names but for generating report How can I count and display the records of the same date in php while if u have any confusion ask me

 

the output for the last 30 days will be like:

 

Date                      Tenders

2009-03-08            35

2009-03-09              0

2009-03-10            14

2009-03-11            27

 

Thanks in advance.

Regards, imran

 

Here is the Code

 

<?

$username="*****";

$password="******";

$database="*****";

 

mysql_connect(localhost,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM ppra_tenders";

 

 

$result=mysql_query($query);

 

$num=mysql_numrows($result);

 

mysql_close();

 

echo "<b><center>Database Output</center></b><br><br>";

 

$i=0;

while ($i < $num) {

 

$id=mysql_result($result,$i,"id");

$title=mysql_result($result,$i,"tender_title");

$adate=mysql_result($result,$i,"advertise_date");

$cdate=mysql_result($result,$i,"close_date");

 

 

echo "<b>$id $title</b> Phone: $adate Mobile: $cdate<br><hr><br>";

 

$i++;

}

 

 

?>

 

total Tenders <?php echo "$i" ?>

[Darghon]

I'm not sure how you want to show your data, but you can "GROUP BY" your data to get a tenders count by date

 

Example SQl would be:

select distinct advertise_date, count(tender_title) as tenders from ppra_tenders group by advertise_date order by advertise_date asc

 

hope this helps

 

in addition:

if you want to dump the list you gave as an example =>

<table>
<tr><th>Date</th><th>Tenders</th></tr>
<?php  
//make sure you connect to database first
$query="select distinct advertise_date, count(tender_title) as tenders from ppra_tenders group by advertise_date order by advertise_date asc";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0){
  while($row = mysql_fetch_array($result)){
  ?>
    <tr><td><?php echo $row["advertise_date"]; ?></td><td><?php echo $row["tenders"]; ?></td></tr>
  <?php
  }
}
mysql_free_result($result);
?>
</table>

[imrankhan]

thnx Darghon

 

i want to know where the $row["tenders"] comes from in table? and  1 thing more ifu dont mind if  i want a listbox in this page  in which all month and year names comes hows it possible ? i know how to put list box but dont know how it works from which we select month and year it gave result in same form of table like if we select month January and year 2010 it gave January result

 

 

Date                      Tender

01-01-2010              32

02-01-2010              5

 

which month we select it gave that result ???????????????

 

 

[imrankhan]

its 100% working but the problem is its show time too like

 

Date                                        Tenders

2008-01-14 00:00:00                    4

2008-01-15 00:00:00                    0

2008-01-16 00:00:00                    1

2008-01-17 00:00:00                    0

2008-01-18 00:00:00                    1

 

i want to use date function to show like that

 

Date                          Tenders

2008-01-14                    4

2008-01-15                    0

2008-01-16                    1

2008-01-17                    0

2008-01-18                    1