How to display date and price in MySQL database and display it in various formats in PHP

Question

I am new to php / mysql, so I insert dates in any format that usually appears on my pages, for example:

Saturday, June 19, 2010 

directly to the database in plain text. But I saw people enter it in some other way, and then display the same entry in different formats using some php function. How to do this so that I can have one record in the database and display it in various formats, such as:

 6/19/2010 June 19, 2010 Saturday, June 19, 2010 

etc...

Second part of the question

How to do the same, but with money?

Answer 1

You must store the data as MYSQL datetime feild. Then use php to format the data as you want it.

As for the price, I recommend that you store it in US dollars. Thus, you can easily convert it to other currencies.

Answer 2

I myself store it as a unix timestamp.

The time () function returns a unix timestamp.

And check the date () function here:

http://php.net/manual/en/function.date.php

Two arguments are required: the format you want to display, and the unix timestamp.

Answer 3

How to process, save, change php + mysql dates

For dates, just save them as DATETIME . It is easy to read and easy to use with PHP. You could go with a clean TIMESTAMP , but I would not recommend it.

DATETIME range ...
'1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

TIMESTAMP range ...
'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'

Not only that, but you also have many MYSQL built-in functions for working with DATETIME, for example INTERVAL , which allows you to do great things.

 // easy pull all comments values from within a range SELECT * FROM your_table WHERE your_date_field = DATE_SUB('2001-01-01 12:12:12' + INTERVAL 15 DAYS; 

If you need to work with timestamps in PHP, you can pull them in real time in SQL as follows:

 // converts a DATETIME field real time to timestamps SELECT UNIX_TIMESTAMP(your_date_field) FROM your_table (...) 

And when you pull them out, just format them as you like using strftime . An example is below.

 // pull a date from MySQL and format them to a custom format // %A -> full weekday | %B -> full month name | %d -> month day | %Y - year 1999 $sql = "SELECT title, name, UNIX_TIMESTAMP(date) as timestamp FROM your_table WHERE your_limits"; $result = mysql_fetch_assoc(mysql_query($sql)); echo strftime("%A, %B %d, %Y", $result['timestamp']); 

How to store currencies in MySQL

I would always keep them in their loaded value. Only when there is a transaction or change, you should convert from one currency to another. The rest retain values ​​when they were downloaded at the original price.

 | 135.23 | USD | | 200.35 | EUR | | 34.00 | GBP | 

When displaying currencies, you can do the conversion in real time. Just keep a separate table with the current exchange date and do the mathematical time in real time. It would also be a good indicator for your conversion rates.

Tell the user above with the above account to get your currency in EUR.

 135.23 USD = 109.39 EUR (1 USD = 0.80893059 EUR) 30 GBP = 35.90 EUR (1 GBP = 1.19681281 EUR) 200.35 EUR ----------------------- total 345.64 EUR