• PHP Help PHP Coding

  • [RESOLVED] getting php to determine whether it's before or after mysql datetime?

Hi there everyone,

I've got a sql query that is pulling a datetime as

DATE_format(end_date, '%b %e, %Y') as end_date

I need a way of determining if the current datetime is before or after the one being pulled from the database. Since the format is being created by the query, I'm not sure how to get php to make this determination:

if($end_date<<NOW()){

}

failed miserably, as I found out that NOW is a mysql function 😃

Any help would be greatly appreciate!
json

    depending on your whole query you could check in the where clause only returning values that are before\after NOW()

    WHERE date>NOW()

    or something like this

    SELECT *,if( date > NOW( ) , 'yes', 'no' ) AS date_check
    FROM table

    then you have yes or no to work with, in your php.

      Thanks for your reply dagon.

      I need both the active and inactive records from the mysql query. Is there any way to handle the check with php or do I have to do two queries to get the records I need?

      thanks,
      json

        can do it with php, but as your accessing mysql, i would use it, your not provideing enough info to be able to see the issue.

          Here's the complete deal:

          $query1="SELECT id,cat_id,active,title,primary_image,image_1,image_1_thumb,image_2,image_2_thumb,image_3,image_3_thumb,image_4,image_4_thumb,image_5,image_5_thumb,image_6,image_6_thumb,DATE_format(create_date, '%b %e, %Y') as create_date,DATE_format(end_date, '%b %e, %Y') as end_date FROM classifieds_ads WHERE user_id=$user_id ORDER BY end_date DESC";
					$result1=mysql_query($query1) or die (mysql_error());
					$numr=mysql_num_rows($result1);
					if($numr==0){
						echo("<p>You currently have no advertisements.  If you would like to create an advertisement, simply
						click the \"create an ad\" in the quick link section at the bottom of this page.</p>");
					}else{
						echo("
						<hr width='100%'>
						");
						/*Show the user's ads*/
						while($row=mysql_fetch_array($result1)){
							$id=$row['id'];
							$cat_id=$row['cat_id'];
							$active=$row['active'];
							$full_title=$row['title'];
							$title=strtruncate($full_title, 20, " ");
							$primary_image=$row['primary_image'];
							$image_1=$row['image_1'];
							$image_1_thumb=$row['image_1_thumb'];
							$image_2=$row['image_2'];
							$image_2_thumb=$row['image_2_thumb'];
							$image_3=$row['image_3'];
							$image_3_thumb=$row['image_3_thumb'];
							$image_4=$row['image_4'];
							$image_4_thumb=$row['image_4_thumb'];
							$image_5=$row['image_5'];
							$image_5_thumb=$row['image_5_thumb'];
							$image_6=$row['image_6'];
							$image_6_thumb=$row['image_6_thumb'];
							$create_date=$row['create_date'];
							$end_date=$row['end_date'];

						$query2="SELECT title,parent FROM classifieds_categories WHERE id=$cat_id";
						$result2=mysql_query($query2) or die (mysql_error());
						while($row=mysql_fetch_array($result2)){
							$cat_title=$row['title'];
							$parent=$row['parent'];

							$query3="SELECT title FROM classifieds_categories WHERE id=$parent";
							$result3=mysql_query($query3) or die (mysql_error());
							while($row=mysql_fetch_array($result3)){
								$parent_title=$row['title'];
							}
						}

						echo("
						<table width='100%'>
							<tr>
								<td width='100'>
								<img src='/uploaded_images/".$thumbnail."' alt='thumbnail'>
								</td>
								<td width='10'>
								</td>
								<td valign='top'><b>Title: <a href='classifieds.php?action=view_ad&ad_id=".$id."' alt='".$full_title."' title='".$full_title."'>".$full_title."</a><br>
								");
								if($active=='1'){
									echo("<b><font color='green'>ACTIVE</font></b>");
										if($end_date<<NOW()){
										 echo(" <b><font color='red'>BUT EXPIRED</font></b>");
										}
								}else{
									echo("<b><font color='red'>INACTIVE</font></b>");
										if($end_date<<NOW()){
										 echo(" <b><font color='red'>AND EXPIRED</font></b>");
										}
								}
								echo("
								Found In:	".$parent_title."~>".$cat_title."<br>
								Ends On: ".$end_date."</b><br />
								<b>| <a href='' alt='Renew this ad'>Renew</a> -|-  <a href='' alt='Edit this ad'>Edit</a> -|- <a href='' alt='Deactivate this ad'>Deactivate</a> -|- <a href='' alt='Delete this ad'>Delete</a> |</b>
								</td>
							</tr>
						</table>
						<br />
						<hr width='100%'>
						");

					}
					echo("

					");				
				}
			}
		}

          If you look at the "BUT EXPIRED" and "AND EXPIRED" portion of the code, you'll see where I'm stuck.

          thanks,
          json

            I tried modifying the sql statement to add your suggestion but got an error for it.

            $query1="SELECT id,cat_id,active,title,primary_image,image_1,image_1_thumb,image_2,image_2_thumb,image_3,image_3_thumb,image_4,image_4_thumb,image_5,image_5_thumb,image_6,image_6_thumb,DATE_format(create_date, '&#37;b %e, %Y') as create_date,DATE_format(end_date, '%b %e, %Y'), as end_date, if( end_date < NOW( ) , 'yes', 'no' ) AS date_check FROM classifieds_ads WHERE user_id=$user_id ORDER BY end_date DESC";

              theres an extra comma in the query, otherwise it looks like it should do what you need.

                Your absolutely right. Removing the offending comma solved my issues and your solution works fantastically. Thanks so much for your help!

                  Write a Reply...