[SOLVED] mysql date format

[frobak]

Hi

Im trying to format mysql dates to uk dd/mm/yyyy in a select statement and im struggling to get it to work.

 

Heres the select statement:

 

$query="SELECT * FROM events WHERE event_date >= CURdate() ORDER BY event_date ASC";

 

ive tried this and it didnt work?

 

$query="SELECT * FROM events

WHERE DATE_FORMAT(event_date,'%d/%m/%Y') >= CURdate()

ORDER BY event_date ASC";

 

any help would be appreciated

 

cheers

[Ken2k7]

What type of data is event_date stored as? TIMESTAMP?

[frobak]

no, just as date!

[Ken2k7]

SELECT * FROM events WHERE event_date >= NOW() ORDER BY event_date

Try that.

[Mchl]

SELECT DATE_FORMAT(event_date,'%d/%m/%Y')  FROM events WHERE event_date >= CURDATE() ORDER BY event_date

 

 

[frobak]

thanks but i get the following errors after changing as above:

 

Warning: mysql_result() [function.mysql-result]: DATE_FORMAT not found in MySQL result index 4 in /home/frobakco/public_html/rockinghorsenights.com/eventpage.php on line 47

 

Warning: mysql_result() [function.mysql-result]: band_name not found in MySQL result index 4 in /home/frobakco/public_html/rockinghorsenights.com/eventpage.php on line 48

 

Warning: mysql_result() [function.mysql-result]: narrative not found in MySQL result index 4 in /home/frobakco/public_html/rockinghorsenights.com/eventpage.php on line 49

 

heres the full php code:

 

				<?php
				include("includes/db_connect.inc.php");

				$query="SELECT DATE_FORMAT(event_date,'%d/%m/%Y')  FROM events WHERE event_date >= CURDATE() ORDER BY event_date";
				$result=mysql_query($query);

				$num=mysql_numrows($result);

				mysql_close();

				$i=0;
				while ($i < $num) {

				$event_date = mysql_result($result,$i,'event_date');
				$band_name = mysql_result($result,$i,'band_name');
				$narrative = mysql_result($result,$i,'narrative');

				echo "<div id='eventbox'>
						<table id='events'>
							<th colspan='2'>$event_date
							</th>
							<tr>
								<th>$band_name
								</th>
							</tr>
							<tr>
								<td id='events'>$narrative
								</td>
							</tr>
						</table>
					</div>";
				$i++;
				}
			?>

[Mchl]

Use column alias and add other columns.

SELECT DATE_FORMAT(event_date,'%d/%m/%Y') AS event_date_formatted, band_name, narrative  FROM events WHERE event_date >= CURDATE() ORDER BY event_date

[frobak]

that worked, many thanks

 

how do i set the post as solved?

[Ken2k7]

Click the Topic Solved link at the bottom.