Date problem

date

[Awful newbie]

Ok, remember that I am an awful newbie. I have problems with my date displayed. Yes, I have checked the php.net date function. I want the date outputted to display d.M.Y and not the default Y.m.d

I have this query getting the date (for the document stored):
(at the very end of the code). Please.

$showdoc = mysql_query("SELECT * FROM $prefix"."documents where catid=$r_doccat ");
while ( $rowshowdoc = mysql_fetch_array($showdoc) ) {
$row_color = ($row_count % 2)? $color1 : $color2;
echo "<table width=100%><tr bgcolor='$row_color'><td>";
echo("<span class='mini'>Doc:</span><span class='norm'> ".$rowshowdoc ["name"]."</span></td><td><b><a target='_blanc' href='./docs/".$rowshowdoc ["name"]."'>show doc</a></b></td><td class='mini' align='left'>Date: " . $rowshowdoc ["date"] . "</td></tr>\n");

[phparion]

$showdoc = mysql_query("SELECT DATE_FORMAT(date_column_name, %d %M %Y) as Newdate, * FROM $prefix"."documents where catid=$r_doccat ");

you will get new date form in Newdate variable. you can omit * and use the exact names of the other columns to exclude date selection again.

[Awful newbie]

Thanks! I tried as you said ending up with this:

//(the old query)$showdoc = mysql_query("SELECT * FROM $prefix"."documents where catid=$r_doccat ");

//your suggestion:
$showdoc = mysql_query("SELECT DATE_FORMAT(date_column_name, %d %M %Y) as Newdate, * FROM $prefix"."documents where catid=$r_doccat ");

//BUT THE I GET THE FOLLOWING ERROR:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\here\test\documents.php on line 38

//THIS IS LINE 38:
while ($rowshowdoc = mysql_fetch_array($showdoc) ) {

$row_color = ($row_count % 2)? $color1 : $color2;
echo "<table width=100%><tr bgcolor='$row_color'><td>";
echo("<span class='mini'>Doc:</span><span class='norm'> ".$rowshowdoc ["name"]."</span></td><td><b><a target='_blanc' href='./docs/".$rowshowdoc ["name"]."'>show documents</a></b></td><td class='mini' align='left'>Date: " . $rowshowdoc ["Newdate"] . "</td></tr>\n");

[phparion]

$showdoc = mysql_query("SELECT DATE_FORMAT(date_column_name, %d %M %Y) as Newdate, * FROM $prefix"."documents where catid=$r_doccat ");

did you replace 'date_column_name' with your table's actual date's column name or not? lol

paste your exact code here...

[Awful newbie]

Well.. (blush) first I didn't... But then I did, and I got the same error message.

$showdoc = mysql_query("SELECT DATE_FORMAT(date, %d %M %Y) as Newdate, * FROM $prefix"."documents where catid=$r_doccat ");

//I am told that the following line are wrong when entering the query as you told:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\here\docs\documents.php on line 38

//the following line is told to be wrong (with the new query):
while ($rowshowdoc = mysql_fetch_array($showdoc) ) {

$row_color = ($row_count % 2)? $color1 : $color2;
echo "<table width=100%><tr bgcolor='$row_color'><td>";
echo("<span class='mini'>Doc:</span><span class='norm'> ".$rowshowdoc ["name"]."</span></td><td><b><a target='_blanc' href='./docs/".$rowshowdoc ["name"]."'>show doc</a></b></td><td class='mini' align='left'>Date: " . $rowshowdoc ["date"] . "</td></tr>\n");
echo "<tr><td colspan='3' bgcolor='$row_color'><i><span class='mini'>".$rowshowdoc ["description"]."</span></i></td></tr>";

[phparion]

the error you are getting is due to empty Resource from mysql_query() which means your query is not fetching any records which is absolutely confusing for me....

anyway, remember the steps to debug such code.. first of all print your query..

echo "SELECT DATE_FORMAT(date, %d %M %Y) as Newdate, * FROM $prefix"."documents where catid=$r_doccat ";

this will show you the actual shape of the query before it is executed against the database... check if any variable value is missing...

then execute this query directly to database using Command Line or phpMyAdmin to see if you get any error...

by the way try to replace * from the query with the column names e.g

"SELECT DATE_FORMAT(date, %d %M %Y) as Newdate, col1,col2,col3 FROM $prefix"."documents where catid=$r_doccat "

that might be an issue...

[dublinmike]

I would recommend not using 'date' as the name of your date field in the db, that might be why your query isn't executing, 'date' is a function in mysql - ironically it's used to deal with exactly what you want to accomplish ;-)

To see exactly what the problem is with your query append 'or die(mysql_error())' after your query, so it should read "$showdoc = mysql_query("SELECT DATE_FORMAT(date_column_name, %d %M %Y) as Newdate, * FROM $prefix"."documents where catid=$r_doccat ") or die(mysql_error());"

[phparion]

yeah, you are very right and I am very confused that how did he succeed to make a table with DATE column name mysql should have given him error.

[jatar_k]

it will let you create the table but the problems will show up later, especially when querying

[dublinmike]

Yup, I've always found that ridiculous ;-)

[Awful newbie]

Hm, I added the die() and got the following error:

Parse error: parse error, unexpected T_STRING in C:\xampp\here\docs\documents.php on line 40

And line 40 is here:
echo "<table width=100%><tr bgcolor='$row_color'><td>";

The odd thing is that the script works with the other date-option.
This app is a modified version of tigerdms(dot com). Originally I thought about contacting them, but that's not possible.

Maybe you will be bored to death, but here is the whole mess in documents.php:

<?php
session_start();
include 'conection.php';
import_request_variables("gP", "r_");

//this is modified by me, alternating row colors)
$color1 = "#CCFFCC";
$color2 = "#f0f0f0";
$row_count = "0";
$date = date("d.M.Y");

echo "<html>";
echo "<head>";
echo "<title>Documents</title>";
echo "<link rel='stylesheet' type='text/css'href='style.css' />";
echo "</head>";
echo "<body bgcolor='#ffffff'>";
echo "<h2>Some documents</h2>";

$permit = mysql_query("SELECT * FROM $prefix"."userper where userid='$_SESSION[id]'");
if (!$permit) {
echo("<P>Error querying: " .
mysql_error() . "</P>");
exit();
}
$num_rows = mysql_num_rows($permit);
if ($num_rows == 0){
echo "<p>Not allowd to view category</p>";
echo "Back to <a href='index.php'>main page<a/>";}
else{

//if show doc is pressed
if (isset($r_showcat)){

//$showdoc = mysql_query("SELECT * FROM $prefix"."documents where catid=$r_doccat ");

//the hint you have given me in order to change the date formatting
$showdoc = mysql_query("SELECT DATE_FORMAT(date, %d %M %Y) as Newdate, * FROM $prefix"."documents where catid=$r_doccat ")or die(mysql_error());"

while ($rowshowdoc = mysql_fetch_array($showdoc) ) {

this line is added by me in order to alternate row colors
$row_color = ($row_count % 2)? $color1 : $color2;

echo "<table width=100%><tr bgcolor='$row_color'><td>";
echo("<span class='mini'>Ark:</span><span class='norm'> ".$rowshowdoc ["name"]."</span></td><td><b><a target='_blanc' href='./docs/".$rowshowdoc ["name"]."'>show documents</a></b></td><td class='mini' align='left'>Date: " . $rowshowdoc ["date"] . "</td></tr>\n");
echo "<tr><td colspan='3' bgcolor='$row_color'><i><span class='mini'>".$rowshowdoc ["description"]."</span></i></td></tr>";
$row_count++;
echo "</tr></table><br /> \n";
} }

//this is to select documents to show
echo "<br /><h3>select category documents</h3>";
echo "<form action='documents.php' method='post' \n> ";
echo "Category<br><select name='doccat'> \n";
while ( $rownewdoc = mysql_fetch_array($permit) ) {
$newus = mysql_query("SELECT * FROM $prefix"."category where id=$rownewdoc[catid] ");
while ( $rownewus = mysql_fetch_array($newus) ) {
echo("<option value=".$rownewdoc["catid"].">" . $rownewus["category"] . "</option>\n");
}}
echo "</select> <br>\n";
echo "<input type='submit' name='showcat' value='select category'> \n";
echo "</form>";
echo "<br><br><a href='index.php'>to start</a>";
echo "<br><br><a href='userlogout.php'>Log out</a>";
echo "<br> \n" ;

echo "<html><body>";
}
?>

[phparion]

Hm, I added the die() and got the following error:

Parse error: parse error, unexpected T_STRING in C:\xampp\here\docs\documents.php on line 40

you added die to mysql query and in return it started to spit out parse error .. that is funny :D...

anyway, you are not reading our posts with consideration, I guess...

your date column name is 'date' which is mysql reserve word and it might be creating problems, so change the column name by altering table...

as long as it is about the error you have shown... you get it mostly when you are trying to display array in quotes e.g

echo "$row['handle']";

however this might work

echo "$row[handle]"; // but not recommended it could break up

this is recommended way to do it..

echo $row['handle'];

so check if you are displaying any array value within quotes...