最受欢迎

PHP/MySQL:在MySQL查询中插入逗号以分隔从html表单中提取的变量

PHP/MySQL: Inserting commas to separate variables pulled from html form in to MySQL query

本文关键字：MySQL html 分隔 提取 变量 表单 查询 PHP 插入 | 更新日期: 2023-09-27

我目前正在编写一个脚本来随机化不同的输入值。以下是php脚本的主要部分：

$alive='';
if(isset($_GET['alive'])) {
$alive=$_GET['alive'];
}
$injured='';
if(isset($_GET['injured'])) {
$injured=$_GET['injured'];
}
$deceased='';
if(isset($_GET['deceased'])) {
$deceased=$_GET['deceased'];
}
$unkstat='';
if(isset($_GET['unkstat'])) {
$unkstat=$_GET['unkstat'];
}
$found='';
if(isset($_GET['found'])) {
$found=$_GET['found'];
}
$missing='';
if(isset($_GET['missing'])) {
$missing=$_GET['missing'];
}
$youth='';
if(isset($_GET['youth'])) {
$youth=$_GET['youth'];
}
$adult='';
if(isset($_GET['adult'])) {
$adult=$_GET['adult'];
}
$unkage='';
if(isset($_GET['unkage'])) {
$unkage=$_GET['unkage'];
}
$incidentid='';
if(isset($_GET['incidentid'])) {
$incidentid=$_GET['incidentid'];
}
$limit='';
if(isset($_GET['limit'])) {
$limit=$_GET['limit'];
}
mysql_connect ("$host", "$username", "$password") or die ("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql="INSERT INTO $tbl_name (given_name, family_name, gender, status, age, incidentid)
select First10.$gender, Last10.family_name,
(SELECT $gender from gender) AS Gender,
(SELECT * FROM status WHERE status IN ($alive $injured $deceased $unkstat $found $missing)) AS Status,
(SELECT * FROM age WHERE age IN ($youth $adult $unkage)) AS Age,
(SELECT $incidentid from incidentid) AS Incident_ID
from ( select fn.$gender, @fns := @fns + 1 as Sequence
from ( select $gender from fnames where $gender IS NOT NULL order by rand() limit 100) fn,
(select @fns := 0 ) vars ) First10
JOIN
( select ln.family_name, @lns := @lns + 1 as Sequence
from ( select family_name from lastnames order by rand() limit 100 ) ln,
(select @lns := 0 ) vars ) Last10
ON First10.Sequence = Last10.Sequence";

html在这里：

<p>
Status: <BR>
<INPUT TYPE=CHECKBOX name="alive" value="'alive'">Alive and Well<BR>
<INPUT TYPE=CHECKBOX name="injured" value="'injured'">Injured<BR>
<INPUT TYPE=CHECKBOX name="deceased" value="'deceased'">Deceased<BR>
<INPUT TYPE=CHECKBOX name="unkstat" value="'unknown'">Unknown<BR>
<INPUT TYPE=CHECKBOX name="found" value="'found'">Found<BR>
<INPUT TYPE=CHECKBOX name="missing" value="'missing'">Missing<BR>
</p>
<p>
Age: <BR>
<INPUT TYPE=CHECKBOX name="youth" value="'youth'">Youth (0-17)<BR>
<INPUT TYPE=CHECKBOX name="adult" value="'adult'">Adult (18+)<BR>
<INPUT TYPE=CHECKBOX name="unkage" value="'unknown'">Unknown<BR>
</p

我要做的是从复选框中提取值，并将选中的值插入到mysql查询中。然后，查询将在预制表中搜索这些值，并将它们插入到一个单独的表中。我的问题是，每当我引入变量时，我都会收到一个语法错误，通常会导致以下结果：

'SELECT*FROM status WHERE status IN('活着'受伤'死亡'(AS状态‘

括号中的值似乎需要逗号来分隔，但我想不出，也找不到有效的方法。请告知。你们太棒了：(

创建一个数组。使用array_push将每个设置值推送到数组。然后使用implode(",",$array)创建逗号分隔的值变量。在您的IN语句中使用

与PeeHaa的回应结合的例子。

$vars = array('alive', 'injured', 'deceased', 'unkstat', 'found', 'etc', ); 
$result = array(); 
foreach($vars as $var) { 
   if (isset($_GET[$var])) {
     array_push($_GET[$var], $result);
   }
}
$result = implode(",",$result);

我可以建议一个稍微好一点的解决方案吗通过在名称末尾添加方括号(例如status[](，可以创建向PHP返回array的表单。使用数组将使PHP代码更短，因为您不需要单独查找每个变量。例如：

HTML:

<p>
Status: <BR>
<INPUT TYPE=CHECKBOX name="status[]" value="alive">Alive and Well<BR>
<INPUT TYPE=CHECKBOX name="status[]" value="injured">Injured<BR>
<INPUT TYPE=CHECKBOX name="status[]" value="deceased">Deceased<BR>
<INPUT TYPE=CHECKBOX name="status[]" value="unknown">Unknown<BR>
<INPUT TYPE=CHECKBOX name="status[]" value="found">Found<BR>
<INPUT TYPE=CHECKBOX name="status[]" value="missing">Missing<BR>
</p>

PHP:

$sql = 'SELECT * FROM status WHERE status IN ("' . implode('","', array_map('mysql_real_escape_string', $_REQUEST['status'])) . '")';

implode函数将把数据连接在一起。我还添加了一个array_map调用，它将通过mysql_real_escape_string运行每个数组值以转义该值，以防万一。

$in_clause = '';
$in_clause_t = '';
$alive='';
if(isset($_GET['alive'])) {
    $alive=$_GET['alive'];
    $in_clause .= $alive . ", ";
}
$injured='';
if(isset($_GET['injured'])) {
    $injured=$_GET['injured'];
    $in_clause .= $injured . ", ";
}
$deceased='';
if(isset($_GET['deceased'])) {
    $deceased=$_GET['deceased'];
    $in_clause .= $deceased . ", ";
}
$unkstat='';
if(isset($_GET['unkstat'])) {
    $unkstat=$_GET['unkstat'];
    $in_clause .= $unkstat . ", ";
}
$found='';
if(isset($_GET['found'])) {
    $found=$_GET['found'];
    $in_clause .= $found . ", ";
}
$missing='';
if(isset($_GET['missing'])) {
    $missing=$_GET['missing'];
    $in_clause .= $missing . ", ";
}
$youth='';
if(isset($_GET['youth'])) {
    $youth=$_GET['youth'];
    $in_clause_t .= $youth . ", ";
}
$adult='';
if(isset($_GET['adult'])) {
    $adult=$_GET['adult'];
    $in_clause_t .= $adult . ", ";
}
$unkage='';
if(isset($_GET['unkage'])) {
    $unkage=$_GET['unkage'];
    $in_clause_t .= $unkage . ", ";
}
$incidentid='';
if(isset($_GET['incidentid'])) {
    $incidentid=$_GET['incidentid'];
}
$limit='';
if(isset($_GET['limit'])) {
    $limit=$_GET['limit'];
}
if (strlen($in_clause) > 0) {
    $in_clause = substr($in_clause, 0, (strlen($in_clause) - 2));
}
if (strlen($in_clause_t) > 0) {
    $in_clause_t = substr($in_clause_t, 0, (strlen($in_clause_t) - 2));
}
mysql_connect ("$host", "$username", "$password") or die ("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql="INSERT INTO $tbl_name (given_name, family_name, gender, status, age, incidentid)
select First10.$gender, Last10.family_name,
(SELECT $gender from gender) AS Gender,
(SELECT * FROM status WHERE status IN (" . $in_clause . ") AS Status,
(SELECT * FROM age WHERE age IN (" . $in_clause_t . ")) AS Age,
(SELECT $incidentid from incidentid) AS Incident_ID
from ( select fn.$gender, @fns := @fns + 1 as Sequence
from ( select $gender from fnames where $gender IS NOT NULL order by rand() limit 100) fn,
(select @fns := 0 ) vars ) First10
JOIN
( select ln.family_name, @lns := @lns + 1 as Sequence
from ( select family_name from lastnames order by rand() limit 100 ) ln,
(select @lns := 0 ) vars ) Last10
ON First10.Sequence = Last10.Sequence";

这样就可以了。

使用当前设置，在问题语句的每个变量后面添加逗号：

SELECT * FROM status WHERE status IN ($alive, $injured, $deceased, $unkstat, $found, $missing)) AS Status

有点糟糕，但这可能奏效。。。。

基本上形成所有$_POST变量的逗号分隔列表。。。

<form action="" method="post">
<p>
Status: <BR>
<INPUT TYPE=CHECKBOX name="alive" value="'alive'">Alive and Well<BR>
<INPUT TYPE=CHECKBOX name="injured" value="'injured'">Injured<BR>
<INPUT TYPE=CHECKBOX name="deceased" value="'deceased'">Deceased<BR>
<INPUT TYPE=CHECKBOX name="unkstat" value="'unknown'">Unknown<BR>
<INPUT TYPE=CHECKBOX name="found" value="'found'">Found<BR>
<INPUT TYPE=CHECKBOX name="missing" value="'missing'">Missing<BR>
</p>
<p>
Age: <BR>
<INPUT TYPE=CHECKBOX name="youth" value="'youth'">Youth (0-17)<BR>
<INPUT TYPE=CHECKBOX name="adult" value="'adult'">Adult (18+)<BR>
<INPUT TYPE=CHECKBOX name="unkage" value="'unknown'">Unknown<BR>
</p>
<input type="submit">

<?php 
if(isset($_POST)) {
    print_r($_POST);
    $str = implode(",",$_POST);
    echo $str;
}
?>
相关文章：
最新更新
www.56WenDa.com 2012-2023 | php问答网 | php学习