php - Can a variable be recognized as an int or float or time if its stored as a varchar
Get the solution ↓↓↓
Solution:
In my opinion you could model the field as FLOAT except if you absolutely need to know about the type of variable stored. Time can be converted to an integer value by converting to timestamp. Integers are a subset of the real (floating point) numbers set actually so I guess that way you have everything covered. Floating point arithmetic can cause some issues with precision and equality testing though so be careful!
Answer
Solution:
You can use CAST and CONVERT functions to convert the string datatype into another MySQL datatype such as INT, FLOAT, DECIMAL, DATE, DATETIME etc.
There are a few issues. How do you know what datatype is stored in a row? MySQL does have RegExp support but I do not recommend using it in your WHERE clauses. Store the datatype in another column.
Also, using cast functions in the where clause of your query will make them run slow. If you need to search upon/sort by the data you should use proper datatypes. Perhaps you should add one column for each datatype; and for each row, populate only the corresponding column.
Answer
Solution:
mysql will happily convert text to numbers of the appropriate type if you perform a mathematical operation on it. However, it will also convert non-numeric text to zero and perform the same operation on it, so you need to check that you're only pulling fields of the appropriate type beforehand.
This forum post shows how to add a regular expression condition to your query to ensure that you're only pulling fields with numeric data in them. However, I think it's probably wiser to use a separate column to indicate what type of score each record is, and use that to retrieve the appropriate ones.
I don't know how to convert text to a date (putting it throughdate() doesn't work). However, note that the mysql date format (2012-05-08 11:20:23) has the date elements in order of descending significance. If you just want to find the highest / lowest date, or sort by date, treating them as strings will work fine.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: you must enable the openssl extension in your php.ini to load information from https://repo.packagist.org
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.