Posted on by

Create MYSQL TABLE using PHP ARRAY

As a web developer I hate when I have to create mysql table during middle of the project. Most of the clients do not provide complete project scope due to that some of the scenarios and scope remain uncovered during database design.

In my recent project I have faced this type of issue. At the eleventh hour client came to me and said please create another view for CSV upload and link csv to another module. First I really get angry but you know CLIENT IS ALWAYS RIGHT.


One another thing he told me that CSV has 217 fields. Yes! Only 217 fields which means I have to create 217 columns in mysql table. I could not use JSON data type for this because if I would, then i had to alter my most of the code which I couldn’t.

I came up with an idea, Why not create an associative array using first row (CSV Column) and second row (for column values) and then write a PHP code for Mysql table and execute that. So i do not need to create mysql table manually.

Idea works!

So in this post I am going to share that code with you. No no do not panic i will not list all 217 fields 🙂

Convert PHP Array to Mysql Table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
<?php
 
 
$array = array(
"First_Name" => "Ahsan",
"Last_Name" => "Zameer",
"Phone" => "+92123456789",
"Email" => "ahsan@info.com",
"Address" => "abc house Karachi, Sindh, Pakistan",
"Zip" => 11111,
"City" => "Karachi",
"State" => "Sindh",
"Country" => "Pakistan",
"Marital_Status" => "Married",
"DOB" => "1988-12-250",
"Height"=> 5.10,
"Is_Active" => true
);
 
 
$createTableStatement = "CREATE TABLE `my_array_table`";
$createTableStatement .= '(';
$createTableStatement .= '`id` INT NOT NULL AUTO_INCREMENT,';
 
foreach($array as $dataKey => $dataValues)
{
$getDataType = gettype($dataValues);
if($getDataType == 'integer')
{
$createTableStatement .= '`'.$dataKey.'` int(11) DEFAULT NULL, ';
}
elseif($getDataType == 'double')
{
$createTableStatement .= '`'.$dataKey.'` float DEFAULT NULL, ';
}
elseif($getDataType == 'boolean')
{
$createTableStatement .= '`'.$dataKey.'` tinyint(2) DEFAULT NULL, ';
}
else
{
$createTableStatement .= '`'.$dataKey.'` varchar(255) DEFAULT NULL, ';
}
}
 
$createTableStatement .= 'PRIMARY KEY (`id`)';
$createTableStatement .= ')';
 
$createTableStatement .= "COLLATE='latin1_swedish_ci' ENGINE=InnoDB";
 
echo $createTableStatement;
 
?>
 

Output:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE TABLE `my_array_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `First_Name` VARCHAR (255) DEFAULT NULL,
  `Last_Name` VARCHAR (255) DEFAULT NULL,
  `Phone` VARCHAR (255) DEFAULT NULL,
  `Email` VARCHAR (255) DEFAULT NULL,
  `Address` VARCHAR (255) DEFAULT NULL,
  `Zip` INT (11) DEFAULT NULL,
  `City` VARCHAR (255) DEFAULT NULL,
  `State` VARCHAR (255) DEFAULT NULL,
  `Country` VARCHAR (255) DEFAULT NULL,
  `Marital_Status` VARCHAR (255) DEFAULT NULL,
  `DOB` VARCHAR (255) DEFAULT NULL,
  `Height` FLOAT DEFAULT NULL,
  `Is_Active` TINYINT (2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) COLLATE = 'latin1_swedish_ci' ENGINE = INNODB
 

Just execute above code in the browser and table code will print. Copy and paste that code to phpmyadmin or anyother mysql client. Or if you want to execute create table statement so you need to create database connection and pass $createTableStatement variable mysqli_query()

Also read:

 

More from WDB24.com

Posted in PHP

Published by Ahsan Zameer

I am a web developer and love to search new stuff on web. Trying different approaches and ideas to make web developing more interesting and enjoyable.

Leave a Reply

Your email address will not be published. Required fields are marked *