how to determine the correct sql data type for each element of an array in PHP

Question

I have written a PHP class that gets the headers from a .xls spreadsheet and creates a table with those headers as column names.

It also gets each row of data from the spreadsheet and places them into an array.

What I would then like to do, is determine the best data type for each column in the row. It's mostly going to be text but there will be numbers in there, say for example £1,000 this would need to be saved as 1000 and be a int rather than a string.

It needs to be done dynamically as each spreadsheet has different column names and data in different orders.

I don't really know how to go about this, I was thinking maybe a for each loop and preg_match?

Any ideas are very much appreciated.

Won't variable handling functions be enough?

Shef
– Shef

2011-09-17 16:34:45 +00:00
Commented Sep 17, 2011 at 16:34 — Shef
– Shef, Commented Sep 17, 2011 at 16:34

Timur · Accepted Answer · 2011-09-17 16:34:32Z

1

I think that you need to check all data in column to determine if there is some not numeric values ( http://ru.php.net/manual/en/function.is-numeric.php ). If there is no not numeric data, you may use INT/TINYINT/MEDIUMINT type with appropriate length. If there is not only numeric data, you may use CHAR/VARCHAR/BLOG/TEXT with appropriate length.

answered Sep 17, 2011 at 16:34

Timur

6,7381 gold badge30 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jdias · Accepted Answer · 2011-09-17 16:41:19Z

0

The way I would go about it is to define how precise I wanted to be. For example, if I find a column with 1s and 0s, will I define it as binary or should I define it as integer just in case there will be numbers different than 0 and 1 in the future.

Also are you going to parse all the rows of the spreadsheet or only a few rows at the top before deciding which data type to use? In the above example, you may have 0s and 1s at the top of the spreadsheet, but find other numbers closer to the bottom. If you decide to review only the top rows, you may want to be less strict on the data type. So if you find only 0s and 1s, you may decide to define the field as integer, not binary. This would reduce the chances for errors when importing the data.

You could use a logic somewhat like this:

for each row (and you can decide if you want to check all the rows or just a few)
if is_int() -> data field integer;
if is_float() -> data field is float;
if is_string()
    if it is a date & time -> data field is datetime;
    if it is a date without time -> data field is date;
    else -> data field is varchar.

I hope this helps. Good luck.

answered Sep 17, 2011 at 16:41

jdias

5,7344 gold badges24 silver badges28 bronze badges

1 Comment

Max Rose-Collins Over a year ago

that was a great help! definitely pointed me in the right direction, thanks a lot :)

Collectives™ on Stack Overflow

how to determine the correct sql data type for each element of an array in PHP

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related