SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Hybrid View

  1. #1
    billycundiff{float:left;} silver trophybronze trophy RyanReese's Avatar
    Join Date
    Oct 2008
    Location
    Whiteford, Maryland, United States
    Posts
    13,564
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Echoing the variable shows one thing, however the database is showing another

    Hi, I have this script below. I echo in there the date, time, and the message. The message matches what is showin my database. However, the echo that I put on this page doesn't match what it should

    EDIT-http://ryanreese.us/checkInfo.php

    That page actually has the echoing. The recall.php is taking from the database and is showing a whole bunch of 0's in place of date/time. The checkInfo.php when you go there shows what the actual date() and time() is, and what SHOULD be entered into the database.

    This is my recall.php
    Code:
    <?phpsession_start();
    include "config.php";
    $link = mysql_connect($host, $username, $password);
    
    
    if(!$link){echo "link";die(mysql_error());}
    $db_selected = mysql_select_db($DbName, $link);
    if(!$db_selected){echo "dbselect";die(mysql_error());}
    
    
    
    
    $i=0;
    $query="SELECT * FROM userSubmitted";
    
    
    $result=mysql_query($query) or die(mysql_error());
    
    
    $num=mysql_num_rows($result);
    
    
    while ($i <= $num) {
    
    
    $fieldOne=mysql_result($result,$i,"PostNumber");
    $fieldTwo=mysql_result($result,$i,"Date");
    $fieldThree=mysql_result($result,$i,"Time");
    $fieldFour=mysql_result($result,$i,"Content");
    
    
    echo $fieldOne;
    echo "<br>";
    echo $fieldTwo;
    echo "<br>";
    echo $fieldThree;
    echo "<br>";
    echo $fieldFour;
    
    
    echo "<br><br>";
    
    
    $i++;
    }
    echo "end of script";
    mysql_close();
    ?>
    I have this code for checkInfo.php
    Code:
    <?phpinclude ("config.php");
    ?>
    <!doctype html>
    <html>
    <head>
    <title>check</title>
    </head>
    <body>
    <?php
    
    
    $contentOfPost=$_POST['contentOfPost'];
    $date = date("Y/m/d");
    $time = time();
    
    
    $link = mysql_connect($host, $username, $password);
    
    
    if(!$link){echo "link";die(mysql_error());}
    $db_selected = mysql_select_db($DbName, $link);
    if(!$db_selected){echo "dbselect";die(mysql_error());}
    $insertionToDatabase="INSERT INTO $tableName (PostNumber, Date, Time, Content) VALUES (NULL, $date, $time, '$contentOfPost')";
    
    
    $result = mysql_query($insertionToDatabase);
    if (!$result) {
        die('Invalid query: ' . mysql_error());
    }
    
    
    echo $contentOfPost;
    echo "<br>";
    echo $date;
    echo "<br>";
    echo $time;
    echo"<br>";
    echo $result;
    mysql_close($link);
    
    
    ?>
    
    
    </body>
    </html>
    I can describe my database if you wish. Just 4 columns. PostNumber (auto increment), Date, Time, and Content. Time in the database is a Timestamp, the date is set as a Date.

    Oh, just went to my page, apparently someone is having fun with my blog.php, because there are somehow 12 blog posts, and ther eis only supposed to be like 2-3 in there (that were my own)...
    Twitter-@Ryan_Reese09
    http://www.ryanreese.us -Always looking for web design/development work

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by RyanReese View Post
    Time in the database is a Timestamp, the date is set as a Date.
    that's redundant

    a mysql timestamp includes the date, so the date column isn't really necessary

    i don't do php, but i'll bet that $time = time(); is being entered into the timestamp column as a time only, i.e. with a zero date portion

    you should really have only one column

    TIMESTAMP is okay, but in my opinion DATETIME is better

    (TIMESTAMP will update automatically whenever the row is updated, so if you make a post, and then come back and maybe do some editing on the content, the timestamp will be updated, whereas a DATETIME will update only when you actually update it, which you probably wouldn't do if you were just fixing content typos...)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    billycundiff{float:left;} silver trophybronze trophy RyanReese's Avatar
    Join Date
    Oct 2008
    Location
    Whiteford, Maryland, United States
    Posts
    13,564
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    If I only had one column in my database, would I be able to pick out blog posts that only have dates on a certain day? I don't know how DATETIME formats their entries.

    $time=time(); only. No formatting has been done. What would I need to change in my code now? I'll change my database accordingly to merge the time/date together into DATETIME
    Twitter-@Ryan_Reese09
    http://www.ryanreese.us -Always looking for web design/development work

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,044
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Use the MySQL function FROM_UNIXTIME() to convert the return value of time() to the proper, expected format.

    Code SQL:
    $insertionToDatabase="INSERT INTO $tableName (PostNumber, Date, Time, Content) VALUES (NULL, $date, FROM_UNIXTIME($time), '$contentOfPost')";
    The only code I hate more than my own is everyone else's.

  5. #5
    billycundiff{float:left;} silver trophybronze trophy RyanReese's Avatar
    Join Date
    Oct 2008
    Location
    Whiteford, Maryland, United States
    Posts
    13,564
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Thank you oddz, now when I go to submit a post, the Time section is displaying such as this

    2012-02-27 20:03:37

    However one more issue. The date isn't showing as expected. The Time column in my database has the date/time, so should I merge those columns into a Date/Time column?

    In a perfect world, the date column will disply the 2012-02-27 in that column, while the time column will only have the time.

    We're making progress .

    Edit-to clarify, my date is still showing a string of 0's. As expected.
    Twitter-@Ryan_Reese09
    http://www.ryanreese.us -Always looking for web design/development work

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by RyanReese View Post
    ... so should I merge those columns into a Date/Time column?
    yes, that's my recommendation

    Quote Originally Posted by RyanReese View Post
    In a perfect world, the date column will disply the 2012-02-27 in that column, while the time column will only have the time.
    that's far from perfect, i'm afraid

    displaying date and time separately is trivially easy, but storing and manipulating them as separate columns is problematic
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,044
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    No, do as @r937 suggested and trash the date column because it is redundant. You don't need to split the date and time portion between two separate columns. The *only* reason one *might* consider that is for optimization reasons (if you needed to join on the date portion only). However, that would probably be a rare case and is much more advanced/complex than what you need here. Also, it can always be added later when it is absolutely necessary. Otherwise your just making things more complex than they really need to be given the time manipulation functions available in both MySQL and PHP.
    The only code I hate more than my own is everyone else's.

  8. #8
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,578
    Mentioned
    62 Post(s)
    Tagged
    3 Thread(s)
    @RyanReese,

    With PHP and MySql you have just opened Pandora's Box

    Check out http://www.php.net/manual/en/index.php

    PHP Code:

    define
    ('jj''<br />');


      while (
    $row mysql_fetch_object($result))
      {
         echo 
    jj'PostNumber: '$row->PostNumber;
         echo 
    jj$row->Date;
          
        
    $tmp strtotime$row->Date );    
        echo 
    jjdate("l jS \of F Y h:i:s"$tmp );
        
        echo 
    jj'$row->Content';
      } 
    #Output

    PostNumber: 1
    2012-02-17 21:34:21
    Friday 17th of February 2012 09:34:21
    $row->Content

    PostNumber: 2
    2012-02-17 21:22:32
    Friday 17th of February 2012 09:22:32
    $row->Content

    PostNumber: 3
    2012-02-17 21:24:08
    Friday 17th of February 2012 09:24:08
    $row->Content

    PostNumber: 4
    2012-02-11 12:37:28
    Saturday 11th of February 2012 12:37:28
    $row->Content
    Last edited by John_Betong; Feb 27, 2012 at 22:37. Reason: fieldname incorrect

  9. #9
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In your database, have 1 field say DATED VARCHAR(20) and make your insert command like this:

    HTML Code:
    $insertionToDatabase="INSERT INTO $tableName (PostNumber, Dated, Content) VALUES (NULL, time(), '$contentOfPost')";
    Now, when you display use this:

    PHP Code:
    $fieldOne=mysql_result($result,$i,"PostNumber");
    $fieldTwo=mysql_result($result,$i,"Dated");
    $fieldThree=mysql_result($result,$i,"Content");


    echo 
    $fieldOne;
    echo 
    "<br>";
    echo 
    date("Y-m-d (h:i:s)",$fieldTwo); // this will show 2012-02-27 (12:00:05).          IMP: use php.net for full date / time formats
    echo "<br>";
    echo 
    $fieldThree;

    //You can use other formats like:
    echo date("Y-m-d",$fieldTwo); // 2012-02-27
    echo date("M/d/Y",$fieldTwo); // Feb/02/2012
    echo date("h:i:s a",$fieldTwo); // 12:11:12 am 
    Above way, your database will have a timestamp via time(). And you can use that to show the date in any format you want.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    VARCHAR??? oh, please
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    billycundiff{float:left;} silver trophybronze trophy RyanReese's Avatar
    Join Date
    Oct 2008
    Location
    Whiteford, Maryland, United States
    Posts
    13,564
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    I'll rewrite my code to compensate for only one column now, and I'll editm y code accordingly.

    @John, that code you just posted, what is that replacing? ALL of my echoing code...?
    Twitter-@Ryan_Reese09
    http://www.ryanreese.us -Always looking for web design/development work

  12. #12
    billycundiff{float:left;} silver trophybronze trophy RyanReese's Avatar
    Join Date
    Oct 2008
    Location
    Whiteford, Maryland, United States
    Posts
    13,564
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Ok now it is displaying correctly.

    1
    2012-02-28 12:38:41

    message
    Running



    Twitter-@Ryan_Reese09
    http://www.ryanreese.us -Always looking for web design/development work


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •