Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – insert to to table with if case

can anyone help me with this query? is it possible to do something like this?
I am very new to sql.

table1 ‘userJobId’ have 2 coloumn userid and jobgroupid,
table2 ‘tbl14userJob’ have a14userID and a14jobID as coloumn.
I need to add data to table1 userJobId where userid = a14userID
for jobgroupid = 1 if a14jobID = ‘CUPE12MO’, jobgroupid = 2 if a14jobID=’TEACHOCCL’, jobgroupid = 3 if a14jobID = ‘COOPSTUDENT’

this is the part where i have to add the query

$sql = "INSERT INTO userJobId (userid, jobgroupid) value ";
$sql .= "('$uid', ??? ),";
$db->exec($sql);

how to run an if statement in sql?

Tags:

2

Answers


  1. 0

    Do learn how to use bind parameters to prevent SQL injection attacks (and bugs).

    You do want to use a subquery, something like this (untested):

    insert into userJobId (userid, jobgroupid) values (?, (
    select
        case tbl14userJob.a14jobID
            when 'CUPE12MO' then 1
            when 'TEACHOCCL' then 2
            when 'COOPSTUDENT' then 3
        end
    from tbl14userJob
    where tbl14userJob.a14userID=userJobId.userid
))
    Login or Signup to reply.
  2. 0

    You can achieve your goal using a combination of SQL and PHP logic. Since SQL doesn’t have direct "if" statements in the way programming languages do, you can handle the conditions in PHP before building your SQL query.

    Here’s how you can do it:

    1. Fetch the a14userID and a14jobID from tbl14userJob.
    2. Determine the corresponding jobgroupid based on the value of a14jobID.
    3. Insert the records into userJobId accordingly.

    Here’s a sample implementation:

    // Assume $db is your PDO connection and $uid is defined
$sql = "INSERT INTO userJobId (userid, jobgroupid) VALUES ";

// Sample query to fetch data from tbl14userJob
$query = "SELECT a14userID, a14jobID FROM tbl14userJob";
$stmt = $db->query($query);
$values = []; // Array to hold values for the insert

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $jobgroupid = null;

    // Determine jobgroupid based on a14jobID
    if ($row['a14jobID'] === 'CUPE12MO') {
        $jobgroupid = 1;
    } elseif ($row['a14jobID'] === 'TEACHOCCL') {
        $jobgroupid = 2;
    } elseif ($row['a14jobID'] === 'COOPSTUDENT') {
        $jobgroupid = 3;
    }

    // If a matching jobgroupid was found, prepare to insert it
    if ($jobgroupid !== null) {
        $values[] = "('$uid', $jobgroupid)";
    }
}

// If there are values to insert, construct and execute the SQL
if (!empty($values)) {
    $sql .= implode(',', $values); // Combine all values into a single SQL statement
    $db->exec($sql);
} else {
    echo "No matching jobgroupid found.";
}

    Explanation:

    1. Fetch Data: You fetch all records from tbl14userJob.
    2. Determine jobgroupid: Use PHP to set the jobgroupid based on the value of a14jobID.
    3. Build Insert Statement: Store all the values in an array, then join them to create the final SQL query.
    4. Execute: Only run the insert query if there are values to insert.

    Note:

    • Make sure to handle exceptions and errors in your code for a more robust implementation.
    • Always validate and sanitize your inputs to prevent SQL injection. In production code, consider using prepared statements for better security.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search