SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Setting date on database

    Evrytime I was trying to enter the date into my
    Database I was getting the date there 00-00-0000
    What can I do so it would set the date on the database?

    By the way my the date on the visitors table is "DATE" type.
    Code:
    <?php
    $mysql_link = mysql_connect(*************) or die("ERROR 001: Cannot connect to MySQL server. please contact system administration.");
    
    if  (!mysql_select_db(********* $mysql_link))
    echo "Could not select DataBase.";
    
    $result = mysql_query("SELECT date,count FROM visitors");
    
    echo date('j-n-Y');
    $date=date('j-n-Y');
    $count = 0;
    while ($row = mysql_fetch_array($result))
    {
    	if (strpos(date('Y-n-j'),$row['date'])!== FALSE)
    	{
    		$count = $row['count'] + 1;
    		break;
    	}
    }
    
    if  ($count !== 0)
    	mysql_query("UPDATE visitors SET count=".$count." WHERE date=".$date);
    else
    {
    	$count = 1;
    	mysql_query("INSERT INTO visitors (date, count) VALUES ('".$date."','".$count."')");
    }
    
    ?>
    Edit:
    What is the best way to insert date to database so I could read from database like that:
    "SELECT ... FROM ... BETWEEN date1 AND date2

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it must be in year-month-day sequence only

    e.g. '2011-01-10' or '2011/01/10' or '2011$01$10' or '20110110'

    or as numeric (without quotes) 20110110
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you now I can insert date..
    but why isn't it updating the count ?

    Code:
    $date=date('Y-m-d');
    echo $date;
    
    $count = 0;
    while ($row = mysql_fetch_array($result))
    {
    	if (strpos($date,$row['date'])!== FALSE)
    	{
    		$count = $row['count'] + 1;
    		break;
    	}
    }
    
    if  ($count > 0)
    	mysql_query("UPDATE visitors SET count=".$count." WHERE date=".$date);
    else
    {
    	$count = 1;
    	mysql_query("INSERT INTO visitors (date, count) VALUES ('".$date."','".$count."')");
    }
    for some reason the "WHERE date = ..."
    is not equal :\

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by danrevah View Post
    for some reason the "WHERE date = ..."
    is not equal :\
    the reason is you forgot the quotes that are needed because of the dashes

    this is right: 20110110

    and this is also right: '2011-01-10'

    but this is wrong: 2011-01-10

    2011 minus 1 minus 10 equals 2000 and that's not a valid date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •