Learn Intermediate PHP - Project 1 - CodeClub

Att mods: if you are reading this, I’m sorry I couldn’t find a more accurate tag.

Link to exercise:
https://www.codecademy.com/courses/learn-intermediate-php/projects/php-code-club

I’m trying to complete the optional 22. task but have been unsuccessful, i want to introduce a column for the userStatus (i’ve referred to as simply ‘status’) but how to change something on the DB boggled me for a long time until i noticed the db.sql file. But an ychanges in the db.sql doesnt seem to affect the CC environment.

I’m sure it’s something silly and small (as per my history here) but i’m been stuck on this for hours now so I’m looking for an assist.

Note: i’ve restarted and forced refreshed the pages numerous times.

My latest error on https://localhost/members.php:
SQLSTATE[42703]: Undefined column: 7 ERROR: column members.status does not exist LINE 3: members.*, members.status, tiers.title ^

My db.sql

DROP TABLE IF EXISTS tiers; CREATE TABLE tiers ( id serial PRIMARY KEY, title VARCHAR (255) NOT NULL, price INT, status BOOLEAN DEFAULT TRUE ); INSERT INTO tiers (title, price) VALUES ('Bronze', 20), ('Silver', 50), ('Gold', 100), ('Platinum', 250); DROP TABLE IF EXISTS members; CREATE TABLE members ( id serial PRIMARY KEY, first_name VARCHAR (255) NOT NULL, last_name VARCHAR (255), address VARCHAR (255), tier_id INT ); INSERT INTO members (first_name, last_name, address, tier_id) VALUES ('Selena', 'Heidenreich', '858 Murl Locks Lake Jonasside, TN 33085-2765' , 2), ('Theodore', 'Barnes', '994 Kunze Inlet Kemmerberg, WA 69344', 3), ('Matias', 'Larson', '12340 Kub Ridge Hailiemouth, MN 96724', 3), ('Mckayla', 'Gopinatha', '50601 Homenick Lake Suite 855 Keelingbury, WV 62917-8508', 4); UPDATE members SET status = 1 WHERE id = 1;

members.php

<?php require_once 'functions.php'; // Create a try/catch block to handle database exceptions try { // Connect to the database using the connect function $db = connect(); // Create the $membersQuery here. Use an inner join to get the tier titles for the table $membersQuery = $db->query(' SELECT members.*, members.status, tiers.title FROM members INNER JOIN tiers ON members.tier_id = tiers.id' ); // Fetch all members and assign the result to $members $members = $membersQuery->fetchAll(PDO::FETCH_ASSOC); } catch (Exception $e) { // Echo the message if there was an exception echo $e->getMessage(); } // Close the database connection here ?> <?php require_once '_header.php' ?> <a href='index.php' class='btn btn-secondary m-2 active' role='button'>Home</a> <a href='tiers.php' class='btn btn-secondary m-2 active' role='button'>Tiers</a> <?php if (!empty($_GET['type']) && ($_GET['type'] === 'success')) : ?> <div class='row'> <div class='alert alert-success'> Success! <?= $_GET['message'] ?> </div> </div> <?php elseif (!empty($_GET['type']) && ($_GET['type'] === 'error')) : ?> <div class='row'> <div class='alert alert-danger'> Error! <?= $_GET['message'] ?> </div> </div> <?php endif; ?> <div class='row'> <h1 class='col-md-12 text-center border border-dark text-white bg-primary'>Members</h1> </div> <div class='row'> <table class='table table-striped'> <thead> <tr> <th scope='col'>#</th> <th scope='col'>First Name</th> <th scope='col'>Last Name</th> <th scope='col'>Address</th> <th scope='col'>Tier</th> <th scope='col'>Status</th> <th scope='col'>Actions</th> </tr> </thead> <tbody> <?php foreach ($members as $member) : ?> <tr> <td><?= $member['id'] ?></td> <td><?= htmlentities($member['first_name']) ?></td> <td><?= htmlentities($member['last_name']) ?></td> <td><?= htmlentities($member['address']) ?></td> <td><?= htmlentities($member['title']) ?></td> <td><?= htmlentities($member['status'] ? 'active' : 'inactive') ?></td> <td> <a class='btn btn-primary' href='member-form.php?id=<?= $member['id'] ?>' role='button'>Edit</a> <a class='btn btn-danger' href='delete-member.php?id=<?= $member['id'] ?>' role='button'>Delete</a> </td> </tr> <?php endforeach; ?> </tbody> </table> </div> <div class='row'> <div class='col'> <a class='btn btn-success' href='member-form.php' role='button'>Add member</a> </div> </div> <?php require_once '_footer.php' ?>

Edit: Just realized this is terrible to recreate. please let me know if I should share all the projects modified files. I can post it as code snippet like above or upload as PHP files.

Hey everyone!

I am at task 10 right now and I am getting this “could not find driver” when calling the connect() function.
Does anyone know what I might be doing wrong?

Thank you.

Error

<?php

// Connect to the database and return the database object
function connect() {
    // Set the hostname for CodeCademy's platform
    $hostname = '/tmp';

    // Set the database name
    $dbname = 'ccuser';

    // Set the username and password with permissions to the database
    $username = 'ccuser';
    $password = 'pass';
    
    // Create the DSN (data source name) by combining the database type, hostname and dbname
    $dsn = "pgsql;host:$hostname;dbname=$dbname";

    // Create the try/catch blocks here
    try {
      return new PDO($dsn, $username, $password);
    } catch (Exception $e) {
      echo $e->getMessage();
    }
}

// Get a list of all tiers in the database
function getTiers() {
    try {
        // Get the database object
        $db = connect();
        // Create a query to get all fields for all tiers
        try {
          $tiersQuery  = $db->query("SELECT * FROM tiers");
          return $tiersQuery->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
          echo $e->getMessage();
        }
        // Return all records
    } catch (Exception $e) {
        // If an error occurs echo the error
        echo $e->getMessage();
    }
}

How you create the DSN (from the lesson):

The first part of the DSN is the prefix , where we specify which database system to use. For PostgreSQL, that is pgsql . Then we add a colon, followed by a key/value pair containing the hostname and database name, separated by a semi-colon. The DSN uses a precise format, so it’s important not to include spaces or extra characters.

The key value pairs are

  • key = host, value = $hostname
  • key = dbname, value = $dbname

These should have the same format.