CODEWITHSUNDEEP
phpmysqlcodeigniterselect
Afshin
Afshin

Reputation: 2437

How to deal with SELECT(MAX()) in mysql inside a foreach using PHP and Codeigniter

I'm using SELECT(MAX()) inside a foreach loop and this is my code:

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

The problem is that for each image_Basename, query produces a new number.

For example, if I got 3 image_Basenames, it will insert 1, 2 and 3 for those three image_Basenames. But I want it to insert the same number to all of image_Basenames.

For example, if the max number in the image_Group_Id is 1, then add number 2 for each image_Basename. How can I do that?! I've put 

SELECT 1 + coalesce((SELECT max(image_Group_Id) FROM mg_gallery

outside of the foreach loop, but it didn't work!!!

The answer is added below by myself

Upvotes: 0

Views: 550

Answers (4)

Afshin
Afshin

Reputation: 2437

IT WORKS:

Specially thanks to Niloy Saha, finally, I got the answer, and this is the code I've used:

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

Upvotes: 0

Anthony
Anthony

Reputation: 37065

First of all, you should never be inserting values directly from the POST array. But in the interest of just addressing the question at hand, I'll leave the code as is.

You need to query for the MAX(image_Group_Id) before starting the loop and not do a + 1 inside the loop. Like this:

$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'
    ");
}

Upvotes: 0

Nil'z
Nil'z

Reputation: 7475

EDITED 2
Try this if it works or not,

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

Upvotes: 1

Robin Castlin
Robin Castlin

Reputation: 10996

I'm not fully sure what kind of data you have and exactly what you want, but I'll help you towards the right direction:

$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 would only run the query twice and spare your database the trouble of loop queries. I also escaped the values through $this->db->query() as intended to avoid mysql injections. This doesn't really require INSERT ... SELECT as INSERT ... VALUES is enough.

Upvotes: 0

Related Questions