begin_transaction(); // 1. First update the users table $query_users = "UPDATE users SET firstname = ?, lastname = ?, email = ?, phone = ? ,username =? WHERE user_id = ?"; $stmt_users = $conn->prepare($query_users); if (!$stmt_users) { throw new Exception("Prepare failed for users table: " . $conn->error); } $stmt_users->bind_param("sssssi", $first_name, $last_name, $email, $phone,$username, $user_id); if (!$stmt_users->execute()) { throw new Exception("Error updating users table: " . $stmt_users->error); } // 2. Then handle the job_seekers table // First check if a record exists $check_query = "SELECT COUNT(*) as count FROM job_seekers WHERE user_id = ?"; $check_stmt = $conn->prepare($check_query); $check_stmt->bind_param("i", $user_id); $check_stmt->execute(); $result = $check_stmt->get_result(); $row = $result->fetch_assoc(); if ($row['count'] > 0) { // Update existing record $job_seeker_query = "UPDATE job_seekers SET skills = ?, experience = ?, education = ?, location = ? WHERE user_id = ?"; } else { // Insert new record $job_seeker_query = "INSERT INTO job_seekers (skills, experience, education, location, user_id) VALUES (?, ?, ?, ?, ?, ?)"; } $stmt_job_seekers = $conn->prepare($job_seeker_query); if (!$stmt_job_seekers) { throw new Exception("Prepare failed for job_seekers table: " . $conn->error); } // Same binding for both UPDATE and INSERT $stmt_job_seekers->bind_param("ssssi", $skills, $experience, $education, $location, $user_id ); if (!$stmt_job_seekers->execute()) { throw new Exception("Error with job_seekers table: " . $stmt_job_seekers->error); } $conn->commit(); echo ""; exit(); } catch (Exception $e) { $conn->rollback(); $_SESSION['error_message'] = "Error: " . $e->getMessage(); error_log("Profile update error: " . $e->getMessage()); header("Location: jobseekerprofile.php"); exit(); } finally { if (isset($stmt_users)) $stmt_users->close(); if (isset($stmt_job_seekers)) $stmt_job_seekers->close(); if (isset($check_stmt)) $check_stmt->close(); $conn->close(); } } ?>