php date input

lerro

I'm trying to use the date function to get it passed to a variable within a function that is in a class. Then a mysql query stores it in the database. The function inserts data into the database as below;

$app_date is declared as a variable in the class

var $app_date;

Then in the function that inserts data into the database, the variable $app_date receives the current date

$app_date = date("Y-M-D");

Then within the same function, i use the variable $app_date to insert the date into the database

$query = "INSERT INTO tablename
(app_date,)
VALUES ('$app_date')";
	mysql_query($query) or die(mysql_error());[/quote]
but then when i check the database, all i see in the field is "0000-00-00"

How can i get the current date to be passed to the given variable in the php function and then stored into Mysql?

bradgrafelman

lerro wrote:
How can i get the current date to be passed to the given variable in the php function and then stored into Mysql?

Well for one thing, you'd need to use the correct date format for MySQL's DATE format (hint: it's not "Y-M-D").

But more importantly, why use PHP at all? MySQL is more than capable of figuring out what the current date is.

dagon

$app_date, is the current date, so why not use the mysql function CURDATE(), asuming the php and mysql server are in the same time zone

lerro

but how do i get mysql to record that date in the field?

This is because, when inputting a new person's details into the table and i need to record the date that each record is added.

Is there a setting in mysql where the date field is auto-filled whenever a new record is added?

dagon

$query = "INSERT INTO tablename
(app_date)
VALUES (CURDATE())";

you can default to 'CURRENT_TIMESTAMP' then it would be filled in automatically, as long as its a date field.

bradgrafelman

For MySQL, take a look at using a TIMESTAMP column type. You can always retrieve the column as just a date if you don't need the time portion, but using a TIMESTAMP column allows you to omit the column in your INSERT queries and allow MySQL to automatically populate the column with the current date and time.

EDIT: Note that the CURRENT_TIMESTAMP flag dagon and I are referring to only works on a TIMESTAMP column type.

lerro

Thanks, curdate() worked. I will also consider using timestamp in the future.

I have one more question.

I need to generate a unique id for a record and insert that into mysql. I do the following

I first have a function to generate the ID using current date combined with a random number

function generate_id(){
	$genDate = date("Ymd");
	$randNum = rand(10000000,99999999);

	$uniqueid = "$genDate"."$randNum";

	$uniqueid = str_replace(".", "", "$uniqueid");

	return $uniqueid;
}[/QUOTE]
Then i call up the generate function within the insert function in the same class and then insert it into mysql
function insert(){
	//$app_no=$this->generate_id();
$query = "INSERT INTO tablename (app_no) VALUES ('$app_num')";

mysql_query($query) or die('Error, insert application failed'. mysql_error());
The problems i then have are that

1) an id is generated and stored. But then the next id generated for the next record is not unique when it's generated and i'm prompted with error alert of duplicate primary key

2) The date part is not passed on. While testing with echo, the output shows the date then the random number, but when input into mysql, the number is different, e.g. 2147483647

3) how can i have a unique number than never duplicates?

Could you please assist me how to resolve this?

johanafm

First off, if you are using any kind of date data type for the field, you can't append an arbitrary number to the date. Either you will get an invalid date, or you will get some other date. For this to work, you'd need a string data type.

Had you wanted to number entries for each day from 1 to number of entries that day, it would have made sense to use this

CREATE TABLE tbl (
	id		INT UNSIGNED,
	app_date	DATE,
PRIMARY KEY(id, app_date)
)

INSERT INTO tbl
SELECT MAX(id) + 1, 'some date' FROM tbl WHERE dttime='same date as before...'

But if you have no real world information to give you a unique key, why complicate things?

CREATE TABLE tbl (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	app_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
INSERT INTO tbl VALUES(null, null)

Now, you never have to include the id or app_date when inserting data, unless of course you want to specify some other time than right now. id keeps incrementing, thus giving each row a unique id (not tied to the day in any way). app_date sets the time of insertion.

Also, with your approach to MUST handle id collissions (or accept that your application will fail sooner or later for no outwardly apparent reason). It does not matter how unlikely it is that the same number is generated more than one time during the same day, it can still happen. It can still happen 10 times in a row.

lerro

Thanks for that. Using a joint id is a possibility.

But what about when i need to generate e.g. an account number and each customer is supposed to have a unique 8 digit account number in the Account table. How do i go about doing that?
As the account number would have to be stored in one filed, not as two fields that form a joint id.

johanafm

My recommendation was however to not use a two field PK, but just the integer field. Assuming you use MySQL, the non standard BIGINT UNSIGNED can contain up to 18 billion billion or something like that, while INT UNSIGNED limits you to 4 billion.

As for the account number, that depends on how to use it, what information it carries (if any, other than being uniqie). For example

CREATE TABLE account(
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
) AUTO_INCREMENT = 10000000

Now, you use an integer, you get an automatic new unique number each time, and it has 8 digits, at least until you run out of numbers. Creating an INSERT trigger to check that it contains 8 digits may be an idea.

But if you have requirements such that specific digits' values are used for specific account types, or that your range is restricted etc, you should probably go with a string where you set the specific digits to match your needs/options, and then fill in the rest.

Also, if you are not allowed to use sequential incrementation of account numbers, you will have to come up with a way of assigning random account numbers, and care needs to be take. With your 8 digit account number you have 99999999 - 10000000 + 1 = 90000000 possible account numbers. It seems a lot, but your birthday bound (first expected collision) is no more than about 11000 account numbers using an ordinary random generator.

As your number of used account numbers gets to some significant fraction of all possible numbers, you may have to devise a strategy to deal with the problem of increasing collision. And once you get to this point, it will be harder to deal with the problem than doing so up front. I've never had to care about something like this, so it's possible there are better options available to you, but one strategy would be to keep all possible account numbers pre-generated, and either delete them or marked them as used as you create new accounts.
And if you need to deal with this issue combined with the things I mentioned before, such as numbers starting with 5 being restricted to individuals, 6 for corporations or whatever they may be, you could stick your numbers in different tables matching these needs.
When you need an account number, you can select one randomly, and do not forget to aqcuire an exclusive read lock on the row, then delete the row once you've read the value. Otherwise you once again risk dplicate key inserts.

lerro

Okay,

given that i already have the table created with some test data values (which i can delete), how do i go about setting the id to autoincrement starting with 8 digits 10000000 ?

Or do i have to delete the table and create it again with the autoincrement rule?