SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    billycundiff{float:left;} silver trophybronze trophy RyanReese's Avatar
    Join Date
    Oct 2008
    Location
    Whiteford, Maryland, United States
    Posts
    13,599
    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)...
    Always looking for web design/development work. Willing to do it cheap to build portfolio!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 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...)
    rudy.ca | @rudydotca
    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,599
    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
    Always looking for web design/development work. Willing to do it cheap to build portfolio!

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    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,599
    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.
    Always looking for web design/development work. Willing to do it cheap to build portfolio!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 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
    rudy.ca | @rudydotca
    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,147
    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,833
    Mentioned
    73 Post(s)
    Tagged
    6 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
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Patche on earning Member of the Month for July 2014

  9. #9
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,134
    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,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    VARCHAR??? oh, please
    rudy.ca | @rudydotca
    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,599
    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...?
    Always looking for web design/development work. Willing to do it cheap to build portfolio!

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

    1
    2012-02-28 12:38:41

    message
    Running



    Always looking for web design/development work. Willing to do it cheap to build portfolio!


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
  •