Can You Just Confirm Please?

[Byronwells]

Alright guys

I am just wondering can I create on field on the db, and allow number and text information to be stored in that field?

Or does it have to be seperate....?

[angst]

yes thats correct, you can create a field and store data in the fields column.

[bdl]

If you're asking "do I have to have separate fields for numeric and string data", no. You can have a VARCHAR or CHAR type field and store any string in there. Numeric values count as a string, as long as they're wrapping in 'quotes'.

These can be stored in a VARCHAR or CHAR field:
"0.00"
"1234A"
"Hello, World!"
"0x01"

Now, an INT or DECIMAL type field is another matter. They expect only numeric data, and the data does not have to be wrapped in quotes, e.g.

100
501.95

Just remember that a string can be a numeric value, but it's still not an integer as far as MySQL is concerned. So any actions that you would take such as SUM() or AVG() on a numeric field will not apply (or will not apply properly) on a string type field.

Please read the MySQL manual section on data types (http://dev.mysql.com/doc/refman/5.1/en/data-types.html).

[Byronwells]

If you're asking "do I have to have separate fields for numeric and string data", no. You can have a VARCHAR or CHAR type field and store any string in there. Numeric values count as a string, as long as they're wrapping in 'quotes'.

These can be stored in a VARCHAR or CHAR field:
"0.00"
"1234A"
"Hello, World!"
"0x01"

Now, an INT or DECIMAL type field is another matter. They expect only numeric data, and the data does not have to be wrapped in quotes, e.g.

100
501.95

Just remember that a string can be a numeric value, but it's still not an integer as far as MySQL is concerned. So any actions that you would take such as SUM() or AVG() on a numeric field will not apply (or will not apply properly) on a string type field.

Please read the MySQL manual section on data types (http://dev.mysql.com/doc/refman/5.1/en/data-types.html).

Alright

Thanks for that.. At the moment I am currently using a javascript datepicker to choose the date, which is then displayed in nurmerical data in a text box, which is then link to a date field on the db..

I have heard from someone that I can use the datetype for the date field as date and not var or char. And then write some php code, that allows me to convert the numerical month data, into lets, and then get it to output like this dec/03/09 . Is that possible??

If so any ideas how to go about it please??

[bdl]

Thanks for that.. At the moment I am currently using a javascript datepicker to choose the date, which is then displayed in nurmerical data in a text box, which is then link to a date field on the db..

I have heard from someone that I can use the datetype for the date field as date and not var or char. And then write some php code, that allows me to convert the numerical month data, into lets, and then get it to output like this dec/03/09 . Is that possible??

Ok. Just remember, anything coming from a request to the PHP script will always be a string, even numeric values. A date value especially will always be a string regardless of whether you're in PHP or MySQL, so always wrap it in quotes.

Using the MySQL DATE or DATETIME data types is always wise when dealing with date values, and there are many built-in functions (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html) to manipulate and format date values from those fields. NEVER use a CHAR / VARCHAR type field to store a date. Depending on your application, you might consider using an INT type field to store a UNIX TIMESTAMP (UNIX EPOCH) integer value, but going with the DATETIME type field is usually the most flexible.

MySQL can innately format any date value. So if you have a date like '2009-12-03', you can always format that on-the-fly with MySQL's DATE_FORMAT() function (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format) into any format you like, such as "dec/03/09". PHP can also do this if you like.

[Old Pedant]

Everything BDL says is true, but watch out for one gotcha:

Although MySQL can *output* a DateTime value as you wish, the only datetime format that it understands "natively" is 'YYYY-MM-DD hh:mm:ss'. Yes, you can convert from other formats, but it's usually a lot of work and it slows MySQL down. You should try to present all dates and times to MySQL in that format.

[bdl]

@Old Pedant> That's a good point; you always want to store and work with any date values in that format ("YYYY-MM-DD"). I disagree, however, that calling DATE_FORMAT() causes any real, marked slowdown. It's a built-in function, that's what it's for!

[Old Pedant]

No no...I meant converting the *other* way...from a non-standard format into MySQL standard...for input.

Now, *all* functions take *some* time. But since it would just be a matter of where the time is used--in the query or in some PHP code that had to do the formatting--then it's clear that the output function is worth using.