MySQL dates defaulting

jrmg

😕 I have a DB of various clients who can log-in to their site admin tool and do stuff - When they create their account or later log-in etc, the site is supposed to update the MySQL DB with their last log in date/time - Problem is, it doesn't and when it does (appears to only work when I use the DB admin tool) all the dates default to the same date?

There are 3 date/time DB fields: Created, Last Log-in, Registration

Created appears to work
Last Log defaults to same as existing clients
Registration doesn't work at all and gives 0000-00-00 00:00:00 (though the posting script may be the cause of this - I'm working on it)

Most important to start I guess is the Last Log defaulting? Here is the code:

	$query = "UPDATE clients set last_login_date='" . date("Y-m-d H:i:s.", getlastmod()) . "'";

Could it be a simple date format problem or what? I use PHP MyAdmin for most back-end access. Thanks, JR

koolkrazy

Try seperating out the field where you put the date and time together, like put date in one field and time in to another one.

Also mysql stores the date in YYYY-MM-DD format and time in HH:MM:SS format. So if you missout any one then the date is defaulted to the default value u had set when u created the table and if you had not set a default value then it puts in 0000-00-00

Stinger51

MySQL also supports a "timestamp" type, which automatically gets updated with the current date and time when you write anything to the record.

If I understood your question correctly, this may be what you are looking for. Here's a link for more info.

http://www.mysql.com/doc/en/DATETIME.html

HTH

jrmg

Thanks for the help. To clarify - there are two pieces of code - 1st one is log-in update, 2nd one is a call-back script that writes new member details to the DB.

In response to the 1st prob:

/** Update last_login_date in clients table */
$query = "UPDATE clients set last_login_date='" . date("Y-m-d H:i:s.", getlastmod()) . "'";

The format is actually working, but when a client logs-in, ALL the other clients 'log-in' dates updates to the same date - so it appears everyone log-in at the same time?

2nd Prob (this is the one giving 000:000 etc), the code is set to:

set live_date='now()', property_num='$property_num', currency='$M_currency', subscription_length='$M_length'...

Which appears to be what your reference link suggests, but it doesn't work. So now I'm confused, because the format for prob 1 works (but shouldn't?) and the 2nd format doesn't but should!

So there are 2 Q's:

1 - Surely it is not normal for all the dates in that "column" of the DB (as you look at in via PHP Admin) to default like this - Have you heard of this problem before?

2 - Why doesn't set live_date 'now()' work?

Thanks, JR

Stinger51

I thought what you were trying to do was automatically update a field with a date stamp when a certain ID logs in. I assume this ID is an autonum, so that it is unique?

If you set the column that records their log in time to the timestamp type, it will automatically update itself. No other record should update if the record ID is unique.

Otherwise, to avoid any confusion with using the timestamp type (and if can be confusing, even after reading the documentation) you could use the regular datestamp type and do this manually. It will just be one extra thing you have to take care of.

HTH

rinjani

Problem one is that you need to specify the particular user in your query with something like:

WHERE id = '$USER_ID';

otherwise it will just update every record.

jrmg

Sorry, I have confused everybody. Here is today's problem!

The following code (all of it for ease) is used when a "client" logs-in. It DOES at present update the relevant MySQL DB field, called 'last_login_date' properly with a date_stamp.

Unfortunately, it also updates every other clients' records with the same datestamp for that field (last_login_date). So every single "client" suddenly has the same log-in date? Here is the code:

<?php

/
Filename: login_client.php
Parameters:
- $username
- $password;
/

require("../INC_common.php");

$conn = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD)
	or die ("Could not connect to Database Server");
mysql_select_db(DB_NAME, $conn) or die ("Could not select the Database.");

$query = "select client_id, last_login_date from clients where username='${username}' and password='${password}'";
$result = mysql_query($query)
	or die (mysql_error());

$client_id = "";
while ($row = mysql_fetch_array($result)) 
{
	$client_id = $row["client_id"];
	$last_login_date = $row["last_login_date"];
}

if ($client_id != "") 
{
	/** Update last_login_date in clients table */
	$query = "UPDATE clients set last_login_date='" . date("Y-m-d H:i:s.", getlastmod()) . "'";

	mysql_query($query)
		or die (mysql_error());

	session_start();
	session_register("username");
	session_register("client_id");
	session_register("last_login_date"); /** Previous login date time */
	print("<script>location.replace('site_manager.php')</script>");
	exit();
}
else
{
print("<script>location.replace('login.php?client_id=$client_id&message=Either%20password%20or%20username%20is%20incorrect')</script>");
exit();
}

Does this clarify the problem - I thought I WAS specifying the client_id? JR

Stinger51

My eyes glaze over when I try to read other people's code, esp. if it's more than 2 or 3 lines. But I did look thru yours and am still confused.

I thought you were just trying to update a client's log in time when he/she logged in? Then why are you doing the looping below? You already have their ID, so just update their record and be done with it.

This is what I would do:

$table = "clients";
$val = now();

$query = "UPDATE $table set last_login_date = $val WHERE username = $username";

It's really pretty simple.

Also, what's up with the "{" around the variable $username?

Andy_B2

Yes, you need a 'Where' clause on your 'Update clients' call.

Look at the line:
UPDATE clients set last_login_date='<<DATE>>'

Nowhere does it specify WHO'S last_login_date to update, so it does the LOT. Echo the number of affected rows after the update, you'll see what I mean.

jrmg

You seem to be right - it does appear to be updating all the files. I also tried:

$table = "clients";
$val = now();

$query = "UPDATE $table set last_login_date = $val WHERE username = $username";

but couldn't make it work. I have tried various changes to the string to specify username, but only get errors so the whole log-in doesn't work.

I am confused. I know what I need to do but not how to do it. JR

Weedpacket

The username is a piece of text? Then it needs to be quoted in the query:

.... WHERE username = '$username'

jrmg

I have tried every conformation I can think of, including:

	$query = "UPDATE clients set last_login_date=' . date("Y-m-d H:i:s.", getlastmod()) . "'" WHERE username = '$username'";

(parse error)

and this:

	$query = "UPDATE clients set last_login_date=' WHERE username = '$username'" . date("Y-m-d H:i:s.", getlastmod()) . "'";

(syntax error)

and this:

	$query = "UPDATE clients set last_login_date=' WHERE username = $username" . date("Y-m-d H:i:s.", getlastmod()) . "'";

(works but the date defaults to 0000...)

I have tried every combo I can think of. What am I missing?

KoshNaranek

öööööhhmmm

The syntax is "völlig durcheinander"

You are setting a date to "WHERE username..." ... that's nonsens.
It's a date-field in the DB?! "WHERE..." isn't a date. So it's "0000-00..."

try

$sql="UPDATE clients SET last_login_date = now() WHERE username = '$username'";

The now() works fine in my scripts... its a SQL-Build-In and returns the actual date of the SQL-Server

This SQL-Clause should work.

jrmg

That worked. I don't get it though, why doesn't this work:

$query = "UPDATE clients set last_login_date=' . date("Y-m-d H:i:s.", getlastmod()) . "'" WHERE username = '$username'";

Isn't it fundamentally the same thing? Also, a double thank you, because I had another function not working (see my second post below):

set live_date='now()', property_num=...

I changed it to live_date = now() (as you did it) and it now works too. Again, I thought this was OK, but guess not!