我在foreach
循环中使用SELECT(MAX())
,这是我的代码:
foreach($_POST['image_Basename'] as $key=>$image_Basename){
$image_Title = $this->input->post('image_Title');
$image_Category_Id = $this->input->post('image_Category_Id');
$this->db->query("INSERT INTO mg_gallery (image_Group_Id, image_title, image_Basename, image_Category_Id)
SELECT 1 + coalesce((SELECT max(image_Group_Id) FROM mg_gallery), 0), '$image_Title', '$image_Basename', '$image_Category_Id'
");
}
问题是,对于每个image_Basename
,查询都会生成一个新的数字。
例如,如果我得到3个image_Basenames
,它将为这三个image_Basenames
插入1、2和3。但我希望它在所有image_Basenames
中插入相同的数字。
例如,如果image_Group_Id中的最大数字为1,则为每个image_Basename添加数字2。我怎么能那样做?!我放了
SELECT 1 + coalesce((SELECT max(image_Group_Id) FROM mg_gallery
在foreach
循环之外,但它不起作用!!!
答案由我自己添加到下面
已编辑2
试试这个,如果它工作与否,
$maxRs = $this->db->query('SELECT max(image_Group_Id) AS max FROM mg_gallery');
echo $this->db->last_query();die; #run this query in your phpmyadmin and debug it.
if($maxRs->num_rows() > 0){
$maxData = $maxRs->row_array();
echo "here :".$maxID = $maxData['max'];die;
}else{
$maxID = 0;
}
//echo "max : ".$maxID;die; #check if its returning the corrent maxid or not.
foreach($_POST['image_Basename'] as $key=>$image_Basename){
$image_Title = $this->input->post('image_Title');
$image_Category_Id = $this->input->post('image_Category_Id');
$this->db->query("INSERT INTO mg_gallery (image_Group_Id, image_title, image_Basename, image_Category_Id)
$maxID, '$image_Title', '$image_Basename', '$image_Category_Id'
");
echo $this->db->last_query();die; #check the query its generating is correct or not and run directly at phpmyadmin
}
我不完全确定你有什么样的数据,也不确定你到底想要什么,但我会帮助你朝着正确的方向前进:
$int_basename = (int)max($this->input->post('image_Basename'));
$str_image_title = $this->input->post('image_Title');
$str_image_category_id = $this->input->post('image_Category_Id');
$query = $this->db->query("SELECT max(image_Group_Id) AS max FROM mg_gallery");
$int_max = (int)$query->row()->max;
$arr_union = array();
for($i = 1; $i <= 3; $i++)
if ($i == 1)
$arr_union = "SELECT " . ($int_max + $i) . " AS id";
else $arr_union = "SELECT " . ($int_max + $i);
$str_union = implode(' UNION ', $arr_union);
$this->db->query("
INSERT INTO mg_gallery (image_Group_Id, image_title, image_Basename, image_Category_Id)
SELECT h.id, ?, ?, ?
FROM ({$str_union}) AS h
", array($str_image_title, $int_basename, $str_image_category_id));
这将只运行查询两次,并使数据库免于循环查询的麻烦。我还通过$this->db->query()
转义了这些值,以避免mysql注入。这并不真正需要INSERT ... SELECT
,因为INSERT ... VALUES
就足够了。
首先,您不应该直接从POST
数组中插入值。但为了解决手头的问题,我将保持代码原样。
在开始循环之前,您需要查询MAX(image_Group_Id)
,而不是在循环内执行+ 1
。像这样:
$get_group_id = $this->db->query("SELECT 1 + coalesce((SELECT max(image_Group_Id) AS group_id FROM mg_gallery), 0)");
$get_group_id_array = $get_group_id->fetch_assoc();
$group_id = $get_group_id_array['group_id'];
foreach($_POST['image_Basename'] as $key=>$image_Basename){
$image_Title = $this->input->post('image_Title');
$image_Category_Id = $this->input->post('image_Category_Id');
$this->db->query("INSERT INTO mg_gallery (image_Group_Id, image_title, image_Basename, image_Category_Id)
$group_id, '$image_Title', '$image_Basename', '$image_Category_Id'
");
}
IT工作原理:
特别感谢Niloy Saha,终于,我得到了答案,这是我使用过的代码:
$getMaxValue = $this->db->query('SELECT 1 + coalesce((SELECT MAX(image_Group_Id)), 0) AS image_Group_Id FROM mg_gallery');
if($getMaxValue->num_rows() > 0){
$group_Id = $getMaxValue->row_array();
$image_Group_Id = $group_Id['image_Group_Id'];
}else{
$image_Group_Id = 0;
}
foreach($_POST['image_Basename'] as $key=>$image_Basename){
$image_Title = $this->input->post('image_Title');
$image_Category_Id = $this->input->post('image_Category_Id');
$this->db->query("INSERT INTO mg_gallery (image_title, image_Basename, image_Category_Id, image_Group_Id)
VALUES ('$image_Title', '$image_Basename', '$image_Category_Id', $image_Group_Id)
");
}