假设我有一个PHP函数,它生成一个唯一的机器人名称,例如:
$rname = generateRobotName();
我想用REMOTE控制器复制所有机器人,并为每个新机器人分配一个唯一的机器人名称(由上述函数生成(。
我该怎么做?
INSERT INTO robots values (origin, name, color, position, controller)
SELECT origin, '".$rname."', color, position, controller
FROM robots
WHERE controller = 'remote'
为了避免SQL注入和其他在SQL语句中直接集成变量所带来的漏洞(在这种情况下不是一个大问题,但这是一个很好的做法(,您可以将PDO与准备好的语句一起使用。以下代码循环执行每个MySQL查询的每个步骤,并更新机器人名称:
$SQLInsert = "INSERT INTO robots (origin, name, color, position, controller) values (:origin, :name, :color, :position, :controller)";
$SQLSelect = "SELECT origin, name, color, position, controller FROM robots WHERE controller = 'remote'";
foreach ($pdo->query($SQLSelect) as $row){
$params = [
':origin' => $row['origin'],
':name' => generateRobotName(),
':color' => $row['color'],
':position' => $row['position'],
':controller' => $row['controller']
];
$SQLInsert = $pdo->prepare($SQLInsert);
$SQLInsert->execute($params);
}
上面的代码执行SQL Select查询,对于它找到的每一行,$row
,它使用第二个定义的查询插入旧数据,同时仍然添加robot名称DO请记住,$pdo
被定义为PDO对象,在PHP安装中需要PDO支持。工作示例可以在这里找到。干杯
您可以将任务分成两部分:
-
从MySQl 获取数据
-
插入由PHP 更新的新数据
$sql = "SELECT origin, name, color, position, controller FROM robots WHERE controller = 'remote'"; $stmt= $pdo->prepare("insert into robots (origin, name, color, position, controller) values (?, ?, ?, ?, ?);"); foreach ($pdo->query($sql) as $row) { $stmt->execute([ $row['origin'], generateRobotName(), $row['color'], $row['position'], $row['controller'] ]); }
这里是实时代码PHPize.online
另一个带有命名参数的示例:
$sql = "SELECT origin, name, color, position, controller
FROM robots WHERE controller = 'remote'";
$stmt= $pdo->prepare(
"insert into robots (origin, name, color, position, controller)
values (:origin, :name, :color, :position, :controller);"
);
foreach ($pdo->query($sql, PDO::FETCH_ASSOC) as $row) {
$row['name'] = generateRobotName();
$stmt->execute($row);
}
PHPize it here