在mysql表中插入php数组元素时，应该是什么类型的数据?只有字符串?

I have to insert elements of multi-dimensional php array into table mysql but i don't know what kind of method should be used there.

我必须将多维php数组的元素插入到表mysql中，但是我不知道应该使用哪种方法。

First create the table

首先创建表

CREATE TABLE earthquakes (
  milliseconds BIGINT,
  latitude FLOAT,
  longitude FLOAT,
  magnitude FLOAT,
  depth FLOAT,
  source VARCHAR(2),
  region SMALLINT,
  serial VARCHAT(16)
);

Should I add the length of the characters even for the other columns?

我应该为其他列加上字符的长度吗?

Now the code to insert elements:

现在代码插入元素:

$array = array(
 array(12345678901112145,-35.4905,167.7843,7.5,450.4,"E",134,"2017-01-10-134"),
 array(12345678901112145,35.4905,-167.7843,3.2,45.4,"U",13,"2016-01-10-14"),
 array(12345678901112145,-35.4905,-167.7843,10.0,4,"I",1,"2016-01-10-1")
);


$data = array();
foreach($array as $row) {
 $milliseconds = mysql_real_escape_string($row[0]);
 $latitude = mysql_real_escape_string($row[1]);
 $longitude = mysql_real_escape_string($row[2]);
 $magnitude = mysql_real_escape_string($row[3]);
 $depth= mysql_real_escape_string($row[4]);
 $source= mysql_real_escape_string($row[5]);
 $code= mysql_real_escape_string($row[6]);
 $serial= mysql_real_escape_string($row[7]); 


 $data[] = "($milliseconds,$latitude,$longitude,$magnitude,$depth,'$source',$code,'$serial')";
}

$values = implode(',', $data);


$sql = "INSERT INTO earthquakes (milliseconds,latitude,longitude,magnitude,depth,source,region,serial) VALUES $values";

My doubt is if the code is correct but especially if i have to add " " double quotes or single quotes for each element (each value that add to the table).

我的疑问是，如果代码是正确的，特别是如果我必须添加“双引号或每个元素的单引号(每个值都添加到表中)”。

I hope you help me and understand my english :(

我希望你能帮助我并理解我的英语。

UPDATE

更新

this is result if i echo $sql

如果我回显$sql，这就是结果

INSERT INTO earthquakes (milliseconds,latitude,longitude,magnitude,depth,source,region,serial) VALUES (12345678901112145,-35.4905,167.7843,7.5,450.4,'E',134,'2017-01-10-134'),(12345678901112145,35.4905,-167.7843,3.2,45.4,'U',13,'2016-01-10-14'),(12345678901112145,-35.4905,-167.7843,10,4,'I',1,'2016-01-10-1')

1 个解决方案

#1

You should try this :-

你应该试试这个:-

 $array = array(
 array(12345678901112145,-35.4905,"222",7.5,450.4,"E",134,"2017-01-10-134"),
 array(12345678901112145,35.4905,-167.7843,3.2,45.4,"U",13,"2016-01-10-14"),
 array(12345678901112145,-35.4905,-167.7843,10.0,4,"I",1,"2016-01-10-1")
);

//----------------------------
 connection to db is necessary to use mysql_real_escape_string function
//----------------------------

$sql = "INSERT INTO earthquakes (milliseconds,latitude,longitude,magnitude,depth,source,region,serial) VALUES ";

foreach($array as $row) {
     $milliseconds = mysql_real_escape_string($row[0]);
     $latitude = mysql_real_escape_string($row[1]);
     $longitude = mysql_real_escape_string($row[2]);
     $magnitude = mysql_real_escape_string($row[3]);
     $depth= mysql_real_escape_string($row[4]);
     $source= mysql_real_escape_string($row[5]);
     $code= mysql_real_escape_string($row[6]);
     $serial= mysql_real_escape_string($row[7]); 
     $sql .="($milliseconds,$latitude,$longitude,$magnitude,$depth,'$source',$code,'$serial'),";
}
$sql = rtrim($sql, ',');
excute the sql here.

If the column type is varchar then you have to add '' single colon to the values.

如果列类型为varchar，则必须向值添加“单个冒号”。

#1