How to repeat foreign id sequentially while insert in php

[suseelan27]

Table user
+---------------+---------------+------------+
|    user_id    |   username    | user_level |
+---------------+---------------+------------+
|      1        |  superadmin   |   admin    |
|      2        |  subadmin     |   admin    |
|      3        |  team1        |   team     |
|      4        |  team2        |   team     |
|      5        |  team3        |   team     |
|      6        |  customer1    |   customer |
|      7        |  customer2    |   customer |
|      8        |  customer3    |   customer |
|      9        |  customer4    |   customer |
+---------------+---------------+------------+
Table complaint:
+---------------+---------------+------------+
| complaint_id  |   complaint   |  user_id   |
+---------------+---------------+------------+
|      1        |  os issue     |      7     |
|      2        |  USB issue    |      8     |
|      3        |  OS currepted |      7     |
|      4        |  HD issue     |      9     |
|      5        |  DVD issue    |      6     |
|      6        |  SW problem   |      9     |
|      7        |  Network issue|      9     |
|      8        |  system issue |      6     |
+---------------+---------------+------------+
Table assign_work
+---------------+------------+
| complaint_id  |  user_id   |
+---------------+------------+
|      1        |      3     |
|      2        |      4     |
|      3        |      5     |
|      4        |      3     |
|      5        |      4     |
|      6        |      5     |
|      7        |      3     |
|      8        |      4     |
+---------------+------------+

When customer raise the complaint data should save in complaint table also that last 
complaint_id should save in assign_work table at the same time  
 user_id also save sequencially, fetch from user table who are the team that person id only.

I m new one please any one help me.

[LastMitch]

@suseelan27

How to repeat foreign id sequentially while insert in php

You have to explain a little more clearly what are you trying to do. You didn't provide any query or any php code related to your issue. I mean a table is good.

[diafol]

Could the assign_work table be incorporated into the complaints table?

Table complaint:

+---------------+---------------+------------+------------+
| complaint_id  |   complaint   |  user_id   |  assigned  |
+---------------+---------------+------------+------------+
|      1        |  os issue     |      7     |     3      |
|      2        |  USB issue    |      8     |     4      |
|      3        |  OS currepted |      7     |     5      |
|      4        |  HD issue     |      9     |     3      |
|      5        |  DVD issue    |      6     |     4      |
|      6        |  SW problem   |      9     |     5      |
|      7        |  Network issue|      9     |     3      |
|      8        |  system issue |      6     |     4      |
+---------------+---------------+------------+------------+

To keep this flexible, I think you'd need to query the user table first to list all the team members, something like:

SELECT user_id WHERE user_level = 'team' ORDER BY user_id

Make this into an array with a fetchAll (if using mysqli / PDO)

So your array should contain {3,4,5}

Get the last complaint from the table:

SELECT assigned FROM complaint ORDER BY complaint_id DESC LIMIT 1

So from the output of the above we should get '4'. Now get the next team member for your insert query (PDO style)

$last = 4; //from DB
$arr = array(3,4,5); //from DB

function getNextTeam($last, $arr){
    $maxKey = count($arr) - 1;
    //if the last team member is still in the DB
    if($key = array_search($last, $arr)){
        if($key < $maxKey){
            $getKey = $key + 1;
        }elseif($key == $maxKey){
            $getKey = 0;
        }
        //if the last team member has been deleted (just in case) 
    }else{
        foreach($arr as $k=>$v){
            if($last < $v){
                $getKey = $k;
                break;  
            }
        }
        if(!isset($getKey))$getKey = 0;
    }
    return $arr[$getKey];
}

$assigned = getNextTeam($last, $arr);

The above maybe a bit spaghetti-ish (head's shot) and could probably do with some reducing.

So if using a parameterized PDO-style query:

INSERT INTO complaint SET complaint = ':complaint', user_id = :id, assigned = :assigned

[LastMitch]

How to repeat foreign id sequentially while insert in php

You might get an error with foreign id if your table is not created correctly:

ERROR 1005 (HY000): Can't create table '' (errno:)

To fixed it. It really depend how your table is structure.

But I think daifol help you clear that up.

[suseelan28]

if(isset($_POST["complaint_reg"]))
{

    $complaint_type = mysql_real_escape_string($_POST["complaint_type"]);
    $remarks =mysql_real_escape_string( $_POST["remarks"]);
    $complaint_id =mysql_real_escape_string( $_POST["complaint_id"]);

    $sql ="select id from admin where user_level='team'";
    $result=mysql_query($sql);
    while($row = mysql_fetch_array($result))
    {

    $userid =$row['id'];

     $sql="INSERT INTO complaint(complaint_type,remarks,complaintreg_dt,id,user_id)    
    VALUES
    ('$complaint_type','$remarks',now(),'$login_id','$userid')";
    $result=mysql_query($sql);


}

This code i m trying to insert the data in complaint table retrive the data from admin(user) table. If i use this code usertable team member last id only inserting every time. Thank you for your reply.

[suseelan28]

$sql ="select id from admin where user_level='team'";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result))
{

 $userid =$row['id'];

    $update_queries = "insert into  assign_complaint  (userid,complaint_id) values ('$userid','$lastid')";
        mysql_query($update_queries); 
$userid++;      


}

If i use this code one time insertion three records adding in the assign_complaint(assign_work) table when complaint registering.

[suseelan28]

Exact requirement:
When i register the complaint , the data should store in complaint table also that last complaint_id should save in assign_work table.(I finished this step).

The challenge is from user table user_level team members id only repetedly like cyclic order save to assig_work table when complaint registering. this is for auto assign the work to developers.

[suseelan28]

Please any one help me. I dont have idea, team member id want to save cyclic order when complaint regisering . In assign_work assign field we only have to save the id while registering the complaint.

[suseelan28]

That user table not only the 3,4,5 . That is employee id, so that will increse.

If that employee like 3,4,5,7,8,11 we have to repeted to assign the work 3,4,5,7,8,11,3,4,5,7,8,11 ... like we have to assign the work.this is for auto assign work to employeer. When complaint will regiter that time automatically work should assign to the employer like cyclic order.

[pritaeas]

If you want a cyclic order, then you will have to store the last used employee id in a configuration table somewhere, so you can use it to determine the next one to use.