[SOLVED] Having problems with MySQL and PHP displaying the date

[influenceuk]

Hi,

i am having issues with displaying the date correctly, can someone check out my code and advise where i am going wrong please?

 

Basically the script works fine and will display the following...

 

Title      Studio    Release

A Item  A Studio  2007-06-12

 

I am after getting the date to format like this - 12 June 2007.

 

I have tried putting in the Date Format bit but it wont work or i get errors Please help :?:

 

<?php

include 'connect.php';

if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}

$max_results = 15;

$from = (($page * $max_results) - $max_results); 

$sql = mysql_query("SELECT * FROM table2 WHERE `release` > CURRENT_DATE ORDER BY 'release' LIMIT $from, $max_results");
$bgcolor = "#E0E0E0";
    echo("<table>");
    echo"<tr class=titlebg><td><b>Title</b><td><b>Studio</b><td><b>Release Date</b></tr>";
    while($row = mysql_fetch_array($sql)){
        if ($bgcolor == "#E0E0E0"){
            $bgcolor = "#FFFFFF";
        }else{
            $bgcolor = "#E0E0E0";
        }

    echo("<tr bgcolor=".$bgcolor."><td width=350 nowrap>");
    echo($row["title"]);
    echo("</td><td width=175 nowrap>");
    echo($row["studio"]);
    echo("</td><td align=\"center\">");
    echo($row["release"]);
    echo("</td></tr>");
    }

    echo("</table>");


$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM table2 WHERE `release` > CURRENT_DATE ORDER BY 'release'"),0);

$total_pages = ceil($total_results / $max_results);

echo "<center>Select a Page<br />";

if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\">Prev</a> -";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
        } else {
            echo " <a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
    }
}

if($page < $total_pages){
    $next = ($page + 1);
    echo "- <a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next</a>";
}
echo "</center>";
?>        

[Wildbug]

DATE_FORMAT(release,'%e %M %Y')

 

Take the single quotes off of 'release' in your query.  If you're going to quote column names, use backticks, not single quotes.

[influenceuk]

sorry can you give me an example of where to enter this as i have tried it and it did not work?

[Wildbug]

<?php

$sql = mysql_query("SELECT *,DATE_FORMAT(release,'%e %M %Y') AS release_formatted FROM table2 WHERE release > CURRENT_DATE ORDER BY release LIMIT $from, $max_results");

?>

[influenceuk]

Cheers i have done this, but when i do it i get the error...

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in .... line 44

 

Line 44 is

 

while($row = mysql_fetch_array($sql)){

 

i cant work out what to do  ???

[Wildbug]

(I must have typed this part a dozen times in the past few days.  Nobody's checking for MySQL error codes these days.)

 

 

<?php

$query = "SELECT *,DATE_FORMAT(release,'%e %M %Y') AS release_formatted FROM table2 WHERE release > CURRENT_DATE ORDER BY release LIMIT $from, $max_results");
$sql = mysql_query($query);
if (mysql_errno()) die(sprintf('Error: %s<br/>Query: %s<br/>',mysql_error(),$query));

?>

 

Try that and see what error is returned.  When developing, you should always check for errors when querying the database.  Additionally, you should also only try to perform any action on the result when a valid result with rows > 0 is returned so that no errors are shown to the end user and no empty tables are produced.  But just do the above for now.

 

if ($sql && mysql_num_rows($sql)) {
while ($row = mysql_fetch_row($sql)) { /* ... etc ... */ }
} else { echo "No results." }

[influenceuk]

i am getting this as the error...

 

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'release,'%e %M %Y') AS release_formatted FROM table2 WHERE releas

Query: SELECT *,DATE_FORMAT(release,'%e %M %Y') AS release_formatted FROM table2 WHERE release > CURRENT_DATE ORDER BY release LIMIT 0, 15

[AndyB]

release is a MySQL reserved word - http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

 

The best solution is to rename it with something that is not a reserved word.

[influenceuk]

done that, now i not getting any error msgs.

 

however its still displaying the date wrong?!

[Wildbug]

What do you mean "wrong?"  Post what you want and what you're getting.  Post some code where you query the db and print the date result.

 

 

(I didn't realize "release" was a reserved word when I took the backticks off -- you can replace them, or go with your renamed column.)

[influenceuk]

I am now using this code, which includes my original code, along with the code that was provided by yourself.

 

As before i am after getting the Date display like - 13 June 2007 rather than the MySQL one - 2007-06-13

 

Cheers for all your help

 

<?php

include 'connect.php';

if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}

$max_results = 15;

$from = (($page * $max_results) - $max_results); 
$sql = mysql_query("SELECT *,DATE_FORMAT(rdate,'%e %M %Y') AS rdate_formatted FROM table2 WHERE rdate > CURRENT_DATE ORDER BY rdate LIMIT $from, $max_results");
$bgcolor = "#E0E0E0";
    echo("<table>");
    echo"<tr class=titlebg><td><b>Title</b><td><b>Studio</b><td><b>Release Date</b></tr>";
    while($row = mysql_fetch_array($sql)){
        if ($bgcolor == "#E0E0E0"){
            $bgcolor = "#FFFFFF";
        }else{
            $bgcolor = "#E0E0E0";
        }

    echo("<tr bgcolor=".$bgcolor."><td width=350 nowrap>");
    echo($row["title"]);
    echo("</td><td width=175 nowrap>");
    echo($row["studio"]);
    echo("</td><td align=\"center\">");
    echo($row["rdate"]);
    echo("</td></tr>");
    }

    echo("</table>");


$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM table2 WHERE `rdate` > CURRENT_DATE ORDER BY 'rdate'"),0);

$total_pages = ceil($total_results / $max_results);

echo "<center>Select a Page<br />";

if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\">Prev</a> -";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
        } else {
            echo " <a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
    }
}

if($page < $total_pages){
    $next = ($page + 1);
    echo "- <a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next</a>";
}
echo "</center>";
?>        

[AndyB]

Isn't rdate_formatted what you want to output rather than rdate?

[influenceuk]

huh?

 

not sure what u mean

[Wildbug]

I thought you were doing that.  You're printing the wrong value.  The "rdate" value is the MySQL standard format.  The one you're formatting is "rdate_formatted" and is the one you want to output.

 

So.... change $row[rdate] to $row[rdate_formatted] to print the formatted date instead of the regular date.

[AndyB]

So.... change $row[rdate] to $row[rdate_formatted] to print the formatted date instead of the regular date.

 

Exactly.

[influenceuk]

awesome it worked!!! cheers guys!

 

Final question i have is do u know how i can group the releases by month?

for exaple

June:

item 1 19 june 2007

 

July

Item 2 1 July 2007

Item 3 2 July 2007

[Wildbug]

You can add another column to your query, MONTHNAME(rdate) AS Month (or DATE_FORMAT(rdate,'%M')).  Then, in your PHP script, on each pass through the loop check if that value has changed since the last loop.  If so, print a header that time.

[influenceuk]

arrr, ok. i will look into this see how i get on.

 

Cheers guys for your help, its much appreciated