How to Add lookup data while php programming ? .mp4

To add lookup data while programming in PHP, you generally follow these steps:

1. Database Design

• Ensure your database has the necessary tables for the main data and the lookup data. For example, if you have a users table, you might have a roles table for user roles.

2. Establish Database Connection

• Use PDO or MySQLi to connect to your database.

php
$host = 'localhost';
$db = 'your_database';
$user = 'your_username';
$pass = 'your_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

3. Fetch Lookup Data

• Retrieve the lookup data from the database.

php
$stmt = $pdo->query("SELECT id, name FROM roles");
$roles = $stmt->fetchAll(PDO::FETCH_ASSOC);

4. Populate Form or Data Structure

• Use the retrieved data to populate a dropdown or other UI elements.

php
echo '<select name="role_id">';
foreach ($roles as $role) {
    echo '<option value="' . $role['id'] . '">' . $role['name'] . '</option>';
}
echo '</select>';

5. Handle Form Submission

• When the form is submitted, you can handle the selected value.

php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $roleId = $_POST['role_id'];
    // Process the data, e.g., insert into another table
}

6. Insert or Update Data

• Use the selected lookup value in your database operations.

php
$stmt = $pdo->prepare("INSERT INTO users (name, role_id) VALUES (?, ?)");
$stmt->execute([$username, $roleId]);

7. Error Handling and Validation

• Always validate and sanitize user input to prevent SQL injection and ensure data integrity.

Example Code

Here’s a complete example:

php
// Connection setup
$host = 'localhost';
$db = 'your_database';
$user = 'your_username';
$pass = 'your_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

// Fetching lookup data
$stmt = $pdo->query("SELECT id, name FROM roles");
$roles = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Form handling
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $username = $_POST['username'];
    $roleId = $_POST['role_id'];

    // Insert into users
    $stmt = $pdo->prepare("INSERT INTO users (name, role_id) VALUES (?, ?)");
    $stmt->execute([$username, $roleId]);
    echo "User added successfully.";
}

// Form HTML
?>

<form method="post">
    Username: <input type="text" name="username" required>
    <select name="role_id" required>
        <?php foreach ($roles as $role): ?>
            <option value="<?= $role['id'] ?>"><?= $role['name'] ?></option>
        <?php endforeach; ?>
    </select>
    <input type="submit" value="Add User">
</form>

This example illustrates how to fetch and use lookup data in PHP for populating a form and handling submissions. Adjust it according to your specific needs!

Download now

Enjoy! Follow us for more...