MySQL reference and 1 question

[Kasracer]

Is there a really good MySQL reference? I want to know how to do certain things like adding tables and rows and even databases or even optimizing a current table.

What is the best reference for MySQL that covers practically everything it can do?

Also, 1 question I did have is how would I go about formatting the dates held in MySQL to a way I like it? Can you store it in the database as mm-dd-yyyy or do you have to convert it to such when displaying it and convert it back to yyyy-mm-dd when inserting it into the database?

[visualAd]

The best MySql reference which covers everything to do with MySql is the MySql online reference manual:

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

You can also buy reference books too, like The Definitive Guide To MySql which goes into great detail about optimization and the MySql API.

With regard to storing dates, especially if I am working only with PHP and MySql, I tend to use an unsigned integer column type and use it to store a unix timestamp. You can then use the the PHP date function when recquired to dispaly the date in a friendly format. This has the advantage of not having to convert the date from native MySql format to the fomat used in your programming language and thus saves on resources and also makes for easy date arithmetic as adding one minute to a unix timestamp is equivelent to +=60.

However, the downside of this is that if you wish to use your database with a program written in Visual Basic or a language that has a different date format it will be incompatible and converting the unix timestmap to a format readable by another will require a complex function, which is un-neccessay. For a situation like this, it is best to use MySql's native date/time format and to convert the date to the necessary format one th fly using the date / time functions provided by Mysql.

For example, a query to convert a MySql date into PHP format may look like this:

 SELECT UNIX_TIMESTAMP('2005-02-04');

To convert a MySql date / time column to VB format:

 SELECT DATE_FORMAT('2005-02-04 17:35', '%m/%d/%Y %H:%i');

Like always it depends on your requirements, what method you would use.

[Kasracer]

This doesn't convert them inside the table, right? How exactly do you call the FORMAT function? Inside the query when fetching the row?

[visualAd]

Many people don't use MySql functions because they believe they modify the data stored in the database. However they do not, unless you use them in an UPDATE or REPLACE query. SELECT queries serve one purpose, to extract data from the database and functions can be used to modify, format the extracted data, but this has no effect on the content in the table only the ouput of the query.

The follwoing is a table I use and an example of a SELECT query which uses a function and its output:

content_item
+-----------------+---------------------+------+-----+---------+----------------+
| Field           | Type                | Null | Key | Default | Extra          |
+-----------------+---------------------+------+-----+---------+----------------+
| id              | int(10) unsigned    |      | PRI | NULL    | auto_increment |
| section_id      | int(10) unsigned    | YES  |     | NULL    |                |
| artical_id      | int(10) unsigned    | YES  |     | NULL    |                |
| display_on_home | tinyint(3) unsigned |      |     | 0       |                |
| date_added      | datetime            |      |     | 0       |                |
| title           | varchar(100)        |      |     |         |                |
| content         | text                | YES  |     | NULL    |                |
| side_item       | int(11)             |      |     | 0       |                |
+-----------------+---------------------+------+-----+---------+----------------+

# to use the format function in a select query - note the colun alias
SELECT id, title, DATE_FORMAT(date_added, '%m/%d/%Y %H:%i') date_added FROM content_item;


+----+------------------------+------------------+
| id |          title         |    date_added    |
+----+------------------------+------------------+
| 1  | Content title 1        | 02/12/2005 14:56 |
| 2  | Content title 2        | 01/01/2005 00:56 |
| 3  | Content title 3        | 05/14/2004 11:06 |
+----+------------------------+------------------+

[CornedBee]

Don't forget that UNIX timestamps can't handle dates prior to 1970-01-01, which makes them pretty useless for various jobs, such as storing birthdays.

[visualAd]

Don't forget that UNIX timestamps can't handle dates prior to 1970-01-01, which makes them pretty useless for various jobs, such as storing birthdays.

That is only actually the case when used on Windows.

A UNIX timestamp of 0 is always 01/01/1970 00:00 and on UNIX a timestamp of -2147483648 is 31/12/1901 20:45:54.

If you want to use dates before or after these times in PHP, you'll need define your own date time representation. The very thought makes my skin crawl

[CornedBee]

Using UNIX_TIMESTAMP on date before 1970 on my MySQL running on Linux (as was the PHP) yielded 0.

[visualAd]

That's a bummer. I have just looked at the MySql manual and they say they only support dates down to 1970 I know on Linux the timestamp goes down to 1901.

But you can still use the MySql datetime column type on dates between 1901 and 1970 and convert it using strtotime() function in PHP.

If they used a 64 bit timestamp it would have enabled them to have date range of 584,942,417,355 years. That should see us fit from the begninning of time till doomsday. Why didn't they think of that one

[Kasracer]

$result = mysql_query("SELECT number,title, body, FORMAT(date, '%m/%d/%Y') date FROM blogs"); 

I tried that and I get this error:

Invalid query: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '\'%m/%d/%Y') date FROM blogs' at line 1

What did I screw up?

[visualAd]

If you have field name called date then you'll need to put it in back ticks as it is also a MuSql reservered word:

 $result = mysql_query("SELECT number,title, body, DATE_FORMAT(`date`, '%m/%d/%Y') `date` FROM blogs");

[Kasracer]

Same error when I add those

[visualAd]

I'm sorry - I have given you the wrong function name. It's DATE_FORMAT not FORMAT. Ive changed my other posts too.

Silly me

[Kasracer]

Works perfectly! Thanks!