#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    6
    Rep Power
    0
    I am new to MySQL/PHP3 and I am learning slowely. I have however 1 problem that I can't solve. I get a wrong date format. Here is a piece of my code:$db = mysql_connect("mysql.iticom.net", "simmersw", "******");
    mysql_select_db("simmersw",$db);

    $resultd1 = mysql_query("SELECT * FROM f4hq ORDER BY id DESC",$db);
    $myrowd1 = mysql_fetch_array($resultd1);

    This will give a date like YYYYMMDDhhmmss.

    I tried already the following:

    $resultd1 = mysql_query("SELECT DATE_FORMAT(date,'%d/%m/%Y') FROM f4hq ORDER BY id DESC",$db);

    This will result in an error. I think the problem has something to do with MS access. I made the table in MS Access and uploaded it with ODBC.

    If there is something wrong with my code can somebody please correct it. If it has something to do with ODBC and MS Access can somebody then please explain how to correct it.

    Thanks in advance.


  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,642
    Rep Power
    4492
    What kind of error did you get when you used date_format? You do realize that you're only selecting one column from the database and it will not be named 'date', right? The date_format() method is the correct way to do it.

    ---John Holmes...
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    6
    Rep Power
    0
    Warning: 0 is not a MySQL result index in index_inc.php3 on line 8

    This is the error I get.
    Without the DATE_FORMAT() I get the date as a number.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Location
    Redding, CA
    Posts
    15
    Rep Power
    0
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by Edwin:
    Without the DATE_FORMAT() I get the date as a number.[/quote]

    Did you make the column a DATE column or some CARCHAR or INT type? The sample data didn't look like a DATE or datetime format.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    6
    Rep Power
    0
    I checked and it is a "timestamp" type.
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    6
    Rep Power
    0
    Maybe somebody can see the error in the code. This is the complete code:<?php


    $db = mysql_connect("mysql.iticom.net", "simmersw", "######");
    mysql_select_db("simmersw",$db);

    $resultd1 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ ORDER BY id DESC",$db);
    $myrowd1 = mysql_fetch_array($resultd1);
    $d1 = $myrowd1["date"];

    $resultup1 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date=$d1 ORDER BY id",$db);
    $myrowup1 = mysql_fetch_array($resultup1);


    $resultd2 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date<$d1 ORDER BY id DESC",$db);
    $myrowd2 = mysql_fetch_array($resultd2);
    $d2 = $myrowd2["date"];

    $resultup2 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date=$d2 ORDER BY id",$db);
    $myrowup2 = mysql_fetch_array($resultup2);


    $resultd3 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date<$d2 ORDER BY id DESC",$db);
    $myrowd3 = mysql_fetch_array($resultd3);
    $d3 = $myrowd3["date"];

    $resultup3 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date=$d3 ORDER BY id",$db);
    $myrowup3 = mysql_fetch_array($resultup3);


    $resultd4 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date<$d3 ORDER BY id DESC",$db);
    $myrowd4 = mysql_fetch_array($resultd4);
    $d4 = $myrowd4["date"];

    $resultup4 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date=$d4 ORDER BY id",$db);
    $myrowup4 = mysql_fetch_array($resultup4);


    $resultd5 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date<$d4 ORDER BY id DESC",$db);
    $myrowd5 = mysql_fetch_array($resultd5);
    $d5 = $myrowd5["date"];

    $resultup5 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date=$d5 ORDER BY id",$db);
    $myrowup5 = mysql_fetch_array($resultup5);


    $resultd6 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date<$d5 ORDER BY id DESC",$db);
    $myrowd6 = mysql_fetch_array($resultd6);
    $d6 = $myrowd6["date"];

    $resultup6 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date=$d6 ORDER BY id",$db);
    $myrowup6 = mysql_fetch_array($resultup6);


    $resultd7 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date<$d6 ORDER BY id DESC",$db);
    $myrowd7 = mysql_fetch_array($resultd7);
    $d7 = $myrowd7["date"];

    $resultup7 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date=$d7 ORDER BY id",$db);
    $myrowup7 = mysql_fetch_array($resultup7);


    $resultd8 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date<$d7 ORDER BY id DESC",$db);
    $myrowd8 = mysql_fetch_array($resultd8);
    $d8 = $myrowd8["date"];

    $resultup8 = mysql_query("SELECT DATE_FORMAT(date, '%d %M %Y') FROM F4HQ WHERE date=$d8 ORDER BY id",$db);
    $myrowup8 = mysql_fetch_array($resultup8);


    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD BACKGROUND='graphics/headerback.jpg' HEIGHT='27'>n";
    echo "<FONT COLOR='Black' SIZE='2' FACE='Verdana'>n";

    printf("&nbsp;News for %sn", $myrowd1["date"]);


    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";


    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";

    printf("<BR> %sn", $myrowup1["remark"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";


    do
    {
    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD></td>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";
    echo "<BR>n";

    printf("<LI><a href="%s.php3">%s</a>: %s<BR>n", $myrowup1["sort"], $myrowup1["sort"], $myrowup1["note1"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    }

    while ($myrowup1 = mysql_fetch_array($resultup1));


    echo "</TABLE>n";

    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD BACKGROUND='graphics/headerback.jpg' HEIGHT='27'>n";
    echo "<FONT COLOR='Black' SIZE='2' FACE='Verdana'>n";

    printf("&nbsp;News for %sn", $myrowd2["date"]);


    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";

    printf("<BR> %sn", $myrowup2["remark"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";


    do
    {
    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD></td>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";
    echo "<BR>n";

    printf("<LI><a href="%s.php3">%s</a>: %s<BR>n", $myrowup2["sort"], $myrowup2["sort"], $myrowup2["note1"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    }

    while ($myrowup2 = mysql_fetch_array($resultup2));


    echo "</TABLE>n";






    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD BACKGROUND='graphics/headerback.jpg' HEIGHT='27'>n";
    echo "<FONT COLOR='Black' SIZE='2' FACE='Verdana'>n";

    printf("&nbsp;News for %sn", $myrowd3["date"]);


    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";

    printf("<BR> %sn", $myrowup3["remark"]);


    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    do
    {
    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD></td>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";
    echo "<BR>n";

    printf("<LI><a href="%s.php3">%s</a>: %s<BR>n", $myrowup3["sort"], $myrowup3["sort"], $myrowup3["note1"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    }

    while ($myrowup3 = mysql_fetch_array($resultup3));


    echo "</TABLE>n";


    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD BACKGROUND='graphics/headerback.jpg' HEIGHT='27'>n";
    echo "<FONT COLOR='Black' SIZE='2' FACE='Verdana'>n";

    printf("&nbsp;News for %sn", $myrowd4["date"]);


    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";

    printf("<BR> %sn", $myrowup4["remark"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    do
    {
    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD></td>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";
    echo "<BR>n";

    printf("<LI><a href="%s.php3">%s</a>: %s<BR>n", $myrowup4["sort"], $myrowup4["sort"], $myrowup4["note1"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    }

    while ($myrowup4 = mysql_fetch_array($resultup4));


    echo "</TABLE>n";



    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD BACKGROUND='graphics/headerback.jpg' HEIGHT='27'>n";
    echo "<FONT COLOR='Black' SIZE='2' FACE='Verdana'>n";

    printf("&nbsp;News for %sn", $myrowd5["date"]);


    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";

    printf("<BR> %sn", $myrowup5["remark"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    do
    {
    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD></td>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";
    echo "<BR>n";

    printf("<LI><a href="%s.php3">%s</a>: %s<BR>n", $myrowup5["sort"], $myrowup5["sort"], $myrowup5["note1"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    }

    while ($myrowup5 = mysql_fetch_array($resultup5));


    echo "</TABLE>n";



    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD BACKGROUND='graphics/headerback.jpg' HEIGHT='27'>n";
    echo "<FONT COLOR='Black' SIZE='2' FACE='Verdana'>n";

    printf("&nbsp;News for %sn", $myrowd6["date"]);


    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";

    printf("<BR> %sn", $myrowup6["remark"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    do
    {
    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD></td>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";
    echo "<BR>n";

    printf("<LI><a href="%s.php3">%s</a>: %s<BR>n", $myrowup6["sort"], $myrowup6["sort"], $myrowup6["note1"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    }

    while ($myrowup6 = mysql_fetch_array($resultup6));


    echo "</TABLE>n";



    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD BACKGROUND='graphics/headerback.jpg' HEIGHT='27'>n";
    echo "<FONT COLOR='Black' SIZE='2' FACE='Verdana'>n";

    printf("&nbsp;News for %sn", $myrowd7["date"]);


    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";

    printf("<BR> %sn", $myrowup7["remark"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    do
    {
    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD></td>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";
    echo "<BR>n";

    printf("<LI><a href="%s.php3">%s</a>: %s<BR>n", $myrowup7["sort"], $myrowup7["sort"], $myrowup7["note1"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    }

    while ($myrowup7 = mysql_fetch_array($resultup7));


    echo "</TABLE>n";



    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD BACKGROUND='graphics/headerback.jpg' HEIGHT='27'>n";
    echo "<FONT COLOR='Black' SIZE='2' FACE='Verdana'>n";

    printf("&nbsp;News for %sn", $myrowd8["date"]);


    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";

    printf("<BR> %sn", $myrowup8["remark"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    echo "</TABLE>n";

    do
    {
    echo "<TABLE WIDTH='90%' ALIGN='center'>n";
    echo "<TR>n";
    echo "<TD></td>n";
    echo "<FONT COLOR='white' SIZE='1' FACE='Verdana'>n";
    echo "<BR>n";

    printf("<LI><a href="%s.php3">%s</a>: %s<BR>n", $myrowup8["sort"], $myrowup8["sort"], $myrowup8["note1"]);

    echo "</FONT>n";
    echo "</TD>n";
    echo "</TR>n";
    }

    while ($myrowup8 = mysql_fetch_array($resultup8));


    echo "</TABLE>n";
    ?>

    The output can be found at http://www.simmersworld.com/index3.php3

    The lines that give the errors are like:
    $myrowd2 = mysql_fetch_array($resultd2);

    Only the first line like that:
    $myrowd1 = mysql_fetch_array($resultd1);
    Does not seem to give an error.
  12. #7
  13. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,642
    Rep Power
    4492
    Wow....where do I start??

    First, explain what you're trying to do with all of those queries. I'm sure you can cut it down to one or two.

    Next...you are only selecting one thing with each query, so all your references to row["date"] row["remark"] etc are not valid.

    use SELECT id,row,remark,DATE_FORMAT(date,'%d/%m/%Y') FROM f4hq

    Also, you use f4hq in one example, then F4HQ in another. Mysql is case sensitive unless your on windows.

    next, when you reference date=$d1, it should have quotes around it date='$d1'.

    You're getting errors because your first select statement returns one row, but you don't assign it a name, so $d1 = $myrowd1["date"] is getting a null value. Then it makes your second query look like "date=" with nothing following the equals sign.

    Whenever you use date_format(), you should use AS to give it a value to refer to in your associative array.

    SELECT id,remar,DATE_FORMAT(date,'%d/%m/%Y') as f_date FROM f4hq

    Then, after you fetch the row, $row["f_date"] will hold your formatted date value. You can't use this as a reference in another select statement because it's not in a valid mysql timestamp format, either...

    wow....have fun!

    ---John Holmes...

    ------------------
    *************************************************************
    * The manual can probably answer 90% of your questions...
    *
    * PHP Manual. www.php.net/manual
    * MySQL Manual: www.mysql.com/documentation/mysql/bychapter
    *************************************************************
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    6
    Rep Power
    0
    Thanks for the replys so far. I don't have much time now but I will soon try a few things and explain what I want with the code. Thanks again.

Similar Threads

  1. Sort by my date format
    By Mickster in forum PHP Development
    Replies: 5
    Last Post: February 19th, 2004, 01:33 PM
  2. Empty date & date format
    By anca in forum Visual Basic Programming
    Replies: 2
    Last Post: February 17th, 2004, 04:38 AM
  3. Strange date format
    By mcnorth in forum Software Design
    Replies: 1
    Last Post: February 11th, 2004, 02:36 PM
  4. Need newbie help with date range.
    By mannikin in forum PHP Development
    Replies: 8
    Last Post: February 4th, 2004, 09:08 PM
  5. Convert date format
    By RockyRR in forum MySQL Help
    Replies: 14
    Last Post: January 21st, 2004, 07:27 AM

IMN logo majestic logo threadwatch logo seochat tools logo