Date If statement help

[richard_PHP]

Hello all,

 

I'm trying to make an events list where the date and location (info entered in a database) are shown, if the event has been and gone then the page display it in red itaclics.

 

So far I have conjured up a basic bit of code but I'm having troubles gettin it to work! Help would be welcomed.

 

Code:

 

<?php
			// Start the connection to the database
				$conn = mysql_connect("d***", "r****s", "******");
			// Select the database to use
				mysql_select_db("******", $conn);
			// Create and run the MySQL command to retrieve the records
				$sql = "SELECT * FROM *****h ORDER BY date ASCEN";
			// Run the MySQL statement. Assign the results of the query to an array. Run a loop to repeat for each record in the table
					$now = time();
					$result = mysql_query($sql, $conn);
				while ($array = mysql_fetch_array($result)) {
					$id = $array[id];
					$date = $array[date];
					$location = $array[location];

					if ($date < $now)
						echo "<p class='past'>$date - $location</p>";
					else
						echo "<p>$date - $location</p>";
				}
		?> 

[Maq]

In the future, please use

tags around your code.

[dcro2]

Try this:

<?php
			// Start the connection to the database
				$conn = mysql_connect("d***", "r****s", "******");
			// Select the database to use
				mysql_select_db("******", $conn);
			// Create and run the MySQL command to retrieve the records
				$sql = "SELECT * FROM *****h ORDER BY date ASC"; //I don't think ASCEN works...
			// Run the MySQL statement. Assign the results of the query to an array. Run a loop to repeat for each record in the table
					$now = time();
					$result = mysql_query($sql, $conn);
				while ($array = mysql_fetch_array($result)) {
					$id = $array[id];
					$date = $array[date];
					$timestamp = strtotime($date); //convert to timestamp like what time() returns
					$location = $array[location];

					if ($timestamp < $now)
						echo "<p class='past'>$date - $location</p>";
					else
						echo "<p>$date - $location</p>";
				}
		?> 

[richard_PHP]

Thanks. That's worked now.

 

Is there a way for me to change the way the date looks? At the moment it is in the database (as a date attirbute) at YYYY-MM-DD, I would like it be either DD-MM-YYYY or even as long handed text (1st January 2011 etc)

[dcro2]

Just feed the date function your new $timestamp. There's lots of ways to format with date().

 

$date = date('d-m-Y', $timestamp);

[Pikachu2000]

If you're pulling it from a MySQL DB, use MySQL's DATE_FORMAT() function.

[richard_PHP]

Thanks for that link. I'm finding it hard to see where in my code I can implement it (bit of a novice). I'll want to use the specifiers: %D %M %Y

[Pikachu2000]

It would go in your query string.

 

SELECT `a_field`, `another_field`, DATE_FORMAT(`date_field`, '%D %M %Y') AS `formatted_date` FROM `table`

 

Then the formatted date would be available just as any other value in the array you fetch from the result. In this case since the field is aliased AS `formatted_date`, that would be the array index you'd use.

[richard_PHP]

I've tried inserting the example above (probably novice-like):

 

<?php
			  //Start the connection to the database
				 $conn = mysql_connect("db4free.net", "rickscapepics", "nyjets16");
			  //Select the database to use
			 	mysql_select_db("scapepictures", $conn);
			 // Create and run the MySQL command to retrieve the records
			 	$sql = "SELECT * DATE_FORMAT('date', '%D %M %Y') AS 'formatted_date' FROM blackwatch ORDER BY date ASC";
			//  Run the MySQL statement. Assign the results of the query to an array. Run a loop to repeat for each record in the table
			 	$now = time();
			 		$result = mysql_query($sql, $conn);
			 	while ($array = mysql_fetch_array($result)) {
			 		$id = $array[id];
			 		$date = $array[date];
			 		$timestamp = strtotime($date); // convert to timestamp like what time() returns
			 		$location = $array[location];

			 		if ($timestamp < $now)
			 			echo "<p class='past'>$date - $location</p>";
			 		else
			 			echo "<p>$date - $location</p>";
			 	}
		?>

 

And the website is saying 'Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in *****/events.php on line 30'

[Pikachu2000]

You've used single quotes around the AS 'formatted_date' alias. They should be changed to `backticks`, or they could just be omitted altogether. You also need a comma between the wildcard * and DATE_FORMAT. I can't help but notice however, that you're using a unix timestamp after the query, which leaves me a bit confused . . .

 

SELECT *, DATE_FORMAT( . . . etc. 

[richard_PHP]

Got it all working now.

 

Thanks for all the help.