Display Db Records Of The Current Month Only How?

[lovephp]

friends how do i display the results of the current month only and rest remain on db but on page just the current month posts?

 

here is my current code to display records

$result = mysql_query("SELECT id, aname, cname, hnumber, address, lamount, status, time FROM db_paydayloans ORDER BY id DESC");
           $rowNo = 1; //Increment Row Number
           while($res = mysql_fetch_array($result))
           {
						    $id = $res['id'];
               $date = $res['time'];
  						 $newdate = date("D, j M, Y", $date);    
						    if($res['status'] == "Approved"){
               echo "<tr align='center' bgcolor='#CDE861'>";
						    }else{
						    echo "<tr align='center' bgcolor='#EDA553'>";
						    }
						    echo"<td><font color='black'>" .$rowNo++."</font></td>";
               echo"<td><font color='black'>" .$res['aname']."</font></td>";
						    echo"<td><font color='black'>" .strtoupper($res['cname'])."</font></td>";
               echo"<td><font color='black'>" .$res['hnumber']."</font></td>";
               echo"<td><font color='black'>". $res['address']. "</font></td>";
               echo"<td><font color='black'>". $res['lamount']. "</font></td>";
               echo"<td><font color='black'>". $newdate. "</font></td>";
						    if($res['status'] == "Approved"){
						    echo"<td>Approved</td>";
						    }else{
						    echo"<td>Pending</td>";
						    }
echo"<td> <a href ='view.php?id=$id'><center>Click to View </center></a></td>";
if($res['status'] !== "Approved"){
               echo"<td><a href ='accepted.php?id=$id'>Approve</a>";
}else{
               echo"<td><a href ='cancel.php?id=$id'>Cancel</a>";
}
               echo"<td> <a href ='del.php?id=$id'><center>Delete</center></a>";            
               echo "</tr>";
           }

 

and this is how i insert data

 

///////////////////////////////////Insert data        
   function insertP($postData) {        

				    $sql = " INSERT INTO db_paydayloans SET
				    aname = '".$_SESSION['aname']."',
           cname = '".$postData['cname']."',
           address    = '".$postData['address']."',
           hnumber = '".$postData['hnumber']."',
           altnumber = '".$postData['altnumber']."',
           lamount = '".$postData['lamount']."',
           mrepayments = '".$postData['mrepayments']."',
           ssn = '".$postData['ssn']."',
           dln = '".$postData['dln']."',
				    mincome    = '".$postData['mincome']."',
				    lpayday    = '".$postData['lpayday']."',
				    npayday    = '".$postData['npayday']."',
				    abalance = '".$postData['abalance']."',
				    msaving    = '".$postData['msaving']."',
				    dob    = '".$postData['dob']."',
				    apnumber = '".$postData['apnumber']."',
				    comments = '".mysql_real_escape_string($postData['comments'])."',
				    status    = '".$postData['status']."',
    				 time    = '".time()."'
           ";
           executeSql($sql);
           }

[lovephp]

will this be right?

 

$date=date("Y-m-d H:i:s");

$result = mysql_query("SELECT id, aname, cname, hnumber, address, lamount, status, DATE_FORMAT(time,'%M %Y') FROM db_paydayloans ORDER BY id DESC");

 

my time stores like 12047364546 in timestamp

Edited December 6, 2012 by lovephp

[jazzman1]

What is wrong with a time field of this query?

Does time have a type of datetime or something else in your DB?

"SELECT id, aname, cname, hnumber, address, lamount, status, time FROM db_paydayloans ORDER BY id DESC"

[lovephp]

no it do not have any datetime its stores time like 1234375645373 thats how it shows in db

[Barand]

WHERE FROM_UNIXTIME(time, '%Y-%m-%d') = CURDATE()

 

If you use DATE or DATETIME types in the first place it saves the overhead of having to convert every time you want to use a datetime function

Edited December 6, 2012 by Barand

[lovephp]

but its already done if i make changes now it will mess up my 5000+ records right?

[Barand]

Something to bear in mind next time you have to create a table.

[lovephp]

thanks will keep this in mind

[PFMaBiSmAd]

Unless you are doing something simple like finding dates great-than/less-than or between a range, storing data as a Unix Timestamp doesn't directly work for any Human related processing by year, month,week number, day, day of year, full date, or time of day periods, because you must first convert it to a human readable format. It's best to store it in a human readable format in the first place.

 

You can always add a new DATE or DATETIME column to your table and populate it from your existing data using the FROM_UNIXTIME() statement that Barand showed. Then modify/test your scripts to use that new column. Finally drop your existing unix timestamp column.

 

Edit: plus, once your data is stored as a DATE or DATETIME value, you can directly use the few dozen mysql data/time functions on it to do neat things in your query.

Edited December 6, 2012 by PFMaBiSmAd

[lovephp]

totally agreed i messed it up in the first place next time onwards ill make sure