sql query for date and IP address

[ashokvas]

I have written a sql query for inserting the current date and the IP address of the user when he is filling up a registration form,as follows

$sql = sprintf("INSERT INTO %s (id, login, pw, real_name, email,date_join,ip_addrr, active) VALUES (NULL, %s, %s, %s, %s,%s,%s, 'n')",
$this->table_name,
$this->ins_string($first_login),
$this->ins_string(md5($first_password)),
$this->ins_string($first_name),

$this->ins_string($this->user_email),date("Y-m-d"),$REMOTE_ADDR);



When I enter it the input to the date field is 1990 and for the IP address it says undefined variable.

Can anyone pls help!!!

Ashokvas

[NogDog]

Probably just need to single-quote the %s place-holder for the date, since it's a string. Even simpler(?) if you just want today's date would be to let MySQL do it itself using its CURDATE() function. As far as the IP address thing, how are you populating $REMOTE_ADDR? Does it actually ever get set? If using the PHP server global array, you may to to reference it explicitly, depending on your environment (explicit reference should always work, so it's best to always use it):
$_SERVER['REMOTE_ADDR']
Note, you'll also need to single-quote this in the query, as it's a string, not a number.

[ashokvas]

Originally posted by NogDog
Probably just need to single-quote the %s place-holder for the date, since it's a string. Even simpler(?) if you just want today's date would be to let MySQL do it itself using its CURDATE() function. As far as the IP address thing, how are you populating $REMOTE_ADDR? Does it actually ever get set? If using the PHP server global array, you may to to reference it explicitly, depending on your environment (explicit reference should always work, so it's best to always use it):
$_SERVER['REMOTE_ADDR']
Note, you'll also need to single-quote this in the query, as it's a string, not a number.

Thanks for the suggestions.How does one use CURDATE() directly in the query?Could youo pls write it out as I am a newbie.
The $REMOTE_ADDR has been directly used.I have not populated it outside.I was understanding that it will directly give the IP address.

Here also could you pls show me how to write the query.

Thanks

Ashokvas

[NogDog]

$sql = sprintf("INSERT INTO %s ".
"(id, login, pw, real_name, email, date_join, ip_addrr, active) ".
"VALUES (NULL, '%s', '%s', '%s', '%s', CURDATE(), '%s', 'n')",
$this->table_name,
$this->ins_string($first_login),
$this->ins_string(md5($first_password)),
$this->ins_string($first_name),
$this->ins_string($this->user_email),
$_SERVER['REMOTE_ADDR']);

[ashokvas]

Thanks for your help.I shall try it and let you know.
I really appreciate your help.In fact I have also been looking at your other postings and I have really learned quite a bit.


Ashokvas

[NogDog]

You're welcom. Luckily it's a slow day at work. :)

[ashokvas]

Dear Nog Dog,

The query worked fine,except I had to remove the single quotes from %s as follows

"(id, login, pw, real_name, email, date_join, ip_addr, active) ".
"VALUES (NULL, %s, %s, %s, %s, CURDATE(), '%s', 'n')",

Thanks once again

Ashokvas

[ashokvas]

Dear NogDog,

In fact I am quite confused about using single quotes and double quotes in PHP and MySql.Is there any place where I can read about this and clear it once and for all.Right now I work on trial and error and it is better to understand properly.
In fact can you suggest what all I should read( on the net) to learn more about PHP,Mysql.

Ashokvas

[NogDog]

Originally posted by ashokvas
Dear NogDog,

In fact I am quite confused about using single quotes and double quotes in PHP and MySql.Is there any place where I can read about this and clear it once and for all.Right now I work on trial and error and it is better to understand properly.
In fact can you suggest what all I should read( on the net) to learn more about PHP,Mysql.

Ashokvas

The sources I always use:

http://www.php.net/manual/en/language.types.string.php

http://dev.mysql.com/doc/mysql/en/String_syntax.html

[ashokvas]

Thanks a lot

Ashokvas

[Stephen Philbin]

With the date thing in mysql, like Mr Dog here suggested, let the MySQL server do it for you. My prefered way of doing it is to use now() for the date or time or both. A handy little behaviour in MySQL is that if you use now() in a query to fill a DATETIME column, it will fill out the whole thing of date and time, but if you have a column just for time, you can still use now() and MySQL will simply chop off the date for you and insert the time alone. The same is also true on a date column too, it'll just see the column doesn't want the time bit of now() and chop it off. Ity's really handy if you're using non-specialised date handling. :D

There's still one or two pitfalls from using php to interface with MySQL though. The biggest one I've found so far is transactions. You can't use:

mysql_query("START TRANSACTION;First query;Seconds query;third query;COMMIT;");

in the way you would expect. I was going crazy when trying to do a full transaction in one mysql_query(""); It would work no problems at all on the command line directly on the MySQL server, but as soon as I tried it with php, MySQL said I had a syntax error. After a while I realised that php must assume that anything you supply after the first semi-colon (eg. START TRANSACTION;) is the optional connection paramater that the fuction allows, so as far as I can see, it sends the remainder of your query as a connection request or something, so mysql wonders what the hell php thinks it's playing at and sends back an error. Only way round it I found so far is:

mysql_query("START TRANSACTION;");
mysql_query("Pointless new line;");
mysql_query("To use another pointless new function call;");
mysql_query("One after the other;");
mysql_query("Over and over;");
mysql_query("Which wastes a lot of time;");
mysql_query("and I should imagine slows the page down too.;");
mysql_query("COMMIT;");

As you can see that's a lot of extra crap you shouldn't need to type or use. I should imagine any speed gains made from using transactions is instantly lost thatks to all the pointless extra function calls in php.

[ashokvas]

Thanks Mr Herer for your suggestion.

But the following part you have mentioned

"A handy little behaviour in MySQL is that if you use now() in a query to fill a DATETIME column, it will fill out the whole thing of date and time, but if you have a column just for time, you can still use now() and MySQL will simply chop off the date for you and insert the time alone. The same is also true on a date column too, it'll just see the column doesn't want the time bit of now() and chop it off. "

is not clear to me.How does Mysql chop off the time part and insert only the date part?Do we have to set the column type to 'date' and then use the query?

Can you please write out what has to be done?


Ashokvas

[Stephen Philbin]

If your column type is DATE then now() will just fill the column with the date. If the column is DATETIME then now() will fill the whole thing out. If your column type is TIME, then now() will fill only the time and discard the date section. That's all there is to it really. That's the beauty of it. You don't need to set up or do anything special. Just go right ahead and throw it in! :D