当前位置： 文江博客话题详情

PHP/MySQL：插入逗号来分隔从 html 表单提取到 MySQL 查询的变量

发布于 2024-11-19 14:48:47 字数 2835 浏览 0 评论 0原文

我目前正在编写一个脚本来随机化不同的输入值。这是 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状态'

看来括号内的值需要用逗号分隔它们，但我想不出，也找不到有效地做到这一点的方法。请指教。你们太棒了:)

原文

i'm currently working on a script to randomize different values of input. Here is the main part of the php script:

$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";

And the html is here:

<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

What I am trying to do is pull the values from the checkboxes and insert the checked values into a mysql query. The query will then search for these values in premade tables and insert them into a separate table. My problem here is that whenever I pull in the variables, I receive a syntax error that usually results in something like this:

'SELECT * FROM status WHERE status IN ('alive' 'injured' 'deceased' )
AS Status'

It appears that the values within the parenthesis require commas to separate them, but I cannot think of, nor find a way to efficiently do this. Please advise. You guys are great :)

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（5）

梦断已成空 2024-11-26 14:48:47

创建一个数组。使用 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);

Create an array. Push each set value to the array using array_push. Then use implode(",",$array) to create comma separated value variable. Use that within your IN statement

Example in conjunction with PeeHaa's response.

$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);
回复 原文
云朵有点甜 2024-11-26 14:48:47

我可以建议一个稍微更好的解决方案吗？您可以通过在名称末尾添加方括号来创建将数组返回到PHP的表单（例如status[] ）。使用数组将使您的 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 转义该值，以防万一。

May I suggest a slightly better solution. You can create forms that return an array to PHP by adding square brackets at the end of the name (e.g. status[]). Using an array will keep you PHP code short as you don't need to look for each variable separately. For instance:

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'])) . '")';

The implode function will join the data together. I've also added an array_map call that will run every array value through mysql_real_escape_string to escape the value, just in case.

回复 原文
意中人 2024-11-26 14:48:47
$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";

这应该可以做到。

$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";

This should do it.

回复 原文
冬天旳寂寞 2024-11-26 14:48:47

根据当前的设置，在问题陈述的每个变量后面添加逗号：

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

With your current set-up, add commas after each variable to the problem statement:

SELECT * FROM status WHERE status IN ($alive, $injured, $deceased, $unkstat, $found, $missing)) AS Status
回复 原文
此岸叶落 2024-11-26 14:48:47

有点糟糕，但这可以工作......

基本上形成所有 $_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;
}

?>

Bit of a botch but this could work....

Basically form a comma seperated list of all $_POST variables...

<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;
}

?>
回复 原文
~没有更多了~

关于作者

箹锭⒈辈孓

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

故笙诉离歌

文章 0 评论 0

qq_swB5Wi

文章 0 评论 0

送你一个梦

文章 0 评论 0

mb_PT8LkUS5

文章 0 评论 0

千仐

文章 0 评论 0

梦里泪两行

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文