1,105,263 Community Members

How to repeat foreign id sequentially while insert in php

suseelan27
Newbie Poster
1 post since Feb 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 
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
Deleted Member
 
0
 

@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.

Member Avatar
diafol
Where are my eyes?
12,941 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
1
 

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
Deleted Member
 
0
 

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
Newbie Poster
5 posts since Feb 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 
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
Newbie Poster
5 posts since Feb 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 
$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
Newbie Poster
5 posts since Feb 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

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
Newbie Poster
5 posts since Feb 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

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
Newbie Poster
5 posts since Feb 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

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.

Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,831 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
1
 

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.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: