mysql - 查询删除所有条目而不是选定的条目
问题描述
我制作了一个从 joomla 数据库填充的下拉菜单。该菜单包含来自特定用户的峰名称。这部分工作顺利。当用户从下拉菜单中选择峰名称并单击删除按钮时,查询应该只删除具有特定用户的 id ($link_id) 和所选顶部名称 ($vrh_name2) 的行。
在我的情况下,查询会删除该用户的所有行,而不管所选顶部的名称如何
我在哪里做错了?
<!DOCTYPE html>
<html>
<body>
<?php
echo '<div class="sender">';
$link_id = JRequest::getInt('link_id');
echo '<h4>Form for delete peak</h4>';
// Creating Dropdown menu from database
$db = JFactory::getDbo();
$query2 = $db->getQuery(true);
$query2->select('peak_name');
$query2->from($db->qn('#__climbing'));
$query2->where($db->quoteName('#__climbing.link_id')." = ".$db->quote($link_id));
$query2->order('peak_id ASC');
$db->setQuery($query2);
$peaks_list2 = $db->loadColumn();
$peaks_select2 = '<select name2="name2" id2="peaks">';
$peaks_select2 .= '<option value="">-- Select peak for delete --</option>';
foreach($peaks_list2 as $p2){
$peaks_select2 .= '<option value="' . $p2 . '">' . $p2 . '</option>';
}
$peaks_select2 .= '</select>';
?>
<form name="lista2" method="post" action="">
<?php echo $peaks_select2; ?>
<input type="submit" name="submit2" value="Delete" />
</form>
<?php
if(isset($_POST['submit2']))
{
$vrh_name2 = $_POST['name2'];
// Delete peak query
$db = JFactory::getDbo();
$q_4 = $db->getQuery(true);
$q_4->delete($db->quoteName('#__climbing'));
$q_4->where($db->quoteName('#__climbing.link_id')." = ".$db->quote($link_id))." AND ".($db->quoteName('#__climbing.peak_name')." = ".$db->quote($vrh_name2));
$db->setQuery($q_4);
$db->execute();
}
echo '</div>';
?>
</body>
</html>
解决方案
让我们不要忘记我过去提供的一些建议。
在这个问题中最引人注目的是,$peaks_select2 = '<select name2=
将不起作用。该name
属性必须是纯正的。
通过首先执行潜在的删除操作,您可以允许您的用户在删除后进行删除,并始终在选择中看到最新的数据。
我不喜欢您将其peak_name
用作标识符。您的表有一个peak_id
,在大多数情况下,这些应该是自动递增的并且是唯一的,并且它们是专业人士关联相关数据的方式。(您应该改变您的设计以采用这种做法。)
When an option's value
attribute value is the same as the option's text value, there is no need to declare the name
attribute. 因为我建议使用 id,所以我声明了name
属性。
未经测试的片段:
<!DOCTYPE html>
<html>
<body>
<?php
// get the $_POST['submit'] value; if missing, set as empty string (WORD is a Joomla-specific filter that strips unwanted characters for security reasons)
$action = JFactory::getApplication()->input->post->get('submit', '', 'WORD')
// get the $_POST['peak_id'] value; if missing set as 0 (INT strips any character that is not a digit)
$peak_id = JFactory::getApplication()->input->post->get('peak_id', 0, 'INT');
// try to get the $_POST['link_id'] value; if it is missing, try from $_GET['link_id']
$hiker_id = JFactory::getApplication()->input->post->get('link_id', 0, 'INT');
if (!$hiker_id)
{
// there was no submission (this is the first load of the page)
$hiker_id = JFactory::getApplication()->input->get->get('link_id', 0, 'INT');
}
echo "<div>Link Id: $hiker_id</div>";
$db = JFactory::getDbo();
if ($action === 'Delete' && $peak) // if action is Delete and $peak is not empty or 0
{
$delete_query = $db->getQuery(true)
->delete("#__climbing")
->where([
"link_id = " . (int) $hiker_id,
"peak_id = " . (int) $peak_id
]);
$db->setQuery($delete_query);
$db->execute();
if ($db->getAffectedRows()) // check for successful deletion (if at least one row was deleted)
{
echo "<div>Successfully deleted row for hiker#: $hiker_id, peak#: $peak_id</div>";
}
else
{
echo "<div>Failed to delete row for hiker#: $hiker_id, peak#: $peak_id</div>";
}
}
// now query the table for the fresh data after the (potential) delete was performed
$peaks_query = $db->getQuery(true)
->select("peak_id, peak_name")
->from("#__climbing")
->where("link_id = " . (int) $link_id)
->order("peak_id");
$db->setQuery($peaks_query);
$peaks_select = '<select name="peak_id">';
$peaks_select .= '<option value="0">-- Select peak to delete --</option>';
if (!$results = $db->loadAssocList()) // there were no rows in the result set
{
// no peaks found for $link_id
}
else
{
foreach ($results as $row)
{
$peaks_select .= "<option value=\"{$row['peak_id']}\">{$row['peak_name']}</option>"; // create option with name as text and id as the value
}
}
$peaks_select .= '</select>';
// print the simple form...
?>
<div class="sender">
<h4>Peak Delete Form</h4>
<form method="post" action="">
<?=$peaks_select?>
<input type="hidden" name="link_id" value="<?=$hiker_id?>">
<input type="submit" name="submit" value="Delete" />
</form>
</div>
</body>
</html>
推荐阅读
- c# - 是否可以获得 Azure 资源类型的所有可用指标?
- android - 以较小的尺寸显示模拟器窗口
- haskell - 我可以在单子上使用地图吗?
- reactjs - 从完成列表中删除一个项目我试图以多种方式做到这一点
- java - 如何在不是字段联系人的模板中添加属性(Sendinblue)
- android - 与 BLE 的通信不工作,很少工作
- mongodb - 有没有办法将毫秒字符串转换为 mongodb 可识别的日期格式?
- sql - Oracle Rownum,获取第 n 行
- c# - 如何对 Web API 项目进行故障排除?
- crystal-reports - Crystal Reports 总不同值列表