动态SQL插入查询 [英] dynamic SQL insert query
问题描述
朋友，我正在创建一个php页面，以将csv文件中的数据导入到sql数据库中.
hi friends i'm creating a php page to import the data from a csv file into sql database..
此处数据库表和字段数由用户自己指定.
here database table and number of fields are specified by user itself..
如果用户指定4个字段，则使用如下所示的for循环创建它.
if user specifies 4 fields, then it is created using a for loop as follows..
<?php
include('connect.php');
$name = $_POST['table_name'];
//echo $name;
$create_tab = "CREATE TABLE $name(id varchar(15) PRIMARY KEY)";
if(mysql_query($create_tab,$con))
{
echo "Table <b><i>$name</i></b> created successfully... <br/> <br/>Add columns...";
}
else {
die('Error1'.mysql_error());
}
$field = $_POST['number_of_fields'];
//echo $name.$field;
echo '<form id="form1" name="form1" method="post" action="update-table.php">';
echo "<p>
<label for='tablename'></label>
<input type='hidden' name='tablename' id='tablename' value='$name' size='5'/>
</p>";
echo "<p>
<label for='fields'></label>
<input type='hidden' name='fields' id='fields' value='$field' size='5'/>
</p>";
echo '<table border="1" cellpadding="5" cellspacing="5">';
for ( $i = 1; $i <= $field; $i ++) {
echo '<tr>';
echo '<td>';
echo "<p>
$i
</p>";
echo'</td>';
echo '<td>';
echo "<p>
<label for='textfield$i'></label>
<input type='text' name='field$i' id='textfield$i' />
</p>";
echo'</td>';
echo '<td>';
echo "
<select name='select$i' id='select$i'>
<option value='varchar(200)'>varchar</option>
<option value='int'>int</option>
<option value='float'>float</option>
<option value='date'>date</option>
</select>";
echo '</td>';
echo '</tr>';
}
echo '</table>';
?>
<p>File Location :
<input type="text" name="fileField" id="fileField" />
</p>
<br/>
<INPUT type="image" name="search" src="images/alter.gif" border="0" height="75" width=120">
</form>
然后按如下所示创建和更改表.
then table create and alter as follows..
<?php
include('connect.php');
$field = $_POST[fields];
$name = $_POST[tablename];
for ( $i = 1; $i <= $field; $i++) {
//getting field names
$varname = ($txtfield . $i);
$$varname = $_POST[field.$i];
// echo $$varname;
$fi = $$varname;
//getting field types
$selname = ($selfield . $i);
$$selname = $_POST[select.$i];
$dt = $$varname;
$sql = "ALTER TABLE $name ADD $fi $dt";
if(mysql_query($sql,$con))
{
echo "Field <b><i>$fi</i></b> added successfully...<br/>";
}
else {
die('Error1'.mysql_error());
}
}
?>
如上所述，数据库表和字段均已创建...
as above database table and fields are crated...
我有了如下使用静态变量插入数据的概念.
i got the concept of inserting data using static variables as follows..
<?php
// data import
include('connect.php');
$field = $_POST['fileField']; //file directory
echo "<br/><br/>Import file path: ";
echo $field;
$file = $field;
$lines = file($file);
$firstLine = $lines[0];
foreach ($lines as $line_num => $line) {
if($line_num==0) { continue; } //escape the header column
$arr = explode(",",$line);
$column1= $arr[0];
$column2= $arr[1];
// ' escape character
if (strpos($column2, "'") == FALSE)
{
$column21 = $column2;
}
else{
$column21 = str_replace ("'", "\'", $column2);
}
$column3= $arr[2];
$column4= $arr[3];
//print data from csv
echo "<table border='1' width='800' cellpadding='5' cellspacing='2'>";
echo "<tr>";
echo "<td width='8'>";
echo $column1;
echo "</td>";
echo "<td width='100'>";
echo $column21;
echo "</td>";
echo "<td width='5'>";
echo $column3;
echo "</td>";
echo "<td width='5'>";
echo $column4;
echo "</td>";
echo "</tr>";
echo "</table>";
$import="INSERT into $name (id,name,year1,year2) values(
'$column1','$column21','$column3','$column4')";
mysql_query($import) or die(mysql_error());
}
?>
现在，我的问题是如何使此插入语句动态化，这样它才能根据从表创建和更改查询中for循环获得的数据在插入查询中动态创建字段名称和值?
now, my question is how can i make this insert statement dynamic as such it creates field names and values dynamically inside insert query from the data obtained from for loop in table create and alter query???
推荐答案
如果我正确理解了您的问题，听起来您想将插入内容组合到一个查询中(这是非常明智的性能选择). SQL允许您一次插入多个行，如下所示:
If I understand your question correctly, it sounds like you want to combine your inserts into one query (which is very smart performance wise). SQL allows you to insert multiple rows at once like so:
INSERT INTO table (id, first, last) VALUES(NULL, 'Ryan', 'Silvers'),(NULL, 'Oscar', 'Smith'),(NULL, 'Jessica', 'Owens')
因此，通过创建一个VALUES数组并使用implode将它们连接起来，您可以进行一个查询:
So by using creating an array of VALUES and using implode to join them you can make one query:
//Create rows
foreach($rows as $row) {
$queryRows[] = "(NULL, '".$row['first']."', '".$row['last']."')";
}
//Create query
$query = 'INSERT INTO table (id, first, last) VALUES'.implode(',', $queryRows);
现在，我了解您的真正问题了，我可以为您提供实现此目标所需的基本概述.您需要创建一个允许用户输入将插入表中的数据的表单.
Now that I understand your real question, I can give you a basic overview of what you need to do to achieve this. You need to create a form that allows a user to enter data that will be inserted into the table.
<form method="post" action="process.php">
<input name="field1" />
<!-- and more -->
</form>
然后，您需要编写一个PHP脚本来处理表单提交并将数据插入MySQL:
Then you need to write a PHP script to process the form submission and insert the data into MySQL:
<?php
//Check form submission and validate entries, then...
$stmt = $pdo->prepare('INSERT INTO table (field1) VALUES(:field1)');
$stmt->execute(array(':field1', $_POST['field1']));
?>
这篇关于动态SQL插入查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
